468,114 Members | 2,029 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,114 developers. It's quick & easy.

SQL VBA Delete statement not working

77 64KB
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.

Expand|Select|Wrap|Line Numbers
  1.  Dim dbs As Database
  2. Set dbs = CurrentDb
  3.  
  4. Dim strOrder As String
  5. strOrder = Me.TxtOrderNo
  6. MsgBox strOrder, vbOKOnly
  7.  
  8.  
  9. dbs.Execute "Delete [DBA_inv_payment]* from" _
  10. & "[DBA_inv_payment] WHERE [pymt_invoice_no] = " & strOrder & ";"
  11.  
  12. dbs.Execute "Delete [DBA_inv_pymt_deduction]* from" _
  13. & "[DBA_inv_pymt_deduction] WHERE [pymd_invoice_no] = " & strOrder & ";"
  14.  
  15.  
  16. dbs.Close
  17.  
  18. Me.Recordset.Requery 
3 Weeks Ago #1

✓ answered by 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) :
Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM [DBA_inv_payment] WHERE pymt_invoice_no = '123456';
Perfectly valid. In your earlier one though :
Expand|Select|Wrap|Line Numbers
  1. 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.

8 3629
twinnyfo
3,638 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. Dim strOrder As String
  2. Dim strSQL As String
  3. Dim dbs As Database
  4.  
  5. strOrder = Me.TxtOrderNo
  6. MsgBox strOrder, vbOKOnly
  7.  
  8. strSQL = _
  9.     "DELETE [DBA_inv_payment].* " & _
  10.     "FROM [DBA_inv_payment] " & _
  11.     "WHERE [pymt_invoice_no] = '" & strOrder & "';"
  12.  
  13. Set dbs = CurrentDb
  14. dbs.Execute strSQL
  15.  
  16. strSQL = _
  17.     "DELETE [DBA_inv_pymt_deduction].* " & _
  18.     "FROM [DBA_inv_pymt_deduction] " & _
  19.     "WHERE [pymt_invoice_no] = '" & strOrder & "';"
  20.  
  21. dbs.Execute strSQL
  22. dbs.Close
  23. Set dbs = Nothing
  24.  
  25. Me.Recordset.Requery
Hope this hepps!
3 Weeks Ago #2
NeoPa
32,023 Expert Mod 16PB
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).
3 Weeks Ago #3
Joe Y
77 64KB
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.

Expand|Select|Wrap|Line Numbers
  1.   DoCmd.RunCommand acCmdUndo
  2.  
Joe
3 Weeks Ago #4
NeoPa
32,023 Expert Mod 16PB
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().
3 Weeks Ago #5
Joe Y
77 64KB
NeoPa,

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

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo CmdUndo_Click_Err
  2. On Error Resume Next
  3.  
  4. DoCmd.RunCommand acCmdUndo
  5.  
  6. Dim dbs As Database
  7. Set dbs = CurrentDb
  8.  
  9. Dim strOrder As String
  10. strOrder = Me.TxtOrderNo
  11. MsgBox strOrder, vbOKOnly
  12.  
  13.  dbs.Execute "Delete [DBA_inv_payment]* from" _
  14. & "[DBA_inv_payment] WHERE [pymt_invoice_no] = " & strOrder & ";"
  15.  
  16. dbs.Execute "Delete [DBA_inv_pymt_deduction]* from" _
  17. & "[DBA_inv_pymt_deduction] WHERE [pymd_invoice_no] = " & strOrder & ";"
  18.  
  19.  dbs.Close
  20.  
  21. Me.Recordset.Requery
  22.  
  23.     If (MacroError <> 0) Then
  24.         Beep
  25.         MsgBox MacroError.Description, vbOKOnly, ""
  26.     End If
  27.  
  28. CmdUndo_Click_Exit:
  29.     Exit Sub
  30.  
  31. CmdUndo_Click_Err:
  32.     MsgBox Error$
  33.     Resume CmdUndo_Click_Exit
  34.  
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.

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo CmdUndo_Click_Err
  2.     On Error Resume Next
  3.  
  4. Dim dbs As Database
  5.     Dim qdf As QueryDef
  6.  
  7.     Set dbs = CurrentDb
  8.  
  9.     dbs.Execute "DELETE * FROM " _
  10.         & "[DBA_inv_payment] WHERE pymt_invoice_no = '" & [TxtOrderNo] & "';"
  11.  
  12.     dbs.Execute "DELETE * FROM " _
  13.         & "DBA_inv_pymt_deduction WHERE pymd_invoice_no = '" & [TxtOrderNo] & "';"
  14.  
  15.     dbs.Close
  16.  
  17.     DoCmd.RunCommand acCmdUndo
  18.  
  19. Me.Recordset.Requery
  20.  
  21.     If (MacroError <> 0) Then
  22.         Beep
  23.         MsgBox MacroError.Description, vbOKOnly, ""
  24.     End If
  25.  
  26. CmdUndo_Click_Exit:
  27.     Exit Sub
  28.  
  29. CmdUndo_Click_Err:
  30.     MsgBox Error$
  31.     Resume CmdUndo_Click_Exit
  32.  
I do want to thank twinnyfo and NeoPa for your helps. I always know where to go when I need help.

Thanks,
Joe
3 Weeks Ago #6
NeoPa
32,023 Expert Mod 16PB
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) :
Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM [DBA_inv_payment] WHERE pymt_invoice_no = '123456';
Perfectly valid. In your earlier one though :
Expand|Select|Wrap|Line Numbers
  1. 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.
3 Weeks Ago #7
DimBulb
2 Bit
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
2 Weeks Ago #8
NeoPa
32,023 Expert Mod 16PB
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:
Expand|Select|Wrap|Line Numbers
  1. strVal = "SELECT * FROM [MyTable] WHERE ([Name] Like 'Acme*')"
Less Good:
Expand|Select|Wrap|Line Numbers
  1. strVal = "SELECT * FROM [MyTable] WHERE ([Name] Like ""Acme*"")"
Not at all Good:
Expand|Select|Wrap|Line Numbers
  1. 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.
2 Weeks Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by AlanAylett | last post: by
1 post views Thread by Ian Davies | last post: by
1 post views Thread by Matt | last post: by
tolkienarda
3 posts views Thread by tolkienarda | last post: by
3 posts views Thread by zaiena | last post: by
1 post views Thread by Ample | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.