New ID

D

Dan @BCBS

I wrote the following code to track evertime a record is touched.
The code run in the "ON LOAD" and copies specific values to a table.

It jsut will not give me a new ID number each time and I cannot figure out
why:

Private Sub Form_Load()
Dim lCriteria As String
Dim lICCNNO As String
Dim lID As Long

lICCNNO = Me!ICNNO

If MsgBox("All changes will be recorded - Do you want to continue? ",
vbQuestion & vbYesNo, "Question") = vbYes Then
DoCmd.SetWarnings False

lID = GetNewID("t_Changes")
lCriteria = "INSERT INTO t_Changes ( ID, ICNNO,
TR_DATE_TIMERCVD_HOI, TR_DATE_TIMERCVD, TR_CLOSEDATE, TR_Who, TR_When) "

lCriteria = lCriteria & "SELECT " & lID & " AS tID,
tblTrackingData.ICNNO, "
lCriteria = lCriteria & "tblTrackingData.TR_DATE_TIMERCVD_HOI,
tblTrackingData.TR_DATE_TIMERCVD, "
lCriteria = lCriteria & "tblTrackingData.TR_CLOSEDATE, " & """"
& gcurrentuser & """" & " AS tUser, "
lCriteria = lCriteria & "#" & Format$(Now, "mm\/dd\/yyyy
hh\:nn\:ss") & "# AS tDate "
lCriteria = lCriteria & "FROM tblTrackingData "
lCriteria = lCriteria & "WHERE (((tblTrackingData.ICNNO)=" &
"""" & lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria

End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Send data to table 11
Transfer data 14
Capture Date deleted 5
record changed values 1
OpenRecordSet Error 3
Save Deleted Record 16
Dialog in MsgBox 4
User Defining Problem 2

Top