1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: ACCESS RunSql UPDATE almost Working

Discussion in 'Business Applications' started by syco0601, Dec 7, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. syco0601

    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
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  3. syco0601

    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
     
  4. syco0601

    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
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1030149

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice