Save record order in main table

[Phillip]

Hi,
I have a form which displays four field from a table. On the form I have 4
sort buttons which sorts the form based on the field selected. My table also
has a field for a sequence number. Based on the sort order selected I would
like the sequence number field for each record in the table to correspond to
the record number shown on the form. In other words, based on the sort the
first record shown on the form would get a sequence number of 1 in the table,
the second record shown would get a 2, etc.
Is this possible and if so could someone help with the code.
Your help would be greatly appreciated.
Thanks,

 

[Dennis]

Access tables don't have "record numbers" per se. This was a difficult thing
for me to understand, as I originally came from a mainframe environment,
where every record was identified by a RecNo. These days, you'd need to setup
an "ID" column as an AutoNumber which increments for every record posted to
the table. Using that methodology, you could get your record numbers. But
there would be "holes" when records are deleted.

 

[Paul Shapiro]

Hi,
I have a form which displays four field from a table. On the form I have
4
sort buttons which sorts the form based on the field selected. My table
also
has a field for a sequence number. Based on the sort order selected I
would
like the sequence number field for each record in the table to correspond
to
the record number shown on the form. In other words, based on the sort
the
first record shown on the form would get a sequence number of 1 in the
table,
the second record shown would get a 2, etc.
Is this possible and if so could someone help with the code.
Your help would be greatly appreciated.

If the sequence number is temporary, like a line number, and changes every
time you add/delete a row or change the displayed sort order, you would have
to calculate it in code. But it sounds like you want to store a sortOrder
value for each row, and have your code automatically update that value when
the user changes sorting. You can write code to process the form's recordset
when the sort order changes, and in that code you could update the stored
sequence number. You could either write a sql statement to automatically
update all rows, or use procedural code to loop through the form's
recordset.