Cmd button First and Last error message

[DuncanKing]

The code for previous record and next record gives an error when click beyond the first/last record. I have no way of knowing that i'm at the first/last record.
Here's the code

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

Is there a piece of code that will give a "user friendly" message when I have reached the first/last record?

Duncan

 

[DuncanKing]

PS the error message I get is "You can't go to the specified record"

 

[PHV]

Something like this ?
' previous record VBA code
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.MovePrevious
If .BOF Then
MsgBox "You already have reached the first record"
.MoveFirst
Else
Me.Bookmark = .Bookmark
End If
End With
Set rs = Nothing

' next record VBA code
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.MoveNext
If .EOF Then
MsgBox "You already have reached the last record"
.MoveLast
Else
Me.Bookmark = .Bookmark
End If
End With
Set rs = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[missinglinq]

Another approach that I've used for this problem is to have the record "roll over." If you're on the last record and click on "Next" you roll over to the first record. Likewise, if you're on the first record and hit "Previous" it rolls over to the last record.

You'll need to replace the "Go2Next_Click" and "Go2Prev_Click" with the actual names of your subs.

Private Sub Go2Next_Click()
On Error GoTo Err_Go2Next_Click
DoCmd.GoToRecord , , acNext

Exit_Go2Next_Click:
Exit Sub

Err_Go2Next_Click:
DoCmd.GoToRecord , , acFirst
End Sub

Private Sub Go2Prev_Click()
On Error GoTo Err_Go2Prev_Click
DoCmd.GoToRecord , , acPrevious
Exit_Go2Prev_Click:
Exit Sub

Err_Go2Prev_Click:
DoCmd.GoToRecord , , acLast
End Sub

Hope this helps!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

How are ya DuncanKing . . .

Might as well add my 2 cents . . .

MovePrevious Code:

[blue]   Dim Msg As String, Style As Integer, Title As String
   
   If Me.CurrentRecord = 1 Then
      Msg = "Can't move Previous! . . . You are at the 1st Record!"
      Style = vbInformation + vbOKOnly
      Title = "Can't Move Previous! . . ."
      MsgBox Msg, Style, Title
   Else
      DoCmd.RunCommand acCmdRecordsGoToPrevious
   End If[/blue]

MoveNext code:

[blue]   Dim Msg As String, Style As Integer, Title As String
   
   If Me.CurrentRecord = Me!RecordsetClone.RecordCount Then
      Msg = "Can't move Next! . . . You are at the Last Record!"
      Style = vbInformation + vbOKOnly
      Title = "Can't Move Next! . . ."
      MsgBox Msg, Style, Title
   Else
      DoCmd.RunCommand acCmdRecordsGoToNext
   End If[/blue]

See Ya! . . . . . .

 

[DuncanKing]

There is something wrong with this piece of code. I get "cant find the fiels RecoredsetClone"

If Me.CurrentRecord = Me!RecordsetClone.RecordCount Then

The "move previous" works great.

Duncan

 

[TheAceMan1]

DuncanKing . . .

Sorry about that! Change the exclamaton in . . .

[blue] Me[red][b]![/b][/red]RecordsetClone.RecordCount[/blue]

. . . to a dot . . .

[blue] Me[red][b].[/b][/red]RecordsetClone.RecordCount[/blue]

See Ya! . . . . . .

 

[DuncanKing]

AceMan...Brilliant...that works

Thanks