Auto number records when a form opens

[Phillip]

Hi,
I have a table that contains fields: Title, Location, etc. and a number
field. Is it possible than when I open a continious form that displays the
data from the table that the number field would be changed to reflect the
order of the records in the form? In other words for the first record the
number field would have the number 1, second record the number 2, etc. I'm
looking at having various forms sorted by the different fields and I want the
records in each form to start with the number 1.
This may be strange, but there is madness behind my interest.
Thanks for your help.

 

[Allen Browne]

The simplest way to have 1 for the first record, 2 for the next, and so on,
is to set the form's Navigation Buttons property to Yes. It will then show
the numbers for you in the horizontal scrollbar.

Of course, you don't *store* these numbers, as they are not part of the
record. They change as the form is sorted, or filtered, or as records are
added, edited, or deleted. They are therefore about the *display* of the
records only -- not anything to do with the values in the records.

If you want to achieve it without using the Navigation Buttons, use a text
box with Control Source set to:
=[Form].[CurrentRecord]

More info in this article:
Numbering Entries in a Report or Form
at:
http://allenbrowne.com/casu-10.html

 

[Phillip]

Hi again and thanks for your response,
I was looking for a way to have the number as part of the record and
therefore be stored with the record in the source table. Is this not
possible?
Thanks,
Phil

The simplest way to have 1 for the first record, 2 for the next, and so on,
is to set the form's Navigation Buttons property to Yes. It will then show
the numbers for you in the horizontal scrollbar.

Of course, you don't *store* these numbers, as they are not part of the
record. They change as the form is sorted, or filtered, or as records are
added, edited, or deleted. They are therefore about the *display* of the
records only -- not anything to do with the values in the records.

If you want to achieve it without using the Navigation Buttons, use a text
box with Control Source set to:
=[Form].[CurrentRecord]

More info in this article:
Numbering Entries in a Report or Form
at:
http://allenbrowne.com/casu-10.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have a table that contains fields: Title, Location, etc. and a number
field. Is it possible than when I open a continious form that displays
the
data from the table that the number field would be changed to reflect the
order of the records in the form? In other words for the first record the
number field would have the number 1, second record the number 2, etc.
I'm
looking at having various forms sorted by the different fields and I want
the
records in each form to start with the number 1.
This may be strange, but there is madness behind my interest.
Thanks for your help.

.

 

[Phillip]

Hi again,
Yes, I want it to renumber with different filters on the form. It's going
to be used for different reports based of the filter.
Thanks,

Hi again and thanks for your response,
I was looking for a way to have the number as part of the record and
therefore be stored with the record in the source table. Is this not
possible?
Thanks,
Phil

The simplest way to have 1 for the first record, 2 for the next, and so on,
is to set the form's Navigation Buttons property to Yes. It will then show

[quoted text clipped - 29 lines]

.

Making the number part of the record doesn't really make sense. What happens
when you filter the form so that you only see a subset of the original
records? You would have to renumber all over again.

 

[Allen Browne]

Hi Phillip

Like Pieter Linden, I don't understand why you need to modify your table
data to generate these reports. If you want sequential numbering in a
report, all you have to to is add a text box with these properties:
Control Source =1
Running Sum Over All

I don't understand the point of having sequential numbers being stored in a
table, for the records being currently viewed in a form. Say you have 2
users, using different filters and sort orders at the same time. The idea of
stored sequential numbers that reflect the current filter/sort just doesn't
make sense in a multi-user database (or even a database with 2 forms that
could be sorted/filtered differently.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi again,
Yes, I want it to renumber with different filters on the form. It's going
to be used for different reports based of the filter.
Thanks,

Hi again and thanks for your response,
I was looking for a way to have the number as part of the record and
therefore be stored with the record in the source table. Is this not
possible?
Thanks,
Phil

The simplest way to have 1 for the first record, 2 for the next, and
so on,
is to set the form's Navigation Buttons property to Yes. It will then
show
[quoted text clipped - 29 lines]

.

Making the number part of the record doesn't really make sense. What
happens
when you filter the form so that you only see a subset of the original
records? You would have to renumber all over again.

 

[John W. Vinson]

Yes, I want it to renumber with different filters on the form. It's going
to be used for different reports based of the filter.

That's a good reason NOT to store them in the table!

These numbers are volatile: they don't depend just on the record that they're
in. They also depend on the context - what *OTHER* records are selected by the
filter, the sort order of the records, etc.; if you store the number in a
table, and then add or delete some completely unrelated record, the number you
store in the table is now WRONG.

I'd suggest that you dynamically calculate this sequential number in both the
Form and also in the Report, using the criteria in the filter and the sort
order. This is easier on a report than on a form: you can use a textbox on the
report with its Running Sum property set to "Over All" (or "Over Group" if you
want several independent number sequences for different subsets). On a Form
you will need a calculated expression in the form's recordsource query such as

=DCount("*", "queryname", "[sortkey] <= " & [sortkey])

where sortkey is a field, or an expression, which defines the sort order in
the query.