By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 1,898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

Error with Forward & Backward Buttons on Access Form

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

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.GoToRecord , , acNext
  2.     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.
May 7 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try putting your own error message in ...
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdNextRecord_Click()
  2. On Error GoTo Err_cmdNextRecord_Click
  3.  
  4.     DoCmd.GoToRecord , , acNext ' move to next record
  5.  
  6. Exit_cmdNextRecord_Click:
  7. Exit Sub
  8. Err_cmdNextRecord_Click:
  9.         MsgBox "This is the last record", , "Last Record"
  10.         Resume Exit_cmdNextRecord_Click
  11. End Sub
  12.  
  13. Private Sub cmdPreviousRecord_Click()
  14. On Error GoTo Err_cmdPreviousRecord_Click
  15.  
  16.     DoCmd.GoToRecord , , acPrevious ' move to previous record
  17.  
  18. Exit_cmdPreviousRecord_Click:
  19. Exit Sub
  20. Err_cmdPreviousRecord_Click:
  21.         MsgBox "There is no record prior to this", , "First Record"
  22.         Resume Exit_cmdPreviousRecord_Click
  23. End Sub
  24.  
Mary
May 7 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdNextRecord_Click()
  2. On Error GoTo Err_cmdNextRecord_Click
  3.    DoCmd.GoToRecord , , acNext
  4. Exit_cmdNextRecord_Click:
  5.     Exit Sub
  6.  
  7. Err_cmdNextRecord_Click:
  8.     DoCmd.GoToRecord , , acFirst
  9. End Sub
  10.  
  11.  
  12. Private Sub cmdPreviousRecord_Click()
  13. On Error GoTo Err_cmdPreviousRecord_Click
  14.     DoCmd.GoToRecord , , acPrevious
  15. Exit_cmdPreviousRecord_Click:
  16.     Exit Sub
  17.  
  18. Err_cmdPreviousRecord_Click:
  19.     DoCmd.GoToRecord , , acLast    
  20. End Sub
May 7 '07 #3

ADezii
Expert 5K+
P: 8,616
I have a form where I put my own Back and Forward buttons on the form.
I used the codes:

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.GoToRecord , , acNext
  2.     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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdFirst_Click()
    2.   DoCmd.GoToRecord , , acFirst
    3. End Sub
  2. Create a Command Button whose Caption = Previous, and whose Name is cmdPrevious. Place this code in the Click() Event.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdPrevious_Click()
    2.   DoCmd.GoToRecord , , acPrevious
    3. End Sub
  3. Create a Command Button whose Caption = Next, and whose Name is cmdNext. Place this code in the Click() Event.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdNext_Click()
    2.   DoCmd.GoToRecord , , acNext
    3. End Sub
  4. Create a Command Button whose Caption = Last, and whose Name is cmdLast. Place this code in the Click() Event.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdLast_Click()
    2.   DoCmd.GoToRecord , , acLast
    3. End Sub
  5. Create a Command Button whose Caption = Add Record, and whose Name is cmdAddNew. Place this code in the Click() Event.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdAddNew_Click()
    2.   DoCmd.GoToRecord , , acNewRec
    3. 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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2. Dim recClone As Recordset, intNewRecord As Integer, Msg As String
    3.  
    4. 'Make a duplicate copy of the Form's Recordset Object using RecordsetClone().
    5. 'It will create a seperate copy of the Recordset so that you can navigate or
    6. 'Manipulate a Form's Records independently of the Form itself. Maneuvers will
    7. 'not be reflected in the Form's Recordset.
    8. Set recClone = Me.RecordsetClone()
    9.    'necessary for initial Record Count for Form Caption
    10.     recClone.MoveLast
    11.     recClone.MoveFirst
    12.  
    13. 'If this is a New Record then disable the <Next> and <New> Buttons and enable
    14. 'the others. Must Exit the Sub-Routine at this point!!!!!!!!!!
    15. 'intNewRecord = IsNull(Me![Name])      'Name is a required Field   OR
    16. intNewRecord = Me.NewRecord
    17.  
    18. If intNewRecord Then                'Zero Records - disable all Buttons
    19.   cmdFirst.Enabled = True
    20.   cmdNext.Enabled = False
    21.   cmdPrevious.Enabled = True
    22.   cmdFirst.Enabled = True
    23.   cmdAddNew.Enabled = False        'Not using in this Form
    24.     Exit Sub
    25. End If
    26.  
    27. 'If we reach this Point, we are not at a New record, so enable the <New Record> Button
    28. cmdAddNew.Enabled = True
    29.  
    30. 'If there are no Records, disable all Buttons except <New Record>
    31. If recClone.RecordCount = 0 Then
    32.   cmdFirst.Enabled = False
    33.   cmdNext.Enabled = False
    34.   cmdPrevious.Enabled = False
    35.   cmdFirst.Enabled = False
    36. Else    'Must synchronize the Current Pointer in both Recordsets. Obviously Records exist
    37.   recClone.Bookmark = Me.Bookmark   'Resync after each Record Navigation
    38.   Msg = "Injuries for Years 1993 to 2000   (Record " & Str$(recClone.AbsolutePosition + 1) & " of "
    39.   Msg = Msg & recClone.RecordCount & ")"
    40.     Me.Caption = Msg
    41.  
    42.   'There are Records, let's see if we're on the First Record. If we are, disable the
    43.   '<First> and <Previous> Buttons
    44.   recClone.MovePrevious
    45.   cmdFirst.Enabled = Not (recClone.BOF)
    46.   cmdPrevious.Enabled = Not (recClone.BOF)
    47.   recClone.MoveNext
    48.  
    49.   'See if we're on the <Last Record> and if we are, disable the <Last>
    50.   'and <Next> Buttons
    51.   recClone.MoveNext
    52.   cmdLast.Enabled = Not (recClone.EOF)
    53.   cmdNext.Enabled = Not (recClone.EOF)
    54.   recClone.MovePrevious
    55. End If
    56.  
    57. 'Don't forget to Close the Recordset
    58. recClone.Close
    59. End Sub
May 8 '07 #4

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


Dim recClone As Recordset

to

Dim recClone as DAO.Recordset
Nov 9 '07 #5

ADezii
Expert 5K+
P: 8,616
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyRS As DAO.Recordset
  2. Dim MyRS As ADODB.Recordset
Nov 9 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.