Show form record number in query results

[RealGomer]

I have a database with around 4,400 records. The main form I use to manage
the data combines all these records into 360 "pages". I have the main form
and included sub-forms designed to show the record ID for the record being
displayed. As an example, the main form will show a field for companies table
record nbr 170, and a field for (subform) agreements table record nbr 206.
There are several other subforms but I don't show the record ID for the
records. In the bottom left corner of the main form is shown Record 102 of
360.
Is there a way to create a query that will show the main form's record
number in addition to other info I need? By showing this record number I can
just enter it into the Record box on the main form and jump directly to the
required "page" without having to do a search for each company first.
Thank you.

 

[Allen Browne]

For options on numbering the records in a query, see:
Ranking and numbering records
at:
http://allenbrowne.com/ranking.html

I would certainly not recommend doing that as a way of chosing the record in
a form though. There are too many cases where it just won't work, e.g.:
- if the user filters the form
- if the user sorts the form differently
- if the form is in Data Entry mode
- if the form is a subform (hence showing only records relevant to the one
in the main form.)

 

[Klatuu]

What Allen is saying is that the record number you see displayed has no
presistent relationship to the current record. It is what is known as the
Absolute Position. That is, the record's ordinal position in the current
recordset. The only time it can be used to navigate to that record is in the
current session and that is provided that since it was the current record you
have not filtered the form, changed the sort order of the form, added
records, or deleted records. Any of those actions will change that number.

I suggest you go to the link Allen provided. He has some execellent
suggestions there that I think will resolve your issue.

 

[RealGomer]

Thank you Klatuu and Allen. The info in the mail form doesn't change, only in
the subforms. Once the main form data / table is created it is set in stone.
I did discover a kludge workaround. I built a few queries that basically
looked for data from one year that populated a subform but did not have any
corresponding data from another year (data existed for 2007 but not 2008).
The kludge was then selecting the main form primary field (my term) and just
using the Page down / Up keys to see if I missed any. For 360 main form
pages it wasn't too bad. It will decrease when I archive all of the kaput
records.