Thanks Fred. That was what I had in mind. Assume I have a sequence number
and I have 1 to 10 for 10 records. I want to move 5 to position 4. I would
- Sort the records by sequence
- Find the record 5
- Change the sequence number to 0
- Find previous record
- Change the previous record sequence number to 5
- Find the record with sequence number 0
- Change the number to 4
- Resort the records
Just thought I might be able to save myself a few hours coding if I could
start with what someone else had done.
fredg said:
I have a need to change the order of records in a datasheet. For example, I
might want to move record 5 appear as the fourth record in the list. I want
to have up and down buttons to move a selected record in the list.
I am sure I can do it with a sequence number in the record, and through code
renumber records then resort but thought if someone else has done it, why
reinvent the wheel. Has anyone seen an example on the net I can download.
Access stores records in no particular order, so if you wish to
display a record in any particular order you will have to include a
field in the table (let's call it the [SortBy] field) that indicates
each record's position in the table. Then, using a query as the form's
record source, sort the query, and therefore the form, on this field.
All you would need do to change that record's position is change the
value in this SortBy field and refresh the form..
I've actually used this method to sort data according to a non-logical
sort order (changed by the user).
Make sure the query that is the form's record source includes the
[Sequence] field and that is the field used to sort the query.
Code the [Sequence] control's AfterUpdate event:
Me.Requery
If you don't care about which records are 1 through 4 and you just
wish a certain record to be #5, set that record to 1, any four other
records to 0 and all the other records to 2. That record (#1) will
display as the 5th record.
If you do need the full table sorted exactly in a certain order, then
you will need to number each record sequence accordingly. Each time
you change a record's Sequence value the entire display will re-sort.
Remember, you do not need to have consecutive numbering. You just need
the number to be the 5th value after sorting, i.e. 2, 5, 7, 9, 12,
13, 14, ... etc., Record 12 will be 5th in the list.
