"bhammer" wrote:
[Quoted Text] > I have the code shown below on a button on a form that appends records to the
> Observations table. It gets the CatalogID from a selection in one listbox,
> and a Doc_Number from looping through a multiselect listbox.
>
> It works fine only if there are no existing records with the same CatalogID.
> The table has a CatalogID field and an ObservationID2 field. This is used to
> create an "index" number like 3.1, 3.2, 3.3 and so on (for CatalogID 3,
> Observations 1-3).
>
> If there are existing CatalogID 3 records, then the ObservationID2 field
> should continue numbering where the last record cataloged into the table with
> the same CatalogID left off.
>
> For example, I want to add observation records with CatalogID 3. The table
> already has 20 records under CatalogID 3, with observationID2 numbered
> sequentially 1-20. So the next added record should be 21, then 22.
>
> How can I lookup the highest ObservationID2 for a given CatalogID? and then
> add one each time?
>
> Private Sub cmdAddObservations_Click()
> Dim ctl As Control, db As Database, rs As Recordset
> Dim varSelectedItem As Variant
> Dim strCatID As String, strDocNum As String
> Dim intCount As Integer
> Dim msg, style
>
> Set ctl = Me.lstPhotos
> sql = "SELECT * From tblObservations"
> Set db = CurrentDb
> Set rs = db.OpenRecordset(sql, dbOpenDynaset)
> intCount = 0
>
> For Each varSelectedItem In ctl.ItemsSelected
> intCount = intCount + 1
> strCatID = Me.lstInspections.Column(0)
> strDocNum = ctl.Column(1, varSelectedItem)
> rs.AddNew
> rs![CatalogID] = strCatID
> rs![ObservationID2] = intCount 'this needs to pick-up at next
> highest value
> rs![Doc_Number] = strDocNum
> rs.Update
>
> Next varSelectedItem
>
> rs.Close
> Set db = Nothing
> Set rs = Nothing
>
> CODE END
>
> -Brad
This works now by adding, before the For Each line,
intMaxID2 = Nz(DMax("ObservationID2", "tblObservations", "CatalogID =" &
strCatID), 0)
where inMaxID2 is a string variable, then in the loop place this in the
ObservationID2 line:
rs![observationID2 = intMaxID2 + intCount
That's it!
-Brad
|