problem after deleting a record

[antonyx]

ok.. i think there is some problem with this code..basically there are a few listboxes and my user chooses a job and an invoice and when they are both highlighted they can 'add to invoice' or 'remove from invoice'

Private Sub btnRemoveJob_Click()
Dim strSql As String
If Nz(Me.[lstInvoiceRefs], -1) = -1 Or Nz(Me.[lstInvoice], -1) = -1 Then
    MsgBox "Please select a Job to remove!", vbExclamation
    Exit Sub
End If
strSql = "DELETE FROM tblJobInvoice WHERE (((tblJobInvoice.fkJobRef)= '" & Me.[lstInvoice] & "'));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdSaveRecord
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Me.lstNoInvoice.Requery
Me.lstInvoice.Requery
Me.lstInvoiceRefs.Requery
End Sub

it successfully deletes a record but then on the same form session i get the following problem trying to add a record:

Private Sub btnAddJob_Click()
If Nz(Me.[lstInvoiceRefs], -1) = -1 Or Nz(Me.[lstNoInvoice], -1) = -1 Then
    MsgBox "Please select a Job AND an Invoice!", vbExclamation
    Exit Sub
End If
[b]Me.txtjobref = Me.lstNoInvoice[/b]
Me.txtInvoiceRef = Me.lstInvoiceRefs
DoCmd.RunCommand acCmdSaveRecord
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Me.lstNoInvoice.Requery
Me.lstInvoice.Requery
Me.lstInvoiceRefs.Requery
End Sub

this is the error i am gettin on the above bold line..

Run-time error '-2147352567 (80020009)':
Record in table 'tblJobInvoice' was deleted by another user.

am i not saving things properly..

 

[antonyx]

ok.. please look at this picture.. ive explained a lot more

http://www.londonheathrowcars.com/xthis.jpg

when you look at that picture.. and look at the code above.. and see where the error is on the bold line..

maybe i am asking if there is a better way to accomplish this feat..

any opinions at all?

 

[Bat17]

Antony
not time to check it all out but try a form requery at the end of the sub.

peter

 

[antonyx]

yes i think that has got rid of the problem.. im trying another delete statement where i am trying to delete from two tables..

here is what i tried using..

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, tblJobInvoice WHERE (((tblInvoice.InvoiceRef)= '" & Me.[lstInvoiceRefs] & "')) AND (((tblJobInvoice.fkInvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));"
DoCmd.SetWarnings False
[b]DoCmd.RunSQL strSql[/b]
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdSaveRecord
Me.lstNoInvoice.Requery
Me.lstInvoice.Requery
Me.lstInvoiceRefs.Requery
Me.Requery
End Sub

it says specify the table containing the records you want to delete

run time 3128.

have i laid out the sql incorrectly?

 

[antonyx]

i tried like this aswell and it only ran the first line of sql

strSql = "DELETE FROM tblInvoice WHERE (((tblInvoice.InvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));" 
strSql = "DELETE FROM tblJobInvoice WHERE (((tblJobInvoice.fkInvoiceRef)= '" & Me.[lstInvoiceRefs] & "'));"