Error with Forward & Backward Buttons on Access Form

[JHNielson]

I have a form where I put my own Back and Forward buttons on the form.
I used the codes:

    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious

But when I get to the last record or the first record, I get an error.
Is there a way to detect if it is a t the last record? Or some other way to fix this.

I tired using the on the on error.... step but it messes with my other code for the button.

[MMcCarthy]

Try putting your own error message in ...

Private Sub cmdNextRecord_Click()
On Error GoTo Err_cmdNextRecord_Click
 
    DoCmd.GoToRecord , , acNext ' move to next record
 
Exit_cmdNextRecord_Click:
Exit Sub
Err_cmdNextRecord_Click:
        MsgBox "This is the last record", , "Last Record"
        Resume Exit_cmdNextRecord_Click
End Sub
 
Private Sub cmdPreviousRecord_Click()
On Error GoTo Err_cmdPreviousRecord_Click
 
    DoCmd.GoToRecord , , acPrevious ' move to previous record
 
Exit_cmdPreviousRecord_Click:
Exit Sub
Err_cmdPreviousRecord_Click:
        MsgBox "There is no record prior to this", , "First Record"
        Resume Exit_cmdPreviousRecord_Click
End Sub
 

Mary

[missinglinq]

Another approach I sometimes use is to "wrap" back around. If you're on the Last Record and hit Next it wraps around to the First Record. If you're on the First Record and hit Previous it wraps back around to the Last Record.

Private Sub cmdNextRecord_Click()
On Error GoTo Err_cmdNextRecord_Click
   DoCmd.GoToRecord , , acNext
Exit_cmdNextRecord_Click:
    Exit Sub
 
Err_cmdNextRecord_Click:
    DoCmd.GoToRecord , , acFirst
End Sub
 
 
Private Sub cmdPreviousRecord_Click()
On Error GoTo Err_cmdPreviousRecord_Click
    DoCmd.GoToRecord , , acPrevious
Exit_cmdPreviousRecord_Click:
    Exit Sub
 
Err_cmdPreviousRecord_Click:
    DoCmd.GoToRecord , , acLast    
End Sub

[ADezii]

I have a form where I put my own Back and Forward buttons on the form.
I used the codes:

    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious

But when I get to the last record or the first record, I get an error.
Is there a way to detect if it is a t the last record? Or some other way to fix this.

I tired using the on the on error.... step but it messes with my other code for the button.

The professional way to accomplish what you are requesting is by the creation of "Smart Navigation" Buttons which change their "Enabled State" depending on the Current Record Pointer. This system involves the creation of 5 Command Buttons, code in the Current Event() of the Form, and the RecordsetClone property to test potential moves. The User will never see an Error because of an inadvertant move, because it will never happen. A little bit of effort, and the close following of these instructions, will give your Form a truly professional look:

1. Create a Command Button whose Caption = First, and whose Name is cmdFirst. Place this code in the Click() Event.
   

   Private Sub cmdFirst_Click()
     DoCmd.GoToRecord , , acFirst
   End Sub

2. Create a Command Button whose Caption = Previous, and whose Name is cmdPrevious. Place this code in the Click() Event.
   

   Private Sub cmdPrevious_Click()
     DoCmd.GoToRecord , , acPrevious
   End Sub

3. Create a Command Button whose Caption = Next, and whose Name is cmdNext. Place this code in the Click() Event.
   

   Private Sub cmdNext_Click()
     DoCmd.GoToRecord , , acNext
   End Sub

4. Create a Command Button whose Caption = Last, and whose Name is cmdLast. Place this code in the Click() Event.
   

   Private Sub cmdLast_Click()
     DoCmd.GoToRecord , , acLast
   End Sub

5. Create a Command Button whose Caption = Add Record, and whose Name is cmdAddNew. Place this code in the Click() Event.
   

   Private Sub cmdAddNew_Click()
     DoCmd.GoToRecord , , acNewRec
   End Sub

6. Place this code in the Current() Event of the Form, it is well documented so that you can get an idea as to what is goiong on. I even added code to modify the Form's Caption depending on which Record is current. This was basically to show how the AbsolutePosition and RecordCount properties of a Recordset work. If you have any questions at all, please feel free to ask. I realize that this may be a little confusing and I'm not really sure of your skill level.
   

   Private Sub Form_Current()
   Dim recClone As Recordset, intNewRecord As Integer, Msg As String
    
   'Make a duplicate copy of the Form's Recordset Object using RecordsetClone().
   'It will create a seperate copy of the Recordset so that you can navigate or
   'Manipulate a Form's Records independently of the Form itself. Maneuvers will
   'not be reflected in the Form's Recordset.
   Set recClone = Me.RecordsetClone()
      'necessary for initial Record Count for Form Caption
       recClone.MoveLast
       recClone.MoveFirst
    
   'If this is a New Record then disable the <Next> and <New> Buttons and enable
   'the others. Must Exit the Sub-Routine at this point!!!!!!!!!!
   'intNewRecord = IsNull(Me![Name])      'Name is a required Field   OR
   intNewRecord = Me.NewRecord
    
   If intNewRecord Then                'Zero Records - disable all Buttons
     cmdFirst.Enabled = True
     cmdNext.Enabled = False
     cmdPrevious.Enabled = True
     cmdFirst.Enabled = True
     cmdAddNew.Enabled = False        'Not using in this Form
       Exit Sub
   End If
    
   'If we reach this Point, we are not at a New record, so enable the <New Record> Button
   cmdAddNew.Enabled = True
    
   'If there are no Records, disable all Buttons except <New Record>
   If recClone.RecordCount = 0 Then
     cmdFirst.Enabled = False
     cmdNext.Enabled = False
     cmdPrevious.Enabled = False
     cmdFirst.Enabled = False
   Else    'Must synchronize the Current Pointer in both Recordsets. Obviously Records exist
     recClone.Bookmark = Me.Bookmark   'Resync after each Record Navigation
     Msg = "Injuries for Years 1993 to 2000   (Record " & Str$(recClone.AbsolutePosition + 1) & " of "
     Msg = Msg & recClone.RecordCount & ")"
       Me.Caption = Msg
    
     'There are Records, let's see if we're on the First Record. If we are, disable the
     '<First> and <Previous> Buttons
     recClone.MovePrevious
     cmdFirst.Enabled = Not (recClone.BOF)
     cmdPrevious.Enabled = Not (recClone.BOF)
     recClone.MoveNext
    
     'See if we're on the <Last Record> and if we are, disable the <Last>
     'and <Next> Buttons
     recClone.MoveNext
     cmdLast.Enabled = Not (recClone.EOF)
     cmdNext.Enabled = Not (recClone.EOF)
     recClone.MovePrevious
   End If
    
   'Don't forget to Close the Recordset
   recClone.Close
   End Sub

[clickingwires]

The only way that I could get this to work was by changing


Dim recClone As Recordset

to

Dim recClone as DAO.Recordset

[ADezii]

The only way that I could get this to work was by changing


Dim recClone As Recordset

to

Dim recClone as DAO.Recordset

That is because you probably have References to both the DAO and ADO Type Libraries. Thanks for bring this to light, since you should always make Explicit References to the Libraries as in:

Dim MyRS As DAO.Recordset
Dim MyRS As ADODB.Recordset