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

 

[Pat Hartman]

Drawing number is probably also a string. Getting quotes inside of strings can be confusing. What I do is create a constant named "QUOTE". Then I use that constant every time I need to get quotes inside a string. Therefore, my solution would look like:

dim strSQL as String
strSQL = "UPDATE [Drawing Log] SET [Current Revision] = " & QUOTE & Me.CurrentRevision & QUOTE & " WHERE [Drawing Number] = " & QUOTE & [Forms]![DCNs]![DCN Details Subform]![Drawing Number] & QUOTE

DoCmd.RunSQL strSQL

Stuffing the string in a variable will allow you to print it easily to make sure that the quotes ended up in the right place. Just put a stop on the DoCmd line and examine the string before running it.

 

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