Record numbers

[JohnE]

Hello NG. I have a form that I am adding a txt box to
that will display the number of records associated to a
client code. Below is the code that I have so far and it
works. The exception is when I have a client that does
not have any info on the form as of yet. So before I can
even add any info, as soon as I bring a client up on the
form I get an error because of no info. I can not figure
out how to place the code for not having any records.
****************
Private Sub Form_Current()
Dim rst As dao.Recordset
Dim lngCount As Long
Set rst = Me.RecordsetClone
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
Me.txtRecordNumber = "Record" & Me.CurrentRecord
& "of" & lngCount

End Sub
*****************
If anyone can help out, thanks.
*** John

 

[Dave Jones]

John,

As there are no records in the recordset, using Movefirst
generates an error.
You need to test to see if theere are any records before
trying to move through the recordset.
Try:

Private Sub Form_Current()
Dim rst As dao.Recordset
Dim lngCount As Long
Set rst = Me.RecordsetClone
If rst.BOF=false then
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
Me.txtRecordNumber = "Record" & Me.CurrentRecord
& "of" & lngCount
Else
Me.txtRecordNumber = "Record 0"
& "of 0"
End If
End Sub

Dave

 

[JohnE]

Dave, thanks. It worked perfectly.
*** John

-----Original Message-----
John,

As there are no records in the recordset, using Movefirst
generates an error.
You need to test to see if theere are any records before
trying to move through the recordset.
Try:

Private Sub Form_Current()
Dim rst As dao.Recordset
Dim lngCount As Long
Set rst = Me.RecordsetClone
If rst.BOF=false then
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
Me.txtRecordNumber = "Record" & Me.CurrentRecord
& "of" & lngCount
Else
Me.txtRecordNumber = "Record 0"
& "of 0"
End If
End Sub

Dave
.

 

[Guest]

Dave, I hope you get this. But I spoke to soon. It
errors out at .MoveLast. I changed the BOF to EOF and it
still errors. When I bring in a new client that has no
records it is fine. But when I go to Requery is when it
error. Any suggestions?
*** John

 

[Dirk Goldgar]

Dave, I hope you get this. But I spoke to soon. It
errors out at .MoveLast. I changed the BOF to EOF and it
still errors. When I bring in a new client that has no
records it is fine. But when I go to Requery is when it
error. Any suggestions?
*** John

Try this:

Private Sub Form_Current()

With Me.RecordsetClone
If .RecordCount <> 0 Then .MoveLast
Me.txtRecordNumber = _
"Record" & Me.CurrentRecord & _
" of " & .RecordCount
End With

End Sub