number record of record

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?
 
A

Allen Browne

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
 
D

deb

WOW. I thought it was a simple task. I was so wrong.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top