DoCmd.RunSQL Update giving Run Time Error

[AMickey]

I'm getting a run time error with this vba code and can't figure out how to get it to work. It runs from a button on a form and should update a table. Any help would be appreciated. The code is below:

    DoCmd.RunSQL "UPDATE Opportunities SET Opportunities.[Calls Made] = " & Me.[Calls Made].Value + 1 _
    & "WHERE SSN=" & [SSN] & " " _
    & "AND Opportunities.Campaign ='" & Me.[Campaign] & "' "
    DoCmd.RunSQL "UPDATE Opportunities SET [Priority] = " & 0 _
    & "WHERE SSN=" & [SSN] & " " _
    & "AND Campaign ='" & [Campaign] & "' "
    DoCmd.RunSQL "UPDATE Opportunities SET [Is Record Locked for Viewing] = False " _
    & "WHERE SSN=" & [SSN] & " " _
    & "AND Campaign ='" & [Campaign] & "' "
    Requery
    DoCmd.RunSQL "UPDATE Opportunities SET [Is Record Locked for Viewing] = True " _
    & "WHERE SSN=" & [SSN] & " " _
    & "AND Campaign ='" & [Campaign] & "' "
    Me.CalledCheckBox = False
    Me.DidntCallCheckBox = False

[zmbd]

[Z{Please use the [CODE/] button to format posted script and formated text - Please read the FAQ} <<CLICK HERE - PLEASE READ ME>>]

Please post the EXACT title, number, and text of the error message. Please do not alter, omit, nor abreviate the information provided in the error message

--
You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.
Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.
So to use your code:
Because you have so many strings in your code, I'll leave them to you to format; however, once done then you can use something along the following:

DIM strSQL as string
strSQL = (replace with your string)
'
'now you can insert a debug print here for troubleshooting
' - press <ctrl><g> to open the immediate window
' - you can now cut and paste this information for review!
'
debug.print "Your criteria = " & strSQL
'
'now use the string in your code:
(replace this in this case the docmd.runsql)

If you will make these little changes and post back the resolved string we can help you tweak the code.

[zmbd]

OH and ABSOLUTLY one should not store SSN in clear text!
Too many cases of Identity theft have happend because these numbers are in clear text.

NEVER EVER EVER think a "Black Hat" can't breach your or your company's security infrastructure -

look what just happened to Target... someone stole a vender's credentials and now 100's of thousands of people are in danger of having their accounts stolen!

Trust me, there will be legal ramifications for this at somepoint and I for one am glad I don't work for Target nor the Banks that let that breach happen!

You should be at least using at least ACC2010 and the built in encryption and read the following, although this article only goes thru ACC2007, the basic concepts should be taken to heart!!! http://bytes.com/topic/access/insigh...atabase-access

You should also look at storing either the digest (think MD5 SHA etc...) or an encyrpted version of the SSN wherein only a few people know the password take a look at the following Insight articles:

MD5

http://bytes.com/topic/access/insights/877896-generate-secure-user-passwords-using-md5-hash-class-module

SHA

http://bytes.com/topic/access/insights/906938-sha2-cryptographic-hash-algorithm-vba-vbscript

AES

http://bytes.com/topic/access/insights/906850-aes-encryption-algorithm-vba-vbscript

RC4

http://bytes.com/topic/access/insights/906671-rc4-encryption-algorithm-vba-vbscript

!A BIG THANK YOU TO: TOPHER23, NEOPA, RABBIT, THESMILEYCODER, AND OTHERS FOR CREATING, COMMENTING, AND MAINTAINING THE ABOVE LINKS!

[mcupito]

Let me point something out, as a means to ONLY reinforce zmbd's previous comments.
You built the SQL strings into VBA, which is not so bad if you know what you're doing, however look at the code. You are inconsistent with your ' and " .. E.g:

    & "WHERE SSN=" & [SSN] & " " _
    & "AND Opportunities.Campaign ='" & Me.[Campaign] & "' "

For SSN you use " & [SSN] & " and for Campaign you use ' " & Me.[Campaign] & " '

And then a few lines down you use

Campaign ='" & [Campaign] & "' "

I think you need to investigate on how to concatenate Fields into SQL strings in VBA, then the error could be clearer than other possibilities.

[AMickey]

Thank you for the help. ZMBD - I'll try what you recommended and then post more if I can't get it to work. I can get each section to work on its own as long as there aren't multiple "Where" criteria, but when I added in the second piece of criteria for the "Campaign" that's where it started to give me problems. As for the SSN remarks I appreciate the concern but that field doesn't actually contain a social security number so no worries there. I'll update this more when I get a chance to try it out.

[NeoPa]

@MCupito.
In this case Mark, it seems there are no such issues as you imagine. The OP is simply using the correct quotes for each system. These are different for VBA & SQL. See Quotes (') and Double-Quotes (") - Where and When to use them.

@AMickey.
I concur with Z's comments about working out your SQL strings in variables before execution whenever there are any problems. Debugging SQL is one thing. Debugging VBA is another.

Debugging a SQL string simply from the VBA code should not be another. It just shouldn't ever happen. It's simply making life more complicated than it needs to be.

[zmbd]

sorry, maybe a little over reaction with the SSN, I assumed
ʕ•ᴥ•ʔ

[AMickey]

Adding the Debug.Print command worked great as I could easily see where my issue was. I simply needed a space in between the open quotation mark and the Where (Changed to " Where...) and it works perfect. Thanks for the help!

[zmbd]

I work for Honey and Chocolate!
ʕ•ᴥ•ʔ

[NeoPa]

A trick once learned, never forgotten :-)