469,125 Members | 1,629 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

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 1015
zmbd
5,400 Expert Mod 4TB
Insert
Expand|Select|Wrap|Line Numbers
  1. debug.print tmpSQL
  2. Stop
on the line just below tmpSQL=...when the code hits the stop
<ctrl><g>
Check that your string is resolving correctly.
Apr 9 '13 #2
barbarao
82 64KB
Thanks but it was a problem of missing quotes around the rs!OPNumber
Apr 10 '13 #3
Rabbit
12,516 Expert Mod 8TB
Do you understand the reason why that would cause an error?
Apr 10 '13 #4
barbarao
82 64KB
Not really other than it must be required.
Apr 12 '13 #5
Rabbit
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
NeoPa
32,161 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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.