Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    59

    Unanswered: Creating a unique ID for a new set of values

    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:

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    for your batch id.. how about using something like the current system timestamp?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •