SQL VBA Delete statement not working

[Joe Y]

Hi,

I have an access database that handle sales and shipping. In the shipment form, user can click a button to take following actions.

1. Check largest invoice number in the order table [DBA_order_header] then add 1 as new invoice number to be assigned to current order record.
2. Add new record to two tables associate to invoice payment: [DBA_inv_payment] and [DBA_inv_pymt_deduction].

There is also a cancel button to supposedly cancel the assignment of new invoice number in tables [DBA_order_header] and remove records in tables [DBA_inv_payment], and [DBA_inv_pymt_deduction]

However, the cancel button with following SQL vba statement is not working on deleting records in the two payment tables. After user click the Cancel button, the newly added records in these two table are not deleted. I cannot figure out what went wrong with the vba codes. Can someone point out what is wrong with the vba code? Thanks in advance.

 Dim dbs As Database
Set dbs = CurrentDb
 
Dim strOrder As String
strOrder = Me.TxtOrderNo
MsgBox strOrder, vbOKOnly
 
 
dbs.Execute "Delete [DBA_inv_payment]* from" _
& "[DBA_inv_payment] WHERE [pymt_invoice_no] = " & strOrder & ";"
 
dbs.Execute "Delete [DBA_inv_pymt_deduction]* from" _
& "[DBA_inv_pymt_deduction] WHERE [pymd_invoice_no] = " & strOrder & ";"
 
 
dbs.Close
 
Me.Recordset.Requery 

[twinnyfo]

Joe,

Looks like you have some issues with periods and spaces (and a typo in the second SQL string). Also, I also recommend using variables for your SQL strings. This allows you to troubleshoot if you have to:

Dim strOrder As String
Dim strSQL As String
Dim dbs As Database
 
strOrder = Me.TxtOrderNo
MsgBox strOrder, vbOKOnly
 
strSQL = _
    "DELETE [DBA_inv_payment].* " & _
    "FROM [DBA_inv_payment] " & _
    "WHERE [pymt_invoice_no] = '" & strOrder & "';"
 
Set dbs = CurrentDb
dbs.Execute strSQL
 
strSQL = _
    "DELETE [DBA_inv_pymt_deduction].* " & _
    "FROM [DBA_inv_pymt_deduction] " & _
    "WHERE [pymt_invoice_no] = '" & strOrder & "';"
 
dbs.Execute strSQL
dbs.Close
Set dbs = Nothing
 
Me.Recordset.Requery

Hope this hepps!

[NeoPa]

Indeed. There are actually a number of issues with the SQL code you are trying to use. Neither will execute.

Twinny's advice to look at the SQL before running it is sound.

I would also recommend that your VBA Dim for dbs uses DAO.Database to avoid confusion.

For completeness, why not check out something I wrote a while back to help with using SQL from within your code (How to Debug SQL String).

[Joe Y]

Hi twinnyfo,

For some reason the delete SQL is still not working after I copied the code to my database. I wonder if it has something to do with these codes locating inside of a "Cancel " button's click event? I know this may sound silly, but I can't think of anything else.

Since I have this speculation I have tried to comment ( ' ) the line below. However, this did not resolve the issue.

  DoCmd.RunCommand acCmdUndo
 

Joe

[NeoPa]

Hi Joe.

As we've both advised to look at the SQL outside of the VBA code I'm wondering if you could progress the issue on that basis.

It's much easier for all concerned if we (and particularly you) can see the SQL code exactly as it's passed on to dbs.Execute().

Even without this we can, and have already obviously, explained that the SQL is faulty. It won't work. I would suggest that is where you need to focus your attention and, without wishing to labour the point too heavily, that should start by you displaying (and sharing here obviously) the exact SQL created by your VBA that gets passed to dbs.Execute().

[Joe Y]

NeoPa,

The original VBA in the Cancel button's Click Event is -

On Error GoTo CmdUndo_Click_Err
On Error Resume Next
 
DoCmd.RunCommand acCmdUndo
 
Dim dbs As Database
Set dbs = CurrentDb
 
Dim strOrder As String
strOrder = Me.TxtOrderNo
MsgBox strOrder, vbOKOnly
 
 dbs.Execute "Delete [DBA_inv_payment]* from" _
& "[DBA_inv_payment] WHERE [pymt_invoice_no] = " & strOrder & ";"
 
dbs.Execute "Delete [DBA_inv_pymt_deduction]* from" _
& "[DBA_inv_pymt_deduction] WHERE [pymd_invoice_no] = " & strOrder & ";"
 
 dbs.Close
 
Me.Recordset.Requery
 
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
 
CmdUndo_Click_Exit:
    Exit Sub
 
CmdUndo_Click_Err:
    MsgBox Error$
    Resume CmdUndo_Click_Exit
 

I finally made it work by lots of trial and error. I think my Access 2010 only works when referencing a value in the form's text box is by naming it directly in the SQL. So instead of using strOrder = Me.TxtOrderNo, I placed TxtOrderNo directly in the SQL and it works.

I know I may get criticized as nonsense by saying this, but I don't know what else to explain it. Here is the working VBA.

On Error GoTo CmdUndo_Click_Err
    On Error Resume Next
 
Dim dbs As Database
    Dim qdf As QueryDef
 
    Set dbs = CurrentDb
 
    dbs.Execute "DELETE * FROM " _
        & "[DBA_inv_payment] WHERE pymt_invoice_no = '" & [TxtOrderNo] & "';"
 
    dbs.Execute "DELETE * FROM " _
        & "DBA_inv_pymt_deduction WHERE pymd_invoice_no = '" & [TxtOrderNo] & "';"
 
    dbs.Close
 
    DoCmd.RunCommand acCmdUndo
 
Me.Recordset.Requery
 
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
 
CmdUndo_Click_Exit:
    Exit Sub
 
CmdUndo_Click_Err:
    MsgBox Error$
    Resume CmdUndo_Click_Exit
 

I do want to thank twinnyfo and NeoPa for your helps. I always know where to go when I need help.

Thanks,
Joe

[NeoPa]

Joe Y:
I know I may get criticized as nonsense by saying this, but I don't know what else to explain it.

I often wonder if others have difficulty understanding what I'm saying too.

Ultimately, we're pretty happy if you get it working. In this case it looks like you used lieral values embedded within your SQL string rather than references to the same items. That's often necessary as the part of Access that interprets & runs the SQL for you is known as The Expression Service and it's totally outside of VBA. There are many things you can reference in exactly the same way - but by no means is that true of everything.

In this case though you've built the values into the SQL string itself using VBA (It's so important to understand what bits are done by which process otherwise you hit limitations you won't understand.) rather than trying to get SQL to interpret the references into the required values.

The technique for helping with this that we're trying to get you to understand helps with this. If you print the SQL String out then you have a better and clearer understanding of what SQL is doing. Not Access, or VBA, but SQL (or The Expression Service if you prefer). It's much easier to spot invalid SQL when you see the SQL string itself rather than the VBA code that's only used to create the SQL.

To illustrate, in your latest post the first string would be (Assuming [TxtOrderNo]==123456) :

DELETE * FROM [DBA_inv_payment] WHERE pymt_invoice_no = '123456';

Perfectly valid. In your earlier one though :

Delete [DBA_inv_payment]* from[DBA_inv_payment] WHERE [pymt_invoice_no] = 123456;"

I can't say if the missing space before the * or after "from" will cause any real problems but they certainly jump out at you as dodgy. The single quotes (') though are so much easier to spot as missing when looking at the SQL directly though.

This is why the article I linked earlier (How to Debug SQL String) is so, so helpful for those starting to work in SQL using VBA. If you follow the advice in there you will find it a lot easier to get your SQL right. After a while you'll rarely need to bother - but you'll always have it as a tool in your coding arsenal.

[DimBulb]

Hi Joe Y,

one of the things that might help you in formulating SQL within VBA is ..... & chr$(34) & around both sides of the string parameter in the SQL statement. chr$(34) is the ASCII code for the double quote. if you concatenate it in there, it forces the double quote in round the string parameter.

The other thing that might cause a rejected delete is if you have a parent child relation e.g. order header/order detail - if you delete a header record, you might end up with orphaned records in the detail table, and the relation might kick out the delete.
So if you really want to do that, you need to delete the detail records first for that header using the relevant key, then delete the header record.
rgds
DimBulb

[NeoPa]

Hi DimBulb.

I hope you appreciate that moniker will stay with you now even when you progress & shine brightly in the Access firmament :-D

Please excuse me while I present arguments against some of what you've suggested. I do it with an understanding of your positive intent, yet I see advice there that I don't think is good advice - but let me explain why.

String parameters in SQL should certainly be enclosed in quotes - but actually they should be single quotes in preference to those double ones that VBA uses. See Quotes (') and Double-Quotes (") - Where and When to use them.

I never advise the use of Chr$() (Nor even the less antiqated Chr() that is recommended for the conversion task nowadays.) as it is more clumsy and less easy to read than simply using the actual quote characters themselves. Where is the benefit of obscuring what you're writing by using ASCII codes when the character works perfectly well as it stands?

This only ever comes up when the same type of quotes are used in the SQL as the VBA - but as you can see that only ever occurs when you try to follow the example of Access and use double-quotes (") for your string literals. If ever you do feel you want to take that route then simply doubling them up gives you the quote character you need. Far less fussy and easier to read and work with than calling a function to convert a number into its ASCII equivalent.

Good:

strVal = "SELECT * FROM [MyTable] WHERE ([Name] Like 'Acme*')"

Less Good:

strVal = "SELECT * FROM [MyTable] WHERE ([Name] Like ""Acme*"")"

Not at all Good:

strVal = "SELECT * FROM [MyTable] WHERE ([Name] Like " & Chr(34) & "Acme*"  & Chr(34) & ")"

Let me stress that any and all well-meaning offerings are appreciated. Sometimes though, having less than optimal advice is better clarified so those reading these threads don't get sent in wrong directions.

As for related records this can certainly be an issue. There are more possibilities than covered here but very well worth being aware of.