User must be able to manually set the order of records

[Onno Willems]

Hi,

I'm looking for ideas for the following: I have a table with a bunch of
records. In a form, a user must be able to move the order of the records
until he/she thinks it's right after which the records are exported to
another database of text file in that order.

I'm thinking of something like the "change tab order" list of the form
designer (a user can select one or more rows in a list there and drag them
to new positions). I haven't got a clue how to make something like that.

Another idea I had was adding a sequence number to the records, creating a
continuous form ordered by that sequence number, putting up/down buttons on
the form to increase / decrease the sequence number and requery the form on
each click, but this seems rather clumsy.

Anyone got a better idea?

Thanks,
Onno Willems
The Netherlands

 

[John Vinson]

Another idea I had was adding a sequence number to the records, creating a
continuous form ordered by that sequence number, putting up/down buttons on
the form to increase / decrease the sequence number and requery the form on
each click, but this seems rather clumsy.

Clumsy perhaps, but that's the correct technique. A Table *has no
order* - it's a heap of data; only by including a field in the table
and creating a Query sorting by that field can you control the order
of records.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Onno Willems]

Hi John, thanks for your reply.

I know a table has no order and that a query on a 'sequence' field is the
solution to have the records in a certain order.

With the 'clumsy' part I was trying to refer to the idea of the continues
form. It takes a lot of work to create a thing like that and to make it
workable for a user. Think what should happen if user presses the down
button on a record: the sequence numer should be increased, the record that
had that number originally should have its number decreased and then the
whole form needs to be requeried in order to show the new sequence. I that
proces, the current selected record gets lost, can scroll outside the part
that is visible (if the continuous form has a scrollbar), etc..

Something like a list box with draggable items would look much better, but
I'm lost on how to create that.

Regards,
Onno

 

[John Vinson]

Something like a list box with draggable items would look much better, but
I'm lost on how to create that.

If you find out - let me know! I agree that the continuous form
solution is a chore, but it's the only way I've found to accomplish
this task.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Onno Willems]

I've spend last evening looking for another solution, but didn't find one.
Experimented a little with a Tree Control (which seems to support dragging)
but couldn't find proper documentation or VBA samples.

I'm going to work on my continuous form now! ;-(

Regards,
Onno