How can I create a unique ID for a new batch set of values

[Russ Slater]

I'm currently using Visual Basic code to insert values into a temporary table
and am looking to change one thing. When I add the values sometimes I will
just add one value at a time or sometimes it could be more, there is no limit.
Each time I add these values I would like to have a unique ID attached to
each value that is added at that specific time, kind of like a batch ID. I
was thinking the way to do this would be to use a '+1' formula or something
like that. Is this the best way? Would I need to do some kind of lookup for
the highest ID and then do the '+1'? I am far less sure about using Visual
Basic for these things than SQL! Here is my current code:

   varNotes = Me.txtNotes
   lngLocID = Me.cboLocID
   lngActID = Me.cboActID
   lngActName = Me.txtActivityName
   lngPrjBox = Me.cboProjectBox
   Set db = CurrentDb
   If Me.grpRepeats = 2 Then 'need to loop through dates
       For datThis = Me.txtStartDate To Me.txtEndDate
           intDIM = GetDIM(datThis)
           intDOW = Weekday(datThis)
           If Me("chkDay" & intDIM & intDOW) = True Or _
                   Me("chkDay0" & intDOW) = True Then
               strSQL = "INSERT INTO tbl_temp_schedule_dates ( tscDate,
tscActID, tscLocID, tscActivityName, tscProjectBox, tscStartTime, tscEndTime,
tscNotes ) " & _
                   "Values(#" & datThis & "#," & lngActID & ", " & _
                   lngLocID & ",""" & lngActName & """," & lngPrjBox & ", #"
& Me.txtStartTime & "#, #" & _
                   Me.txtEndTime & "#," & _
                   IIf(IsNull(varNotes), "Null", """" & varNotes & """") & ")
"
               db.Execute strSQL, dbFailOnError
           End If
       Next
    Else  'dates are there, just add the title, notes, times, location,
Activity
       strSQL = "Update tbl_temp_schedule_dates Set tscActID = " & lngActID
& _
           ", tscActivityName = """ & lngActName & """, tscProjectBox = " &
lngPrjBox & _
           ", tscLocID = " & lngLocID & ", tscStartTime =#" & Me.
txtStartTime & _
           "#, tscEndTime = #" & Me.txtEndTime & "#"
 
       If Len(varNotes & "") > 0 Then
           strSQL = strSQL & ", tscNotes = " & IIf(IsNull(varNotes), Null,
"""" & varNotes & """")
       End If
       db.Execute strSQL, dbFailOnError
   End If
   Me.sfrm_temp_schedule_edit.Requery
   MsgBox "Temporary schedule built. " & _
       "You can now edit the schedule and " & _
       "append to the permanent schedule.", vbOKOnly + vbInformation, "Temp
schedule complete"
End Sub
 

[NeoPa]

Russ:
Iwas thinking the way to do this would be to use a '+1' formula or something like that. Is this the best way? Would I need to do some kind of lookup for the highest ID and then do the '+1'?

Essentially you're right Russ.

Use DMax() to find the maximum value of whichever field you store the Batch ID in then add one to this and use it as your new Batch ID.

Welcome to Bytes!