Results 1 to 8 of 8
  1. #1
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107

    I think I have a syntax error in my docmd.runsql "DELETE" statement



    Code:
            MsgBox ([NewOrderNumber])
            ' delete old records
            DoCmd.RunSQL "DELETE dbo_oeordhdr_sql.* FROM dbo_oeordhdr_sql WHERE dbo_oeordhdr_sql.ord_no=[NewOrderNumber]"
            DoCmd.RunSQL "DELETE dbo_oeordlin_sql.* FROM dbo_oeordlin_sql WHERE dbo_oeordlin_sql.ord_no=[NewOrderNumber]"
            DoCmd.RunSQL "DELETE dbo_iminvtrx_sql.* FROM dbo_iminvtrx_sql WHERE dbo_iminvtrx_sql.ord_no=[NewOrderNumber]"
    I am trying to delete records from three SQL tables that fit the criteria. NewOrderNumber is a variable field I have dimensioned and loaded with the correct data in this same subroutine that the above code appears. When it gets to the above code, it prompts me to enter NewOrderNumber ALL THREE TIMES. The message box confirms the field exists and has the proper data in it prior to running the RunSQL statements. I have tried removing the brackets from around NewOrderNumber with the same results. I have another instance where I am using a form field in the same code and it works as expected:

    Code:
            ' delete old records
            DoCmd.RunSQL "DELETE dbo_oeordhdr_sql.* FROM dbo_oeordhdr_sql WHERE dbo_oeordhdr_sql.ord_no=[Forms]![frmOrderHeaderEntry]![BBIOrderNumberTxt]"
            DoCmd.RunSQL "DELETE dbo_oeordlin_sql.* FROM dbo_oeordlin_sql WHERE dbo_oeordlin_sql.ord_no=[Forms]![frmOrderHeaderEntry]![BBIOrderNumberTxt]"
            DoCmd.RunSQL "DELETE dbo_iminvtrx_sql.* FROM dbo_iminvtrx_sql WHERE dbo_iminvtrx_sql.ord_no=[Forms]![frmOrderHeaderEntry]![BBIOrderNumberTxt]"
    Can someone tell me what I am doing wrong?

    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use queries instead of sql. They always get syntax correct.

    but,
    dbo_oeordhdr_sql.ord_no=[NewOrderNumber]

    has no reference to anything. you need the full path:
    Forms]![frmOrderHeaderEntry]![NewOrderNumber]

  3. #3
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    NewOrderNumber is a variable field in my VBA subroutine, not a form field.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe try changing the ending like this?

    DoCmd.RunSQL "DELETE dbo_oeordhdr_sql.* FROM dbo_oeordhdr_sql WHERE dbo_oeordhdr_sql.ord_no= " & [NewOrderNumber]
    DoCmd.RunSQL "DELETE dbo_oeordlin_sql.* FROM dbo_oeordlin_sql WHERE dbo_oeordlin_sql.ord_no= " & [NewOrderNumber]
    DoCmd.RunSQL "DELETE dbo_iminvtrx_sql.* FROM dbo_iminvtrx_sql WHERE dbo_iminvtrx_sql.ord_no= " & [NewOrderNumber]

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You don't surround variables in square brackets, no? Otherwise, Access thinks it's a field.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Quote Originally Posted by Bulzie View Post
    Maybe try changing the ending like this?

    DoCmd.RunSQL "DELETE dbo_oeordhdr_sql.* FROM dbo_oeordhdr_sql WHERE dbo_oeordhdr_sql.ord_no= " & [NewOrderNumber]
    DoCmd.RunSQL "DELETE dbo_oeordlin_sql.* FROM dbo_oeordlin_sql WHERE dbo_oeordlin_sql.ord_no= " & [NewOrderNumber]
    DoCmd.RunSQL "DELETE dbo_iminvtrx_sql.* FROM dbo_iminvtrx_sql WHERE dbo_iminvtrx_sql.ord_no= " & [NewOrderNumber]
    I tried using this code both with and without the brackets and I get this error both times:

    Run-time error '3464':
    Data type mismatch in criteria expression.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    What datatype is ord_no in your table? If numeric maybe try Val(NewOrderNumber):
    DoCmd.RunSQL "DELETE dbo_oeordhdr_sql.* FROM dbo_oeordhdr_sql WHERE dbo_oeordhdr_sql.ord_no= " & Val(NewOrderNumber)

    If Text maybe:
    DoCmd.RunSQL "DELETE dbo_oeordhdr_sql.* FROM dbo_oeordhdr_sql WHERE dbo_oeordhdr_sql.ord_no= '" & NewOrderNumber & "'"

  8. #8
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Quote Originally Posted by Bulzie View Post
    What datatype is ord_no in your table? If numeric maybe try Val(NewOrderNumber):
    DoCmd.RunSQL "DELETE dbo_oeordhdr_sql.* FROM dbo_oeordhdr_sql WHERE dbo_oeordhdr_sql.ord_no= " & Val(NewOrderNumber)

    If Text maybe:
    DoCmd.RunSQL "DELETE dbo_oeordhdr_sql.* FROM dbo_oeordhdr_sql WHERE dbo_oeordhdr_sql.ord_no= '" & NewOrderNumber & "'"
    The second option was correct! I'm sorry if I threw everyone off by not mentioning that this was a text field. The form field was also a text field and didn't require the single quotes, so this confused me. I guess since I was dealing with an object and not a field, it works differently.

    Thanks to everyone that responded!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-11-2015, 07:48 AM
  2. Replies: 3
    Last Post: 01-27-2015, 12:25 PM
  3. Compile Error: Syntax Error in DoCmd.RunSQL Statement
    By Evilferret in forum Programming
    Replies: 1
    Last Post: 08-27-2012, 12:32 PM
  4. Replies: 4
    Last Post: 07-25-2012, 04:01 AM
  5. Syntax Error with DoCmd.RunSQL
    By dandoescode in forum Programming
    Replies: 2
    Last Post: 06-25-2012, 11:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums