Problem: Display Record # of # on continuous subform?

[Tec92407]

I have a main form and continous subform that I would like to display the
current record number and total number of records similar to the buit-in
navigation. The main form is based on a table containing purchase
requests(PRRequisition). The subform is based on a table containing Items to
be purchased (PRItems). The tables are related in a one to many fashion with
the PRRequisition Table containig the primary key and the PRItems containing
the foreign key..

I have a text box on the footer of each form containing
the following in the control source property:

On main form:
=[CurrentRecord] & " of " & Count([PRID]) & " of " & DCount(" [PRID]
","PRRequisition")

On subform:
=[CurrentRecord] & " of " & Count([ItemID]) & " of " & DCount(" [ItemID]
","PRItems")

The text box on the main form displays correctly but on the subform, the
displayed numbers don't seem to be accurate. The currect record always seems
to show one more record then there really is and doesn't follow the mouse
focus. The total doesn't display correctly, sometimes displays less than
there really is.

Have I coded this correctly? What am I missing?

 

[Tec92407]

More info:

I've change the subform text box control source property to this:
=[CurrentRecord] & " of " & Count([ItemID]) & " of " &
Form.RecordsetClone.RecordCount

The total records number now works correctly. However, if the table contains
3 records, the display shows 1 of 3 of 3, no matter which record has focus or
if I use the record navigation buttons.

Any help is appreciated.

 

[Allen Browne]

This is messy stuff.

When the form loads, Access displays the first record before it loads them
all. That means your screen is initially going to show a record count that
does not reflect all the records that will be loaded into the form.

You can work around that by forcing Access to load all the records before it
displays anything, but this will make the form *very* slow to load if it has
a large number of records. If you want to do it anyway:
Private Sub Form_Load()
With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
End If
End With
End Sub

Once the full record count is known, your control can show the correct
count. But that does not mean Access will keep it up to date. Updating
calculated controls is a low priority task in Access. If there is anything
else that needs processor time (such as running code, fetching records, or
background processes), the info in the calculated control will lag. You can
force it to update the calculations with:
Me.Recalc
but again, this has performance implications for other things it should be
doing, so you are bogging your application down.

The final issue is with the new record. If a form is bound to a table that
has no records, the nav buttons will display:
Record 1 of 1
i.e. it counts the new record as one even through there are none. You will
need to adjust your count to allow for whether or not you are at the new
record if you want to simulate the behavior of the built-in nav buttons.