472,146 Members | 1,470 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,146 software developers and data experts.

VBA not recognizing specific record to update

82 64KB
Hi. ACCESS ADP with SQL Server 2008. Have code that determines what a new overall score should be. The result shows in a message box with a Yes/No to update. When I select yes, getting a message about column name being wrong. it thinks rs!IDNumber is the column name when in fact it is the ID field for the record I want to update. Any help would be appreciated. Thanks.
Expand|Select|Wrap|Line Numbers
  1. tmpSQL = "Update PracticeTable " _
  2. "Set PracticeTable.SortOverall=" & new_sort _
  3. & " where PracticeTable.[IDNumber]=" & rs!IDNumber
Apr 4 '13 #1
6 1118
5,501 Expert Mod 4TB
Expand|Select|Wrap|Line Numbers
  1. debug.print tmpSQL
  2. Stop
on the line just below tmpSQL=...when the code hits the stop
Check that your string is resolving correctly.
Apr 9 '13 #2
82 64KB
Thanks but it was a problem of missing quotes around the rs!OPNumber
Apr 10 '13 #3
12,516 Expert Mod 8TB
Do you understand the reason why that would cause an error?
Apr 10 '13 #4
82 64KB
Not really other than it must be required.
Apr 12 '13 #5
12,516 Expert Mod 8TB
The reason is because your IDNumber field is a string.
12345 is a number.
'12345' is a string.
12345 <> '12345' They are not the same even though they may look the same. They are stored differently in the database.

To tell SQL that something is a string, you surround the value in quotes.
Apr 12 '13 #6
32,499 Expert Mod 16PB
A couple of links you may find helpful Barbara :
Quotes (') and Double-Quotes (") - Where and When to use them.
Before Posting (VBA or SQL) Code.
Apr 13 '13 #7

Post your reply

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

Similar topics

5 posts views Thread by R Duncan | last post: by
4 posts views Thread by BerkshireGuy | last post: by
reply views Thread by Saiars | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.