I have two forms, a datasheet form that shows invoices, and a modal form that shows a single invoice. There's a column on the datasheet form that displays "Edit" in every cell; the user clicks 'Edit' and it opens the specific invoice in the modal form:
After the user makes whatever changes to the record and clicks OK the modal form closes and then Me.Recordset.Requery on the datasheet runs and displays the updated information in the row corresponding to the invoice the user just edited. This works.
I added a 'Delete Record' button to the modal invoice form which runs this:
(This is based on code I hobbled together from the Access Bible.)
This deletes the record and closes the modal invoice form, but the record in question still appears on the datasheet form. It only goes away after you change a filter option in one of the datasheet columns. I'm not sure why.
Ideally I don't want the code that refreshes the datasheet form to reside in the modal form procedure because this modal form will be called from other forms in the future.
Help?
Thanks in advance!
Code:
Private Sub Edit_Click()
DoCmd.OpenForm "InvoiceModalForm", , , WhereCondition:="[INVOICE_NUM_PK]=" & Me!INVOICE_NUM_PK
Me.Recordset.Requery
End Sub
After the user makes whatever changes to the record and clicks OK the modal form closes and then Me.Recordset.Requery on the datasheet runs and displays the updated information in the row corresponding to the invoice the user just edited. This works.
I added a 'Delete Record' button to the modal invoice form which runs this:
Code:
Private Sub DeleteInvoiceRecordButton_Click()
Dim lAnswer As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSql As String
sSql = "SELECT * FROM Invoices " _
& "WHERE INVOICE_NUM_PK = " & Me.INVOICE_NUM_PK & ";"
Set db = CurrentDb
Set rs = db.OpenRecordset(sSql, dbOpenDynaset)
lAnswer = MsgBox("Are you sure you want to delete this Invoice?", vbQuestion + vbYesNo, "Delete Invoice")
If lAnswer = vbYes Then
With rs
If Not .EOF Then
.Delete
End If
End With
End If
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.Close
End Sub
This deletes the record and closes the modal invoice form, but the record in question still appears on the datasheet form. It only goes away after you change a filter option in one of the datasheet columns. I'm not sure why.
Ideally I don't want the code that refreshes the datasheet form to reside in the modal form procedure because this modal form will be called from other forms in the future.
Help?
Thanks in advance!