Prev: Set a report's Sort/Group programmatically?
Next: Capture Delete event
From: Phillip on 26 Mar 2010 11:06
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,
From: Dennis on 26 Mar 2010 11:36
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.

"Phillip" wrote:

> 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,
From: Paul Shapiro on 26 Mar 2010 12:47
"Phillip" <Phillip(a)discussions.microsoft.com> wrote in message
news:FD632F76-0A71-478D-890B-05B30F52002F(a)microsoft.com...
> 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.

 | 
Pages: 1
Prev: Set a report's Sort/Group programmatically?
Next: Capture Delete event