Continue sequence from highest number in field

[bhammer]

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

 

[bhammer]

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