Delete and go to previous record

[Alex]

When a record is deleted using the below code that resided in my delete
button, the form is not refreshed and the deleted record still shows. I need
the form to close if the only record was the one that was deleted (this is
working with the code below). However, if there are more records in the form
then the one that was just deleted, how can I go to the previous record?
Thank you.

Dim strSQL As String

If Me.New_Part_NHL <> "" And Me.New_Part_ <> "" Then

strSQL = "DELETE * FROM AllNewParts WHERE [Model#] = '" &
[Forms]![NewPartInputfrm]![Model] & "' And [Part#] = '" &
[Forms]![NewPartInputfrm]![Part] & "' And [NHL] = '" &
[Forms]![NewPartInputfrm]![NHL] & "'"
CurrentDb.Execute (strSQL), dbFailOnError

Else
Me.Undo
End If

With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else


End If
End With

 

[Michel Walsh]

Hi,


Me.Requery should do the job. Your code acts like another user (since there
is one user at the keyboard that can change things differently than the
running code can, this is nice to consider your code as a user). Since
another user added(or delete a new record), me.Requery will force the form
to refresh the record-collection (recordset) to reflect the latest data.


Hoping it may help,
Vanderghast, Access MVP