Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GoNext GoPrevious with a twist (A2K)

    By using the ongoing help provided to other forum users, I've been able to take care of the problem inherent in going past the first or last record. That is, when using controls. The situation that I have now is based on the fact that the user wants to use the keyboard as opposed to the mouse to do his moving about. So I created the following KeyDown-type code which works very well with respect to adding new records, moving back and forth and exiting the form:

    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

    If KeyCode = vbKeyF5 Then
    On Error GoTo Err_AddNewRecord_Click
    DoCmd.GoToRecord , , acNewRec
    KeyCode = 0
    Exit_AddNewRecord_Click:
    Exit Sub
    Err_AddNewRecord_Click:
    MsgBox Err.Description
    Resume Exit_AddNewRecord_Click
    End If

    If KeyCode = vbKeyF9 Then
    DoCmd.GoToRecord , , acPrevious
    KeyCode = 0
    End If

    If KeyCode = vbKeyF10 Then
    DoCmd.GoToRecord , , acNext
    KeyCode = 0
    End If

    If KeyCode = vbKeyF12 Then
    DoCmd.Close acForm, "frm Master - Login" ' Use Form Name
    Dim stLinkCriteria As String
    DoCmd.OpenForm "frm Menu - Main", , , stLinkCriteria
    KeyCode = 0
    End If
    End Sub

    The problem that I have is:
    When they F9 themselves to the 1st record, they get the old "You can't go to the specified record" message

    When they F10 themselves to the last record, they get the old "You can't go to the specified record" message and also into a blank new record.

    I realize that I can solve this by turning "Allow Additions" to "Off", but that then negates adding new records which in the words of my favourite cell dwelling person, "That is not a good thing".

    Thanks in advance for any help or suggestions.
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: GoNext GoPrevious with a twist (A2K)

    Even in a keydown routine, you still need to test for the EOF and BOF first or else trap the error and handle it.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoNext GoPrevious with a twist (A2K)

    Charlotte,

    Thanks as always for your succinct observations as to what is needed. If you happen to see some code somewhere that might be appropriate and is demonstrably used with my current code, I'd be appreciative of being pointed in that direction.
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: GoNext GoPrevious with a twist (A2K)

    By far the easiest solution is to put a line

    On Error Resume Next

    before the code that handles the go to next/previous record.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoNext GoPrevious with a twist (A2K)

    Hi Hans-To-The-Rescue,

    I followed your suggestion and inserted the code as follows:


    If KeyCode = vbKeyF9 Then

    On Error Resume Next

    DoCmd.GoToRecord , , acPrevious
    KeyCode = 0
    End If

    If KeyCode = vbKeyF10 Then

    On Error Resume Next

    DoCmd.GoToRecord , , acNext
    KeyCode = 0
    End If

    The good news is that it does stop from going beyond the first record, however at the last record, it goes to a blank new record.
    Cheers,
    Andy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: GoNext GoPrevious with a twist (A2K)

    Try this:

    If Me.NewRecord = False Then
    DoCmd.GoToRecord , , acNext
    End If

    BTW, you don't need to repeat On Error Resume Next. It remains in force until the next On Error statement, or until the end of the procedure if there is no next On Error statement.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoNext GoPrevious with a twist (A2K)

    Hans,

    Stopping from going past the first record worked perfectly.
    Stopping from going to new record did not.
    So,
    using your suggested code as a starting point, I did the following which worked:

    If KeyCode = vbKeyF10 Then
    DoCmd.GoToRecord , , acNext
    KeyCode = 0
    End If
    If Me.NewRecord = True Then
    DoCmd.GoToRecord , , acPrevious
    End If

    Cheaters prosper! Thanks again,
    Cheers,
    Andy

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: GoNext GoPrevious with a twist (A2K)

    I don't understand. In the first post in this thread, you mentioned that you didn't want to set AllowAdditions to False, so I thought you wanted the user to be able to add a new record. The code you mention does effectively the same as setting AllowAdditions to False. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoNext GoPrevious with a twist (A2K)

    Hans,

    Very observant and you are absolutely right in your comment. I was aware that turning off AllowAdditions would have the same effect, but I was curious as to what the exact code would be that performed the same function, and as usual, you were able to provide said code. Some people do crosswords, I like to learn and apply new code. Thanks again for your help,
    Cheers,
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •