Test for last Record , GoTo First Record, else GoTo Next Record, (1 Viewer)

[Keith Nichols]

Hi,

Hope this is a nice and simple one. I know I have seen various "last record" references in various threads but I couldn't find one in a search.

I have a command button on my form which navigates you to the next record. I use this rather than the navigation buttons as I can set the tab order to go to it after all the fields are filled. This works fine unless the form is on the last record, in which case it throws up an error. To get round this, I want to test that the current record is not the last record.

It is the test in the If statement I need help with. How do I check that the current record is not the last record in my form? I think it will use the dcount but I don't know how.

Private Sub cmd_Next_Record_Click()
On Error GoTo Err_cmd_Next_Record_Click

If (current record < dcount) then
DoCmd.GoToRecord , , acNext
Else
DoCmd.GoToRecord , , acFirst
End If

Exit_cmd_Next_Record_Click:
Exit Sub

Err_cmd_Next_Record_Click:
MsgBox Err.Description
Resume Exit_cmd_Next_Record_Click
End Sub

Thanks in advance.

Keith.

 

[MarkK]

Check out the AbsolutePosition property of the recordset object.

With rst
  If .AbsolutePosition = .RecordCount - 1 Then
[COLOR="Green"]    'you are on the last record[/COLOR]
  Else
[COLOR="Green"]    'you are some other record[/COLOR]
  End if
End With

I think, but check it out...

 

[Keith Nichols]

Check out the AbsolutePosition property of the recordset object.

With rst
If .AbsolutePosition = .RecordCount - 1 Then
'you are on the last record
Else
'you are some other record
End if
End With
I think, but check it out...

Public Sub Next_Record()
On Error GoTo Err_Next_Record

With recordset
  If .AbsolutePosition = .RecordCount - 1 Then    
'you are on the last record
        DoCmd.GoToRecord , , acFirst
    
  Else                                              'you are on some other record
        DoCmd.GoToRecord , , acNext
  End If
End With

Exit_Next_Record:
    Exit Sub

Err_Next_Record:
    MsgBox Err.Description
    Resume Exit_Next_Record
End Sub

__________________________________________________

Private Sub cmd_Next_Record_Tab_9_Click()
Call Next_Record
End Sub

 

[Keith Nichols]

Check out the AbsolutePosition property of the recordset object.
.......

but check it out...

Hi Lagbolt,

"rs" did not work but "recordset" did. Maybe if I had a dim statement like:
dim recordset = rs
Your code would have worked I made the routine public so I could call it from a separate button on each tab of my form (9 tabs in total).

Anyway, many thanks for the pointer, I wouldn't have got there without it.

Regards,

Keith.

Public Sub Next_Record()
On Error GoTo Err_Next_Record

With recordset
  If .AbsolutePosition = .RecordCount - 1 Then    
[COLOR="Green"]'you are on the last record[/COLOR]
        DoCmd.GoToRecord , , acFirst
  Else                                              
[COLOR="Green"]'you are on some other record[/COLOR]
        DoCmd.GoToRecord , , acNext
  End If
End With

Exit_Next_Record:
    Exit Sub

Err_Next_Record:
    MsgBox Err.Description
    Resume Exit_Next_Record
End Sub
__________________________________________________

Private Sub cmd_Next_Record_Tab_9_Click()
Call Next_Record
End Sub

PS: I think I accidentally posted this half way through editing - if so, my apologies, if not, please ignore.

 

[Mindzeye]

This works great when using the next button to navigating from the last record to the first record but I am having trouble using the previous button and navigating to the last record. see below
On Error GoTo Err_Previous_Record
With Recordset
If .AbsolutePosition = .RecordCount = 0 Then
'you are on the first record
DoCmd.GoToRecord , , acLast

Else
'you are on some other record
DoCmd.GoToRecord , , acPrevious
End If
End With
Exit_Previous_Record:
Exit Sub
Err_Previous_Record:
MsgBox Err.Description
Resume Exit_Previous_Record
End Sub

Regardless what record I am on it goes to the last record

 

[Strike_Eagle]

Regardless what record I am on it goes to the last record

Hi, you are correct because you have no way to get to the proper part fo the code! Try this instead:

Private Sub previousButton_Click()
' I take the easy way out for error code
On Error Resume Next
    ' Use the recordset currently available (I have a filtered query and it only loops through filtered records)
    With recordset
    
        ' Determines position and if true goes to last record.
        ' Basically, I compared the absolute position, like above,
        ' The first record would be 1, always, no matter how many records are there.
        ' So if I test for zero, I can determine where I am
        ' on a record and should instead go to the last record. if I am trying to go to previous from the first
        ' .Recordcount is necassary above because you may not know how many records there are.
        ' Here, there is always at least one record, hopefully!
        
        ' Test for position
        If .AbsolutePosition = 0 Then
        
            ' Go to last record if on first record
            DoCmd.GoToRecord , , acLast
        
        ' Go to previous record if not on first
        Else
        
            ' go to previous record in display
            DoCmd.GoToRecord , , acPrevious
        
        ' end the if statement
        End If
    
    ' end the with statement
    End With
' end the sub
End Sub

I hope this is useful! Enjoy

 

[Frothingslosh]

 

[Strike_Eagle]

Haha yeah but the second guy never got a reply so thought I might help another searcher!