Creating a unique ID for a new set of values

[slaterino]

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

[pbaldy]

You can find the highest ID with a DMax() function, and add 1 to that. You may want to wrap the DMax() in an Nz() function to handle the possibility of there being no records, if appropriate to your application.