Delete records on subform

[AMWO]

Hi everybody,

I have a button on the main form to delete records on a subform. I use this code:

Private Sub BtnDeleteContact_Click()
Dim Respons As String
Dim Msg, Style, Title, Response As String
Msg = "You are about to delete this contact."
Style = vbOKCancel + vbQuestion + vbDefaultButton2
Title = "Continue?"
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then
Me!subfrmCustomerContacts.Form.AllowDeletions = True
Me.subfrmCustomerContacts.SetFocus

Me.subfrmCustomerContacts.Form.Recordset.Delete
Me.subfrmCustomerContacts.Form.Recordset.MoveNext
Else
MsgBox "No record deleted", vbOKOnly, "No changes made"
End If
Me!subfrmCustomerContacts.Form.AllowDeletions = False
End Sub

Now this works fine for all records EXCEPT the last one on the subform. Can somebody explain to me why and if it is possible to also delete the last record.

Appreciate your replies.
Cheers.

 

[wazz]

i'm not sure about the final record issue you're having but i did get something similar to work:

    Dim rs As DAO.Recordset
    Set rs = Me.sfrmName.Form.Recordset
    
    With rs
        .MoveFirst
        Do While Not .EOF
            .Delete
            .MoveNext
        Loop
    End With
    
    Me.sfrmName.Requery
    Set rs = Nothing

one difference is the requery (and .movefirst). i haven't tried an approach quite like this before so can't really say much about it. maybe someone else will have some ideas. anyway, see if that works for you.

 

[tokoloshi]

I kinda like thinking that if I can create a query that can see exactly which records I need to delete first, then change that query to a Delete Query, run it with a docmd.OpenQuery "QueryName", followed by a Form.Requery that it is a lot cleaner.

It is faster and it does not cause as much locking of the tables as the .movenext method does.

I would suggest creating a query where the delete from criteria points at your [forms]!.... [fieldvalue] that says that the specific records that I have on this specific form that meet these specific criteria can now be deleted.

Remember that you can use the BUILDER (...) option in the query designer to point at the appropriate PK/FK fields on the relevant forms. If you can then run the query and get back exactly what you want, you are guaranteed to delete the exact records as well.