Custom Navigation 5

[tnago]

Hi

Is it possible to move forward or backward more than one record at a time. If there are large number of records it is less time consuming if user can move say 5 or 10 records forward or backward. Access built it navigation just allow sigle record movement. Any suggestion?

 

[Genimuse]

Sure thing. Create a button and put the following code in it:

    On Error Resume Next

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

You can use

acPrevious

for moving backwards.

You could put in in a loop if you prefer, like this:

    On Error Resume Next

    Dim i As Integer

    For i = 1 to 5
        DoCmd.GoToRecord , , acNext
    Next

It's important to include the

On Error Resume Next

because it's much easier than checking to see if there are 5 records left to move to.

 

[tnago]

Hi Genimuse

Thanks for your help in this regard and two stars for your effort and time. One more thing on the this subject, is it possible to stop on the last record and give the warning message that there are no further records rather than going to last empty record?

 

[humbleprogrammer]

Hi try this:

Private Sub yourbuttonname_Click()
On Error GoTo Err_LastRecord_Click

Dim i As Integer

For i = 1 To 5
DoCmd.GoToRecord , , acNext
Next

Exit_yourbuttonname_Click:
Exit Sub

Err_LastRecord_Click:
MsgBox "This is the last record", , "No More Records"
DoCmd.GoToRecord , , acPrevious
Resume Exit_yourbuttonname_Click

End Sub

 

[Genimuse]

Good idea, HP, catching it on the error!

(Wait, will it work if the 5th one is the first new record page? I don't think that will throw the error, will it?)

 

[Genimuse]

Hey, here's a "clever" way to do it:

    On Error Resume Next

    Dim i As Integer

    For i = 1 to 6
        DoCmd.GoToRecord , , acNext
    Next

    DoCmd.GoToRecord , , acPrevious

Note that the loop goes one more than necessary, then backs up one.

Ok, it's a little goofy, but it would work.

 

[tnago]

Yes it works but now where I put my error message that user is at the end of database and there are no more records?

 

[Genimuse]

Sorry, I forgot about that. Did you try humbleprogrammer's method?

 

[humbleprogrammer]

Hi,

My method should work. I tested it on a form against a table that had only 3 records. When going past the 3rd (last record) it gives you a message and brings you to the last record.

 

[humbleprogrammer]

Here's another way of doing it if you don't want to use the OnError to display a message. This is combining Genimuse's idea with a select query that looks for the last record. In order for this to work you need to add a field in your form that has the tables unique id.

On Error Resume Next

Dim i As Integer
Dim strLastRecord As String
Dim MaxRecord
Dim rs

For i = 1 To 6
DoCmd.GoToRecord , , acNext
Next

DoCmd.GoToRecord , , acPrevious

strLastRecord = "SELECT Max(Table1.UniqueID) AS MaxOfID FROM Table1"
Set rs = CurrentDb.OpenRecordset(strLastRecord)
MaxRecord = rs("MaxOfID&quot
If Me.txtID = MaxRecord() Then
MsgBox "This is the last record", , "No More Records"
End If

 

[tnago]

Hi

HP & Genimuse

I tried the last suggestion by HP but I am getting error on every click (after every 5 records). Maybe I made an error in the code or something is missing. I have the code as follow:

Private Sub Command10_Click()
On Error Resume Next

    Dim i As Integer
    Dim strLastRecord As String
    Dim MaxRecord
    Dim rs

    For i = 1 To 6
        DoCmd.GoToRecord , , acNext
    Next
    
    DoCmd.GoToRecord , , acPrevious
    
    strLastRecord = "SELECT Max(INCIDENTS.IR) AS MaxOfID FROM INCIDENTS"
    Set rs = CurrentDb.OpenRecordset(strLastRecord)
    MaxRecord = rs("MaxOfID")
    If Me.IR = MaxRecord() Then
            MsgBox "This is the last record", , "No More Records"
    End If
    
End Sub

Where INCIDENTS is the table name and IR is the unique id. Any suggestion.

Thanks

 

[humbleprogrammer]

Well, I obviously didn't test that one out very well. Here is another MUCH simpler solution:

Private Sub Command10_Click()
On Error Resume Next

Dim i As Integer

For i = 1 To 5
DoCmd.GoToRecord , , acNext
Next

If Me.NewRecord Then
DoCmd.GoToRecord , , acPrevious
MsgBox "This is the last record", , "No More Records"
End If

End Sub

The If statement looks to see if a new record (after last record) is being started. If so, it moves to the previous (Last) record and displays the message. I think this will accomplish what you need.

 

[tnago]

Hi

HP & Genimuse

Thank you very much for your help in this regard. It is working as desired. Please accept stars for your effort and time.

 

[gol4]

Another option is to ask them how many they want to move

Private Sub Command34_Click()
Dim innumtomove As Integer
innumtomove = InputBox("Number to move&quot
innumtomove = Me.CurrentRecord + innumtomove
If innumtomove > Me.RecordsetClone.RecordCount Then innumtomove = Me.RecordsetClone.RecordCount
DoCmd.GoToRecord , , acGoTo, innumtomove
End Sub

 

[tnago]

Hi Gol4

An interesting way to do things. Though I have two questions. One it is only moving foward what about if a user wants to go backward. Second how you tell user that either they are at the first or the last record and further ckicking will do no good.

Once again thanks for your suggestion.

 

[gol4]

two buttons coded like this

'forward button code
Private Sub CbForward_Click()
Dim innumtomove As Integer
innumtomove = InputBox("Number to move&quot
innumtomove = Me.CurrentRecord + innumtomove
If innumtomove > Me.RecordsetClone.RecordCount Then
innumtomove = Me.RecordsetClone.RecordCount
msgbox "you are at last record"
end if
DoCmd.GoToRecord , , acGoTo, innumtomove
End Sub

'back button code
Private Sub CbBack_Click()
Dim innumtomove As Integer
innumtomove = InputBox("Number to move&quot
innumtomove = Me.CurrentRecord - innumtomove
If innumtomove <=1 Then
innumtomove = 1
msgbox &quot;you are at first record&quot;
end if
DoCmd.GoToRecord , , acGoTo, innumtomove
End Sub

You dont have to feed the button using an input box you could use a constant as well

 

[BillPower]

Another Method,

This will go forward 6 records at a time:

On Error Resume Next
DoCmd.GoToRecord , , acNext, 6

This will go back 6 records at a time:

On Error Resume Next
DoCmd.GoToRecord , , acPrevious, 6

Good Luck

Bill

 

[tnago]

Hi Gol4

Thanks for your suggestion. I think it is very good in the sense that it allows the user to go forward and backward as many records they want rather the developer limiting them to any set number. Though I have one issue the way the code is running now. I think the code should move to the 1st or last record first before giving the warning. Let say that user is at record 25 and they want to go back 25 or 26 records, the code give warning and once the user clik ok on the warning then it moves to the 1st record. This can be a bit confusing to the user if they are expecting more records.

 

[tnago]

Hi Bill

Thanks for your suggestion. However, without checking for the first or last record it will get stuck before reaching either the beginning or end depending on the number of records in the database.

 

[BillPower]

You could put in message boxes to warn the user they are already at first or last record, up to you:

On Error GoTo Err_LastRecord
DoCmd.GoToRecord , , acNext, 6
Exit Sub
Err_LastRecord:
DoCmd.GoToRecord , , acLast

On Error GoTo Err_FirstRecord
DoCmd.GoToRecord , , acPrevious, 6
Exit Sub
Err_FirstRecord:
DoCmd.GoToRecord , , acFirst

Bill