Is the next record the last record? (1 Viewer)

DanielR · Mar 20, 2018

I am using:
DoCmd.GoToRecord , , acNext
To scroll through my records.
My problem is that when I click next on the last record it opens a new record which I don't want.
I want to display a message saying "You have reached the last record".
How do I determine if I have reached the last record?

isladogs · Mar 20, 2018

Use a routine to check the position:

Code:

Sub CheckPosition()

On Error GoTo Err_Handler

'used in record counter

    N = DCount("*", "MyTableName")
    
    Select Case N
    
    Case 0 Or 1
        cmdFirst.enabled = False
        cmdLast.enabled = False
        cmdNext.enabled = False
        cmdPrev.enabled = False
    Case N
	mdFirst.enabled = True
        cmdLast.enabled = False
        cmdNext.enabled = False
        cmdPrev.enabled = True
    Case Else
        cmdFirst.enabled = True
        cmdLast.enabled = True
        cmdNext.enabled = True
        cmdPrev.enabled = True
        
    End Select
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & " in CheckPosition procedure : " & Err.Description, vbOKOnly + vbCritical
    Resume Exit_Handler
    
End Sub

Now just add the line CheckPosition in your button code e.g.

Code:

Private Sub cmdNext_Click()

On Error GoTo Err_Handler

    DoCmd.GoToRecord , , acNext   
    CheckPosition 
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    
    MsgBox "Error " & Err.Number & " in cmdNext_Click procedure : " & Err.Description, vbOKOnly + vbCritical
    Resume Exit_Handler
    
End Sub

Or reference CheckPosition in your Form_Current event
Modify as appropriate for your setup

moke123 · Mar 20, 2018

My problem is that when I click next on the last record it opens a new record which I don't want.

in the form properties under DATA set the Allow additions to no.

you can also use error handlers to go to the first or last records when you reach the end. Sort of a loop, when you reach the last record you go to the first. When going backwards when you reach the first you go to the last.

Code:

Private Sub cmdBack_Click()
    On Error GoTo cmdBack_Click_Error

    DoCmd.GoToRecord , , acPrevious

    On Error GoTo 0
    Exit Sub

cmdBack_Click_Error:

    If Err.Number = 2105 Then
        DoCmd.GoToRecord , , acLast
        Exit Sub
    End If

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdBack_Click of VBA Document Form_Form4"
End Sub

Private Sub cmdForward_Click()

    On Error GoTo cmdForward_Click_Error

    DoCmd.GoToRecord , , acNext

    On Error GoTo 0
    Exit Sub

cmdForward_Click_Error:

    If Err.Number = 2105 Then
        DoCmd.GoToRecord , , acFirst
        Exit Sub
    End If

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdForward_Click of VBA Document Form_Form4"

End Sub

you could also modify the above error handlers with

Code:

   If Err.Number = 2105 Then
       msgbox "You have reached the end"
        Exit Sub
    End If

Is the next record the last record? (1 Viewer)

DanielR

Registered User.

isladogs

MVP / VIP

moke123

AWF VIP

Similar threads

Users who are viewing this thread