number record of record

  • Thread starter Thread starter deb
  • Start date Start date
D

deb

I have a form(f2PlantUnit) with a subform(f3UnitSub) linked by the field
ProjectID

On the subform, I have the arrows that go First, Previous, Next, Last record.
I would like to show ...
1 of 2 ...if they are on record 1 of 2 records.
.... next to the arrows to let the user know how many records there are and
which record they ar on.

Any ideas?
 
You can show the first number in a text box with ControlSource of:
=[Form].[CurrentRecord]
There's a bit more involved in getting the second number (after the OF.)

Essentially, you want to refer to:
=[Form].[Recordset].[RecordCount]
but there are several problems with that expression:

a) It does not work in Access 2007.

b) In older versions (97 and earlier), you need to use:
=[Form].[RecordsetClone].[RecordCount]

c) It displays the number of records loaded so far, which may not the the
total count. For example, if the form is based on a query, it will probably
show 1 when the form first loads (or zero if there are no records at all),
and only update later (when the form recalculates.)

d) It does not adjust for the new record, in the way the built-in navigation
button-display does.

e) It shows #Error in if you filter a form so it contains no records, and no
new record can be added.

To work around these issues:
1. Use a function call, so it works in Access 2007.

2. In the function use MoveLast so it shows all records. (This can seriously
increase the amount of time it takes for your form to load.)

3. Test the form's NewRecord property, so see if you need to add one.

4. Use error handling in your function to avoid the error.

You can find an example function that does this (except for the time delay
introduced by #2) here:
Avoid #Error in form/report with no records
at:
http://allenbrowne.com/RecordCountError.html
 
WOW. I thought it was a simple task. I was so wrong.

Thank you, I will give it a try and see what happens.
 
Back
Top