Recordset.Requery still showing deleted record

[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).

 

[arnelgp]

why don't you use SQL to delete the record?

Private Sub DeleteInvoiceRecordButton_Click()
    Dim lAnswer As Long
    
    Dim db As DAO.Database
    Dim sSql As String
    
    sSql = "DELETE * FROM Invoices " _
         & "WHERE INVOICE_NUM_PK = " & Me.INVOICE_NUM_PK & ";"
    
    Set db = CurrentDb

    
    lAnswer = MsgBox("Are you sure you want to delete this Invoice?", vbQuestion + vbYesNo, "Delete Invoice")
    
    If lAnswer = vbYes Then
        
        db.Execute sSQL, dbFailOnError
    
   End If
    Set db = Nothing
    
    DoCmd.Close
    
End Sub

 

[Sheridan]

why don't you use SQL to delete the record?

I'm not sure. I was copying almost verbatim an example of record deletion from the 2019 Access Bible:

To remove a record from a table, you use the ADO method Delete. The following code shows an ADO procedure for deleting a record from tblCustomerContacts:​

​

Public Sub DeleteContact(ContactID As Long)​

​

Dim db As DAO.Database​

Dim rs As DAO.Recordset​

Dim sSql As String​

​

​

sSql = "SELECT * FROM tblCustomerContacts " _​

& "WHERE ID = " & ContactID & ";"​

​

Set db = CurrentDb​

Set rs = db.OpenRecordset(sSql, dbOpenDynaset)​

​

With rs​

If Not .EOF Then​

.Delete 'Delete the record​

End If​

End With​

​

rs.Close​

Set rs = Nothing​

Set db = Nothing​

​

Alexander, Michael; Kusleika, Richard. Access 2019 Bible (Function). Kindle Edition.​

​

Is one technique more secure than the other? I'm not sure what practical difference it makes if they both work.

 

[dalski]

I wonder if it is ADO procedure. The rs is declared as 'Data Access Object'; so not ADO. But ADO used to be the default format for Access, then they changed it to DAO as the default. It's best to explicitly declare the type; as you have done.
There are differences between the two, different methods... available to each type. Best to stick with default DAO when learning. But certainly follow the book over what I say.

You should test for .BOF (beginning of file) also; not just .EOF. If the book hasn't mentioned this I'd recommend the below which was kindly recommended to me by the forum. The classes section is a bit lacking in the VBA book mentioned. Some e.g's could be better by focusing on user declared classes; opposed to Access' class objects which obfuscate the lesson greatly. But the rest of it is very good.

• Access 2010 Inside Out by Jeff Conrad & John Viescas
• Access 2010 VBA Inside Out by Andrew Couch