Record # of Total

[striker73]

I would like to be able to display to the user in a label that we are on record "1 of 4" or something similar based on the number of entries entered. Right now it displays this information on the bottom of the screen (ie: 1 of 4 (Filtered)), but I would like to be able to pull this information into a label in a more visible place. Any ideas? I have tried Me.Count, but that doesn't seem to work.

 

[FoxProProgrammer]

Hi striker,

Count is used to count the number of objects in a collection, not the number of records in a recordset. You can count the number of records in a recordset with the following code. This code assumes that the form is bound to a Query or Table. If it isn't, the RecordsetClone Property won't work and I can suggest alternate code.

Dim rs As DAO.Recordset
Dim numRecs As Integer

Set rs = RecordsetClone
rs.MoveLast
numRecs = rs.RecordCount

You could use the AbsolutePosition Property to determine the current record number and assign a message to a text box named StatusMsg, for example.

StatusMsg.Value = "Viewing Record " & Str(rs.AbsolutePosition + 1) & " of " & numRecs

This code should be placed in the On Current event so it runs whenever the record pointer changes.

dz
dzaccess@yahoo.com

 

[striker73]

I tried that and it displays fine except when there are more than one entry. I have one double entry and it displays "Record 1 of 2" when you first open that record, but then when I move to the second one, it still says "Record 1 of 2". I tried displaying rs.AbsolutePosition and every time it displays 1. Any ideas? Thanks!

 

[FoxProProgrammer]

Dumb question. What do you mean by "double entry".

Thanks,
dz
dzaccess@yahoo.com

 

[striker73]

Well the system is set up to track the various computers that a user has. Most users have only 1 computer registered to them, but a few have 2. By "double entry" I just mean that a user has 2 computers registered to him. It is this scenario that I want to be able to say "Viewing 1 of 2 records" or something similar.

 

[danvlas]

You need to Bookmark the RecordsetClone before checking for AbsolutePosition:

Sub Form_Current()
RecordsetClone.Bookmark = Bookmark
StatusMsg = "Viewing Record " & Str(RecordsetClone.AbsolutePosition + 1) & " of " & RecordsetClone.RecordCount
End Sub

That's because the RecordsetClone's current record does not necessarily coincide with what you display on screen.


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[striker73]

Do I need to declare the RecordsetClone as something? I am getting the error message that the object doesn't support that particular property on the StatusMsg = ... line.