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.