By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,297 Members | 1,429 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,297 IT Pros & Developers. It's quick & easy.

DoCmd.RunSQL Update giving Run Time Error

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.RunSQL "UPDATE Opportunities SET Opportunities.[Calls Made] = " & Me.[Calls Made].Value + 1 _
  2.     & "WHERE SSN=" & [SSN] & " " _
  3.     & "AND Opportunities.Campaign ='" & Me.[Campaign] & "' "
  4.     DoCmd.RunSQL "UPDATE Opportunities SET [Priority] = " & 0 _
  5.     & "WHERE SSN=" & [SSN] & " " _
  6.     & "AND Campaign ='" & [Campaign] & "' "
  7.     DoCmd.RunSQL "UPDATE Opportunities SET [Is Record Locked for Viewing] = False " _
  8.     & "WHERE SSN=" & [SSN] & " " _
  9.     & "AND Campaign ='" & [Campaign] & "' "
  10.     Requery
  11.     DoCmd.RunSQL "UPDATE Opportunities SET [Is Record Locked for Viewing] = True " _
  12.     & "WHERE SSN=" & [SSN] & " " _
  13.     & "AND Campaign ='" & [Campaign] & "' "
  14.     Me.CalledCheckBox = False
  15.     Me.DidntCallCheckBox = False
Feb 8 '14 #1
Share this Question
Share on Google+
9 Replies


zmbd
Expert Mod 5K+
P: 5,397
[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:
Expand|Select|Wrap|Line Numbers
  1. DIM strSQL as string
  2. strSQL = (replace with your string)
  3. '
  4. 'now you can insert a debug print here for troubleshooting
  5. ' - press <ctrl><g> to open the immediate window
  6. ' - you can now cut and paste this information for review!
  7. '
  8. debug.print "Your criteria = " & strSQL
  9. '
  10. 'now use the string in your code:
  11. (replace this in this case the docmd.runsql)
  12.  
If you will make these little changes and post back the resolved string we can help you tweak the code.
Feb 8 '14 #2

zmbd
Expert Mod 5K+
P: 5,397
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/insigh...h-class-module
SHA
http://bytes.com/topic/access/insigh...m-vba-vbscript
AES
http://bytes.com/topic/access/insigh...m-vba-vbscript
RC4
http://bytes.com/topic/access/insigh...m-vba-vbscript

!A BIG THANK YOU TO: TOPHER23, NEOPA, RABBIT, THESMILEYCODER, AND OTHERS FOR CREATING, COMMENTING, AND MAINTAINING THE ABOVE LINKS!
Feb 8 '14 #3

100+
P: 294
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:
Expand|Select|Wrap|Line Numbers
  1.     & "WHERE SSN=" & [SSN] & " " _
  2.     & "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
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 8 '14 #4

P: 3
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.
Feb 10 '14 #5

NeoPa
Expert Mod 15k+
P: 31,768
@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.
Feb 10 '14 #6

zmbd
Expert Mod 5K+
P: 5,397
sorry, maybe a little over reaction with the SSN, I assumed
ʕᴥʔ
Feb 10 '14 #7

P: 3
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!
Feb 11 '14 #8

zmbd
Expert Mod 5K+
P: 5,397
I work for Honey and Chocolate!
ʕᴥʔ
Feb 11 '14 #9

NeoPa
Expert Mod 15k+
P: 31,768
A trick once learned, never forgotten :-)
Feb 12 '14 #10

Post your reply

Sign in to post your reply or Sign up for a free account.