Delete command to update record

[angie]

Hi I have a form (service calls)where calls are entered once the calls are completed I go to the record and click on invoice button that opens the invoice form and I create a invoice (they are linked by service call Id) I would like to create a code that would delete the "service calls " form once the invoice is created.

Form were call is created "service calls"
form were invoice is created "invoice"
link by "service call id"

Thank you Angie

[ItsMe]

Are you asking how to delete a record? You mention a form in your post. Instead of deleting a record that is associated with an invoice, why not update the record as being invoiced? I would create a Boolean field/column in the original table to indicate the invoice status or just rely on the ID relationship between the two tables. I would not delete any records. Deleting related records goes against convention.

[angie]

I have a form that is just used for calls that has date time and who will do call, once it is completed I create a invoice in detail so I just need to delete the call I have no need to keep it once it is invoiced. I have a customer record that is never deleted.

[ItsMe]

Here is some code that you can place in a click event. You will need to adjust the text in red to match the form's control name, table name, and field name. This will delete a single record in the table that is associated with the ID field currently displayed in the form

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblName", dbOpenDynaset)
    If rs.EOF Then
        rs.Close
        Set rs = Nothing
    Exit Sub
    End If
rs.FindFirst "[IDFieldName] = " & Me.IDControlName.Value
    If rs.NoMatch Then
        MsgBox "Could Not Find Record to delete."
        rs.Close
        Set rs = Nothing
    Exit Sub
    End If
rs.Delete
    MsgBox "The record has been deleted."
rs.Close
Set rs = Nothing

[angie]

That works great. I add to Requrey the attached form also. Do I have to leave the pop up that record has been deleted. Thanks again Angie

[ItsMe]

it is perfectly OK to remove the following

MsgBox "The record has been deleted."

or any MsgBox code for that matter.

[angie]

Thank you very much. Have a Merry Christmas.