how to avoid getting the "You can't go to the specified record" error when deleting the first record.

When the recordset is first opened, it will be at the beginning so going to a previous record will be an error. If it is not at the END (r.eof), then there are records and you can perhaps move to another one. What are you trying to delete? and what is the purpose of this?

Also, after you delete something using a form, the tabledefs in the currentdb need to be refreshed to see it.

Thanks for your response.  Originally I had the short code below, but if I was on the first record I would get an error with the "you can't...".  This only happens on the first record when that record is being deleted.  My thought was that instead of acPrevious, acNext wold avoid the error, but I can't figure out how to get and If..then..else to work.  Somehow the IF statement needs to know if the deleted record is the first record.

I have a form OnDelete event that takes care of what you mentioned.

Private Sub cmdDeleteRecord_Click()
On Error GoTo Err_cmdDeleteRecord_Click
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings True
Exit_cmdDeleteRecord_Click:
    Exit Sub
Err_cmdDeleteRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteRecord_Click
End Sub

Select allOpen in new window

Try using the Form's recordset:

If Me.Recordset.BOF Then
  <etc etc>
End If

That won't work because Me.Recordset.BOF is always false for some reason when I am on the first record.

The question is how to I get that to be true.

BOF returns True only when you are BEFORE the First Record. It returns False if you are ON or AFTER the first record.

If you want to go to the very first record in a recordset:

rst.MoveFirst

You cannot do that, however if both BOF and EOF are true, so:

If Not (rst.BOF and rst.EOF) Then
  rst.MoveFirst
End If

If you want to move the FORM, then use the Form's Recordset

https://msdn.microsoft.com/en-us/library/ms675787(v=vs.85).aspx

My goal is not to move to the first record, by goal is to end the pop up error that results when I delete the first record and then use acPrevious.  The popup error only shows when the first record is deleted and then acPrevious bumps up against the BOF.

I need to either block the error popup, or have some way of determining if I am on record 1.

Set Warnings is already set to False.

The primary key is an autonumber so it won't likely be 1 even on the first record.

Thanks, but I don't think I need that much code.  I only need to deal with the situation where I am at record 1, because everything else is acPrevious.

  Is there a way to have something like:

If Me.recordset.MovePrevious (or DoCmd.GoToRecord , , acPrevious) = BOF or error then
me.recordset.MoveNext (or DoCmd.GoToRecord , , acNext)
else
me.recordset.movePRevious (or DoCmd.GoToRecord , , acPrevious)
endif

I agree with Scott. Testing to see if you have a record before you try to delete it, each time, is important.

> "everything else is acPrevious"

the original posted code is opening the table, not a recordset of the records that are on the form ... and the recordset in the initial posted code is not on the record in the form; it is at the beginning, and is probably in a different order. The only thing they have in common is that the data comes from the same place. Scott also suggested using the Form's recordset.

Are you possibly trying to delete all the records that might be there? Just the first 2? It would help if you would explain more about the reason why.

I have a delete button on the form that deletes the current record.  That is what I can do with the code that is shown above.  It all works great with the exception of deleting the first record.  The record that the user would want to delete could be any record, but the only one that will generate the error, "You can't go to the specified record" , is when you try to delete the first record, and the acPrevious bumps up against the BOF, a zero record or something that generates the error.  If I don't have to do a recordset routine, great.  I just need to stop the popup error.

This is the code that I would like to minimally modify to get rid of the error popup.

Private Sub cmdDeleteRecord_Click()
On Error GoTo Err_cmdDeleteRecord_Click
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings True
Exit_cmdDeleteRecord_Click:
    Exit Sub
Err_cmdDeleteRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteRecord_Click
End Sub

Select allOpen in new window

OMG,
All I had to do was comment out the MsgBox line.  Now no popup.

Private Sub cmdDeleteRecord_Click()
On Error GoTo Err_cmdDeleteRecord_Click
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings True
Exit_cmdDeleteRecord_Click:
    Exit Sub
Err_cmdDeleteRecord_Click:
    'MsgBox Err.Description
    Resume Exit_cmdDeleteRecord_Click
End Sub

Select allOpen in new window

I hate blocking all error messages.  Is there a way to block just this one?

Or avoid the message by using acNext vs. acPrevious with some if-then-else statement.

Great, thanks.
This is what I am using.  This will catch the 2105 error but let any others get reported.

Private Sub cmdDeleteRecord_Click()
On Error GoTo Err_cmdDeleteRecord_Click
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.GoToRecord , , acPrevious
    Me.Refresh
    DoCmd.SetWarnings True
Exit_cmdDeleteRecord_Click:
    Exit Sub
Err_cmdDeleteRecord_Click:
If Err.Number = 2105 Then
    DoCmd.GoToRecord , , acFirst
Else
    MsgBox Err.Description & " " & Err.Number
End If
    Resume Exit_cmdDeleteRecord_Click
End Sub

Select allOpen in new window

you're welcome

while your handler catches 2105, it then exits ... is this what you want to happen? btw, it would be a good idea to indent the If ... End If block between Err_cmdDeleteRecord_Click: and Resume Exit_cmdDeleteRecord_Click, for better readability.

Agree with Crystal - this is really just a problem waiting to happen.

Thanks, but I don't think I need that much code.

My intent was to show you how to manage a variety of situations, since it was kinda hard to determine exactly what you were after. Swallowing errors, like you're now doing, is bad business unless you are 100% certain that you want to do this (and I cannot imagine any code involving removal of data to be in that category). It's your code, and your app, but in cases like this it's always best to err on the side of caution and not simply ignore errors.

Thank you both.  I will fix the indentation visibility.  This has been functional for over 6 years with no data problems, just the irritating popup window in the rare case where the first record was deleted.  There is a warning window that confirms the deletion that is in the Forms OnDelete Event, so the user does get a warning.