Solved: ACCESS RunSql UPDATE almost Working

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

syco0601

Thread Starter
Joined
Dec 7, 2011
Messages
3
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:

Private Sub Command55_Click()
Dim TempStr As String

TempStr = Me.dcrSubjectOfChange_TB.Value //from Combo

DoCmd.RunSQL ("UPDATE [DB_Company] SET DB_Company.SubjectOfChange = " & TempStr & "WHERE DB_Company.DocNum = Forms!Form_Edit_Current!Doc_Num_CB")

End Sub

Error: 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
 

OBP

Joined
Mar 8, 2005
Messages
19,896
syco0601, welcome to the forum.
I am not that familiar with SQL as I prefer using Access queries.
Yes the string is being recognised, but 2 things come to mind.
1. the string has a ' in front but not after it.
2. there is no space between the string and the Where part of the Statement.
When I work with SQL I prefer to create a complete string first because you can use a msgbox to check how it looks.
and split the statement up on separate lines.
Like this
SQL = "SELECT Que_CallNumber.* " & _
"FROM Que_CallNumber " & _
"WHERE DealerCode = " & Me.Dealercode
Set rs = CurrentDb.OpenRecordset(SQL which is using a recordset instead of running the SQL as you want to do.
 

syco0601

Thread Starter
Joined
Dec 7, 2011
Messages
3
Thanks I appreciate the reply the error I'm getting is this message verbatim:

Error: Syntax error (missing operator) in query expression 'Test123Where DB_Company.DocNum = Forms!Form_Edit_Current!Doc_Num_CB

With no space between 123 and Where, if that is the missing space you were reffering to.

Also thanks for the tips for how to format SQL statements, Im fairly new and havenvt seen a "best practices" in my reading, but I will definitly do that from now on. Thanks Cole
 

syco0601

Thread Starter
Joined
Dec 7, 2011
Messages
3
I understand what you mean with the missing space, didnt account for it being somewhat of a stream of straigt letters and the space was needed. Works great! Appreciate the help
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top