hi,
I am trying to automatically increment a number (sequenceID) in tblMainLog.
Here is my part of my code:
My problem is this:
I am still new to SQL in VB and I would like the Update statement to not update ALL LogID = tmpLogID.
For example: (say this is my table)
LogID SequenceNo
01A 1
01B 1
If I run my code and add a new record with LogID 01A, the result would be:
LogID SequenceNo
01A 2
01B 1
01A 2
I would like the result to read:
LogID SequenceNo
01A 1
01B 1
01A 2
I hope this makes sense.
Please someone help. I'm sure it is a simple change to my SQL statement. But I've googled examples and can't figure it out.
Thanks so much in advance.
I am trying to automatically increment a number (sequenceID) in tblMainLog.
Here is my part of my code:
Code:
stSQL = "INSERT INTO tblMainLog(LogID, Project, Company) VALUES ('" & tmpLogID & "', '" & Nz(Me![txtProject], 0) & "' , '" & Nz(Me![txtContractor], 0))"
CurrentDb.Execute (stSQL) 'Execute the SQL-query.
'Automate incremental
sequenceNo = Nz(DMax("sequenceID", "tblMainLog", "LogID = '" & tmpLogID & " ' "), 0) + 1
'add sequenceNo to table
stSQL2 = "UPDATE tblMainLog SET sequenceID = ' " & sequenceNo & " ' WHERE LogID = '" & tmpLogID & "' "
CurrentDb.Execute (stSQL2)
My problem is this:
I am still new to SQL in VB and I would like the Update statement to not update ALL LogID = tmpLogID.
For example: (say this is my table)
LogID SequenceNo
01A 1
01B 1
If I run my code and add a new record with LogID 01A, the result would be:
LogID SequenceNo
01A 2
01B 1
01A 2
I would like the result to read:
LogID SequenceNo
01A 1
01B 1
01A 2
I hope this makes sense.
Please someone help. I'm sure it is a simple change to my SQL statement. But I've googled examples and can't figure it out.
Thanks so much in advance.