Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Update Statement - help in criteria 3

Status
Not open for further replies.

cutestuff

Technical User
Sep 7, 2006
162
CA
hi,

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.
 



Hi,

Found a few problems
Code:
    stSQL = "INSERT INTO tblMainLog(LogID, Project, Company) "
    strSQL = strSQL & "VALUES ('" & tmpLogID & "', '" & nz(Me![txtProject]) & "' , '" & nz(Me![txtContractor]) & ")"
    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 & "'  "
    strSQL = strSQL & "WHERE LogID = '" & tmpLogID & "' "
    CurrentDb.Execute (stSQL2)
the trailing parenthesis from the VALUES clause was not in the correct place

You had extra space before and after the ' in LogID = '" & tmpLogID & "'

Skip,

[glasses] [red][/red]
[tongue]
 
Depending of the DefaultValue of sequenceID:
stSQL2 = "UPDATE tblMainLog SET sequenceID = ' " & sequenceNo & " ' WHERE LogID = '" & tmpLogID & "' [!]AND sequenceID Is Null[/!]"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Consider this:

Your Insert statement:
Code:
stSQL = "INSERT INTO tblMainLog(LogID, Project, Company, [blue]SequenceNo[/blue]) "
strSQL = strSQL & "VALUES ('" & tmpLogID & "', '" & nz(Me![txtProject]) & "' , '" & nz(Me![txtContractor]) & ", [blue]123[/blue])"

CurrentDb.Execute (stSQL) 'Execute the SQL-query.

Since you want to increment SequenceNo and you want it to be 1 more than the one already assigned to your LogID, you may say:
Code:
"SELECT (MAX(SequenceNo) +1) As MySeqNo FROM tblMainLog " _
" WHERE LogID = '" & tmpLogID & "'"

To get all of that, you may use your Select SQL inside your INSERT statement to get next SequenceNo, something like this;
Code:
stSQL = "INSERT INTO tblMainLog(LogID, Project, Company, [blue]SequenceNo[/blue]) "
strSQL = strSQL & "VALUES ('" & tmpLogID & "', '" & nz(Me![txtProject]) & "' , '" & nz(Me![txtContractor]) & ", [blue] (SELECT (MAX(SequenceNo) +1) As MySeqNo FROM tblMainLog WHERE LogID = '" & tmpLogID & "')"
[/blue])"

CurrentDb.Execute (stSQL) 'Execute the SQL-query.

Have fun.

---- Andy
 
hi all,

Thanks to all who replied. Appreciate it. I tried your suggestions but can't seem to get it working still. :(

Andrzejek:
I tried yours but I still keep getting syntax errors (darn quotes). It keeps saying "expected end of statement." I've tried combinations and stuff. But I can't seem to get it. Sorry for the trouble but can you help me out?
Code:
stSQL = "INSERT INTO tblMainLog(LogID, Project, Company, SequenceNo) VALUES ('" & tmpLogID & "', '" & Nz(Me![txtProject], 0) & "' , '" & Nz(Me![txtContractor], 0) & "',  (SELECT (MAX(SequenceNo) +1) As SequenceID FROM tblMainLog WHERE LogID = '"&tmpLogID&"'))"
Thanks so much...
 
Don't be sorry, just keep working on it :)

First, you need to have SELECT statement working right:
Code:
str = "SELECT (MAX(SequenceNo) +1) As MySeqNo " _
    & " FROM tblMainLog " _
    & " WHERE LogID = [blue]'" & tmpLogID & "'"[/blue]
[green]'Debug.Prine str [/green]
CurrentDb.Execute (str)

You had some spaces around & missing

This should give you just one field with one row, something like:

MySeqNo
5



Please, use: Debug.Print to see your acctual SQL in Immiediate Window, it will help you see how your SQL looks like.


Have fun.

---- Andy
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top