Here is a technique I use to delete the current record and determine whether
to show the next or previous record. It does not close the form if there is
neither a previous or current record. The DelCurrentRec function I have in a
standard modlue so it can be called from any form.
Private Sub cmdDelete_Click()
Dim strMActivity As String
On Error GoTo cmdDelete_Click_Error
On Error GoTo Err_cmdDelete_Click
'Check for data to see if Activity can be deleted
DoCmd.Hourglass True
If IsNull(DLookup("[Activity]", "Actual_Res_Export", "[Activity] = '" & _
Me.cboActivity & "'")) And _
IsNull(DLookup("[Activity #]", "Plan_Res_Export", "[Activity #]
= '" & _
Me.cboActivity & "'")) Then
strMActivity = Nz(Me.txtMactivity, "")
Call DelCurrentRec(Me)
Me.cboActivity = Null
Else
DoCmd.Hourglass False
MsgBox "Activity " & Me.cboActivity & " Has Data", vbExclamation +
vbOKOnly, _
"Cannot Delete"
End If
Exit_cmdDelete_Click:
DoCmd.Hourglass False
Exit Sub
Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click
cmdDelete_Click_Exit:
On Error Resume Next
Exit Sub
cmdDelete_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cmdDelete_Click of VBA Document
Form_frmAttributetable"
GoTo cmdDelete_Click_Exit
End Sub
---------------------------------------
I have added comments to this function to explain how it works
Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset
On Error GoTo DelCurrentRec_Error
'Turn of screen updating so user wont get dizzy
Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
'Set the recordsetclone's bookmark to match the form
'This is the record that will be deleted
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then 'It is not the first record
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete 'Deletes the record using the Recordsetclone
'Recheck position, it will have changed
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
'Turn the screen back on to display to the user the wonderful results
Application.Echo True
DelCurrentRec_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function
DelCurrentRec_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit
End Function
Alex said:
I have a button on my form that will delete a record. I then need it to go
to the next record, if there is one or the previous record if there is one.
If not a previous or next record, close the form. The following code has
been unsuccessful. Any help is appreciated.
Private Sub Command121_Click()
Dim strSQL As String
strSQL = "DELETE * FROM AllNewParts WHERE [Model#] = '" &
[Forms]![NewPartInputfrm]![Model] & "' And [Part#] = '" &
[Forms]![NewPartInputfrm]![Part] & "' And [NHL] = '" &
[Forms]![NewPartInputfrm]![NHL] & "'"
CurrentDb.Execute (strSQL), dbFailOnError
On Error goto tryPrevious
DoCmd.GoToRecord , , acnext
tryprevious:
On Error goto closeform
DoCmd.GoToRecord , , acprevious
Closeform:
DoCmd.Close
End Sub
