Recordset.Requery still showing deleted record (1 Viewer)

[Sheridan]

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:

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:

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 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!

 

[theDBguy]

You did say "modal," but I don't see you using the acDialog argument in your code. Perhaps you can review the following info:

Pop-up, Modal, or Dialog Forms

This article is an excerpt, reprinted with permission, from the book " Professional Access 2013 Programming ." Pop-up, Modal, or Dialog F...

thedbguy.blogspot.com

 

[Sheridan]

You did say "modal," but I don't see you using the acDialog argument in your code.

That fixed it! Thanks!

Me.Recordset.Requery was definitely running before because I could see the edits I made in the modal form reflected in the datasheet; it just wasn't removing the row for the deleted record. How did that acDialog argument fix that?

 

[MajP]

Access is different than other VBA forms. When you open a form that is modal in Access it does not stop code execution in the calling form. The code keeps on running. Other VBA forms and languages modal usually stops code execution from where it was called.
So that requery happens as soon as you open the other form InvoiceModalForm. Actually the other form goes through its opening events, but then the code control returns back to the calling form and the requery happens.

ACDIALOG stops code execution in the calling form and does not start again until the called form is closed (or hidden).