Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    79

    Unanswered: Update table from form.

    Hey almighty VBA gods.

    I'm trying to run an update query on a table from a form and just can't seem to get the syntax right.

    Code:
    DoCmd****nSQL "UPDATE tablename SET tablefield = [Forms]![formname]![formtxtobject] WHERE tablefield = [Forms]![formname]![formtxtobject]"
    I'm sure I'm missing about 20 "s or so. I have no variables declared and this statement is run on a buttons Click event.

    To be honest SQL statements wrap my brain in knots

    Any help out there?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    for the SQL engine to parse values from control it has to drop out intoi VBA mode

    Code:
    dim strSQl as string
    strSQL = "UPDATE tablename
     SET tablefield = " & [Forms]![formname]![formtxtobject] 
    & " WHERE tablefield = " & [Forms]![formname]![formtxtobject]
    if any of the values are string/text/char then you need to use either a speech OR quote mark to delineate where the value starts and ends

    Code:
    dim strSQl as string
    strSQL = "UPDATE tablename 
    SET tablefield = '" & [Forms]![formname]![formtxtobject] 
    & "' WHERE tablefield = '" & [Forms]![formname]![formtxtobject] & "'"
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2012
    Posts
    79
    Rgr! Thanks for the assistance!

  4. #4
    Join Date
    Nov 2012
    Posts
    79
    Regarding this thread I've run into issue with apostrophes in the record data.
    Code:
    dim strSQl as string
    strSQL = "UPDATE tablename 
    SET tablefield = '" & [Forms]![formname]![formtxtobject] 
    & "' WHERE tablefield = '" & [Forms]![formname]![formtxtobject] & "'"
    Using the above form if tablefield = Butter's, then I get a syntax error. If tablefield = Butters it works great.

    I've had the same issue using a DLookup but was able to fix it using only double-quotes. I've tried it with this issue but haven't figured out where to put them all!

    Is this a viable solution and if so, how would I go about doing this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •