SQL Update Statement - help in criteria 3

[cutestuff]

hi,

I am trying to automatically increment a number (sequenceID) in tblMainLog.

Here is my part of my 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.

 

[SkipVought]

Hi,

Found a few problems

    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,

_[red][/red]

 

[PHV]

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

 

[Andrzejek]

Consider this:

Your Insert statement:

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:

"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;

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

 

[cutestuff]

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?

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...

 

[Andrzejek]

Don't be sorry, just keep working on it

First, you need to have SELECT statement working right:

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

 

[Andrzejek]

Also, see others and my replays here

http://www.tek-tips.com/viewthread.cfm?qid=1355695&page=2

Somebody else had problems with single quotes and double quotes in SQLs, this post hopefully sets it straight.


Have fun.

---- Andy