sql delete from 2 tables, bad code layout

[antonyx]

this code doesnt delete from both tables.. how should i structure this code to delete two records from 2 separate tables..

Private Sub btnDeleteInvoice_Click()
Dim strSql As String
If Nz(Me.[lstInvoiceRefs], -1) = -1 Then
    MsgBox "Please select an Invoice to delete!", vbExclamation
    Exit Sub
End If
strSql = "DELETE FROM tblInvoice WHERE (((tblInvoice.InvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));"
strSql = "DELETE FROM tblJobInvoice WHERE (((tblJobInvoice.fkInvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdSaveRecord
Me.lstNoInvoice.Requery
Me.lstInvoice.Requery
Me.lstInvoiceRefs.Requery
Me.Requery
End Sub

 

[ShaneMan]

this code doesnt delete from both tables.. how should i structure this code to delete two records from 2 separate tables..

Private Sub btnDeleteInvoice_Click()
Dim strSql As String
If Nz(Me.[lstInvoiceRefs], -1) = -1 Then
    MsgBox "Please select an Invoice to delete!", vbExclamation
    Exit Sub
End If
strSql = "DELETE FROM tblInvoice WHERE (((tblInvoice.InvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));"
strSql = "DELETE FROM tblJobInvoice WHERE (((tblJobInvoice.fkInvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdSaveRecord
Me.lstNoInvoice.Requery
Me.lstInvoice.Requery
Me.lstInvoiceRefs.Requery
Me.Requery
End Sub

Hey antonyx,

I'm not positive on this but if it's deleting from one table but not both then it could be because your using stSql for both tables. Try Dimming another str and give it a different name and use it for the secound table and see what happens. Just a thought.

HTH,
Shane

 

[antonyx]

ok.. let me do that...

 

[antonyx]

yes.. i thought there was a way to incorporate both statements into one line.. i was wrong..

this works..
it may not be the neatest but it works..

thank you

Private Sub btnDeleteInvoice_Click()
Dim strSql, strsql2 As String
If Nz(Me.[lstInvoiceRefs], -1) = -1 Then
    MsgBox "Please select an Invoice to delete!", vbExclamation
    Exit Sub
End If
strSql = "DELETE FROM tblInvoice WHERE (((tblInvoice.InvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));"
strsql2 = "DELETE FROM tblJobInvoice WHERE (((tblJobInvoice.fkInvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
DoCmd.RunSQL strsql2
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdSaveRecord
Me.lstNoInvoice.Requery
Me.lstInvoice.Requery
Me.lstInvoiceRefs.Requery
Me.Requery
End Sub

 

[ShaneMan]

yes.. i thought there was a way to incorporate both statements into one line.. i was wrong..

this works..
it may not be the neatest but it works..

thank

Your welcome. Glad I could help.

Shane

 

[Moniker]

Here's your code, a lot cleaner:

Private Sub btnDeleteInvoice_Click()

    If Nz(Me.[lstInvoiceRefs], -1) = -1 Then
        MsgBox "Please select an Invoice to delete!", vbExclamation
        Exit Sub
    End If

    CurrentDb.Execute "DELETE FROM tblInvoice WHERE (((tblInvoice.InvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));"
    CurrentDb.Execute "DELETE FROM tblJobInvoice WHERE (((tblJobInvoice.fkInvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));"
    DoCmd.RunCommand acCmdSaveRecord
    Me.lstNoInvoice.Requery
    Me.lstInvoice.Requery
    Me.lstInvoiceRefs.Requery
    Me.Requery

End Sub

Using CurrentDb.Execute eliminates the need for SetWarnings, and there's no need to assign the SQL to strings to run it.

 

[antonyx]

thanks again