Next button problem

[Jason]

I'm running the code below for a command button that does record navigation.

The problem I have is that when I open the form, it thinks there is only one
record, so it moves to the Else portion of the code. I've tried doing a
movelast, movefirst so it has an accurate record count, but then the first
time I click the button it runs the first part of the If statement but
doesn't move to the next record!

Any help would be appreciated.

Private Sub cmdNextRecord_Click()
On Error GoTo Err_cmdNext_Click

If Me.CurrentRecord < Me.Recordset.RecordCount Then
Me.Recordset.MoveNext
Else
DoCmd.OpenForm "frmGrade"
End If

Exit_cmdNext_Click:
Exit Sub

Err_cmdNext_Click:
MsgBox Err.Description
Resume Exit_cmdNext_Click

End Sub

 

[Dirk Goldgar]

I'm running the code below for a command button that does record
navigation.

The problem I have is that when I open the form, it thinks there is only
one
record, so it moves to the Else portion of the code. I've tried doing a
movelast, movefirst so it has an accurate record count, but then the first
time I click the button it runs the first part of the If statement but
doesn't move to the next record!

Any help would be appreciated.

Private Sub cmdNextRecord_Click()
On Error GoTo Err_cmdNext_Click

If Me.CurrentRecord < Me.Recordset.RecordCount Then
Me.Recordset.MoveNext
Else
DoCmd.OpenForm "frmGrade"
End If

Exit_cmdNext_Click:
Exit Sub

Err_cmdNext_Click:
MsgBox Err.Description
Resume Exit_cmdNext_Click

End Sub

This happens because the recordset's RecordCount isn't normally available
until all records have been traversed, and that process occurs
asynchronously. Try using the form's RecordsetClone, and preloading it in
the form's Load event, like this:

'----- start of code -----
Private Sub Form_Load()

Me.RecordsetClone.MoveLast

End Sub


Private Sub cmdNextRecord_Click()

On Error GoTo Err_cmdNext_Click

If Me.CurrentRecord < Me.RecordsetClone.RecordCount Then
Me.Recordset.MoveNext
Else
DoCmd.OpenForm "frmGrade"
End If

Exit_cmdNext_Click:
Exit Sub

Err_cmdNext_Click:
MsgBox Err.Description
Resume Exit_cmdNext_Click

End Sub
'----- end of code -----

Note that, if your form's recordset is slow to load, this may cause an
appreciable delay when your form loads.

 

[BruceM]

This is what I do to get a record count:

Dim lngCurrent As Long, lngTotal As Long

lngCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
lngTotal = Me.RecordsetClone.RecordCount

After that I enable or disable my custom navigation buttons depending on
where I am in the recordset:

Me.cmdFirst.Enabled = Not lngCurrent <= 1
Me.cmdPrevious.Enabled = Me.cmdFirst.Enabled
Me.cmdNext.Enabled = (lngCurrent = 1 And lngTotal > 1) _
Or lngCurrent < lngTotal
Me.cmdLast.Enabled = Me.cmdNext.Enabled
Me.cmdNew.Enabled = Not Me.NewRecord

If you are on the last record of a recordset the Else condition will apply,
so be sure that is not the case.

Do I understand correctly that if the user is at the last record and clicks
the Next button frmGrade will open? Perhaps it would be clearer to the
users if you disable the Next button along the lines suggested so they can't
try to move past the last record, and have them open frmGrade as a specific
action.

Back to your specific question, try MoveLast for the RecordSetClone, then
get the count of the RecordSetClone. It may be something like this:

Me.RecordSetClone.MoveLast
If Me.CurrentRecord < Me.RecordsetClone.RecordCount Then
Me.Recordset.MoveNext
Else
DoCmd.OpenForm "frmGrade"
End If

Again, consider whether it is the best thing to open another form if the
user clicks one time too many on the Next command button.