Update Statement in VB

[Psycholicon]

Hi everybody,
What am I doing wrong with this statement?

DoCmd.RunSQL "UPDATE [Drawing Log] SET [Current Revision] = & "'CurrentRevision'" & " WHERE [Drawing Number] =" & "[Forms]![DCNs]![DCN Details Subform]![Drawing Number]"

I have a form that displays records about drawings and I want to be able to update the revision level of one when it's necessary. I need it to move a letter up, so I have a command button that looks up the current revision level and assigns that as a string, then it runs code to level up the revision and reassign the new letter as the string. Then I wrote a statement that (theoretically) updates the master table. The criteria is that the record be for the drawing currently being viewed, which is found in a control on the form. However, when I click the button, it insters "CurrentRevision" into the Table instead of the next letter (which means that it looks up the record correctly). I have tried changing the arrangement of quotations but all changes have either not fixed it or made it worse.

Thanks in advance
Derek

 

[KeithG]

This is beacuase you are passing a string of CurrentRevision to the SQL statemment. Is CurrentRevision a varable? the below should work is CurrentRevision is a varable.

"UPDATE [Drawing Log] SET [Current Revision] ='" & CurrentRevision & "' WHERE [Drawing Number] =" & "[Forms]![DCNs]![DCN Details Subform]![Drawing Number

 

[Rabbie]

You can also use chr(34) instead of QUOTE in Pat's example.

 

[Psycholicon]

Thanks, Keith, it's working fine now. I knew that it was some arrangement of quotes, but I couldn't figure it out, either thinking it through or jmust trying everything I could think of. I'm very new to Visual Basic and have no resources besides the help files, which have some gaping holes when you get to the nitty gritty stuff.

I'll try to get rid of the syntax issues in the future by using a constant like Pat and Rabbie suggested.