Record numbers

  • Thread starter Thread starter JohnE
  • Start date Start date
J

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
 
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
 
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
.
Click to expand...
 
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
 
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
Click to expand...


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
 
Back
Top