Hello, I have a bound form(autonumber), form has navagation buttons to scroll through records. User's want to be able to delete records,

From wizard
Private Sub Command151_Click()
On Error GoTo Err_Command151_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command151_Click:
Exit Sub

Err_Command151_Click:
MsgBox Err.Description
Resume Exit_Command151_Click

End Sub

This works except I need to alter it to this

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
If Cancel = True Then GoTo aaa

Me.Status.Value = "Available" 'Puting equipment back in to stock
Me.StorageLocation.Value = Me.PhoStorage.Value


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70


Exit_cmdDelete_Click:

aaa:
Me.StorageLocation.Value = "OUT" 'taking equipnent back out of stock
Me.Status.Value = "Committed"
Me.Refresh
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub
but when I do, if I delete the last record then I get an error, access tries to move to next record but there is no next record. Not sure why when I add my code access is moving forword after deleting. Any guidence is welcome

To be honest I'm not at all sure of what you're trying to do here!

If Cancel = True Then GoTo aaa

To begin with, there is no Cancel in a click event! If there were you'd see it in the sub header that Access creates! It would be


Private Sub cmdDelete_Click(Cancel as Integer)

and, of course, it isn't! You can't simply place it there! Next

Me.Status.Value = "Available" 'Puting equipment back in to stock
Me.StorageLocation.Value = Me.PhoStorage.Value

you're assigning values to two fields in the current record then

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

you immediately deleting the current record! This makes no sense, either!

You say you have navigation buttons, but then you talk as if Access is trying to move to the next record after your code deletes a record, which it does not do, on its own.

If you have AllowAdditons set to No, are on the last record then try to move to the next record, you will, indeed, get this error. IF the problem is being caused by a "Next Record" button, you need to check to see if you're on the last record and only execute the code if this is not true.

Perhaps you need to give us some explanation of what you're actually trying to do, in order for us to be able to help you.

Hello missingling, you answered here as well. I had three ways of deleting I had written but had questions on all of them and did not want to burden any one form with all three questions. The above was my first attempt at using and altering a delete statement(therefore at least in my case, bumbling). It's true when you use the wizard you do not advance when you delete so you can delete the last record, but when I added my bad code it caused access to advance on to the next record after deleting except for the advancing it all worked fine.

The Cancel had to do with canceling the deletion

Me.Status.Value = "Available" 'Puting equipment back in to stock
Me.StorageLocation.Value = Me.PhoStorage.Value

This was me updating the equipment table, with the values in these controls, putting things back into stock that had been taken out of stock before the deletion, since the deletion would remove where things needed to go and then setting that particular piece of equipment = Available.
The other two methods using a SQL delete statement(which you gave much appreciated advice on) , ADO delete
both work well know and offer greater flexibility in there use. In the last few days due to the gracious input from you, aceman, php, Mr.B have clearer understanding of deleting, requery, if then, error handling.

Here's what works


Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

If MsgBox("Are you sure you want to continue with deletion?", _
vbQuestion + vbOKCancel, _
"User Confirmation Required! . . .") = vbCancel Then Exit Sub


Me.Status.Value = "Available" 'Puting equipment back in to stock
Me.StorageLocation.Value = Me.PhoStorage.Value


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70


Exit_cmdDelete_Click:
Me.Refresh
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub

Thanks again for your input