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

RunSql UPDATE almost Working

P: 2
I am working on a document tracking database (access) and I want the user to select a document number from a combo box, which then updates all the textboxes. Then the user can update the fields then click an update button. This almost works but when I run the below SQL and supplemental code. I get the below error (I had to put some generic names in for confidentiality aslo "Test123" is entered into textbox:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command55_Click()
  2. Dim TempStr As String
  3.  
  4. TempStr = Me.dcrSubjectOfChange_TB.Value //from Combo
  5.  
  6. DoCmd.RunSQL ("UPDATE [DB_Company] SET DB_Company.SubjectOfChange = " & TempStr & "WHERE DB_Company.DocNum = Forms!Form_Edit_Current!Doc_Num_CB")
  7.  
  8. End Sub
Syntax error (missing operator) in query expression 'Test123Where DB_Company.DocNum = Forms!Form_Edit_Current!Doc_Num_CB

//////////////////////////////////////////

So this leads me to believe the string variable is getting recognized correctly so therefore I must have a sql syntax error. Thanks in Advance
Dec 7 '11 #1

✓ answered by Rabbit

You're missing a space.
... TempStr & " WHERE ...
Basically, if TempStr is bob then
TempStr & "where" = bobwhere
TempStr & " where" = bob where

Also, if SubjectOfChange is a text data type, you will need to surround TempStr with quotes in the SQL string.

Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,315
You're missing a space.
... TempStr & " WHERE ...
Basically, if TempStr is bob then
TempStr & "where" = bobwhere
TempStr & " where" = bob where

Also, if SubjectOfChange is a text data type, you will need to surround TempStr with quotes in the SQL string.
Dec 7 '11 #2

P: 2
Thanks a lot. Should have thought of that myself, it was right in front of me. Works like a charm now

@Rabbit
Dec 7 '11 #3

Post your reply

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