Re-ordering data items in a list box and writing the new order back

[Guest]

I have a situation where new records are added to a table via direct input or by import from Excel. These records need to have a "display order" that can only be determined by an expert user by means of looking at the records and making a judgement. Currently the user looks at a subset of the data using a datasheet view and manually assigns a value to an (integer) "Display Order" field. This is acceptable for a one off but inserting a new record or records (which often happens)means that other records need to have the display order changed manually - a tedious and time consuming task.

To improve this I would like to display a list box (or similar) with the records arranged according to the current display order, allow the user to re-order the records using drag drop (ideally) and then at the push of a magic button the re-ordered data to be written back to the parent table.

I have got part of the way with this but it's a bit clunky to say the least. Has anybody out there got an inspired idea?

Thanks

 

[Wayne Morgan]

I have done it by putting 2 buttons next to a list box. I called the buttons
Up and Down. The listbox was a single select listbox and the buttons would
work on the selected item. Each time you clicked the button, you would add
or subtract one from the selected item and the one above or below it as
appropriate then requery the listbox. You have to check for the item at the
top or bottom if you try to "loop it around the back" or you could simply
prevent moving the bottom item down and the top item up.

--
Wayne Morgan
Microsoft Access MVP

I have a situation where new records are added to a table via direct input

or by import from Excel. These records need to have a "display order" that
can only be determined by an expert user by means of looking at the records
and making a judgement. Currently the user looks at a subset of the data
using a datasheet view and manually assigns a value to an (integer) "Display
Order" field. This is acceptable for a one off but inserting a new record
or records (which often happens)means that other records need to have the
display order changed manually - a tedious and time consuming task.

To improve this I would like to display a list box (or similar) with the

records arranged according to the current display order, allow the user to
re-order the records using drag drop (ideally) and then at the push of a
magic button the re-ordered data to be written back to the parent table.

I have got part of the way with this but it's a bit clunky to say the

least. Has anybody out there got an inspired idea?

 

[Guest]

Wayne

Thanks for the tip. I'm trying to go a bit further and have already managed to use list box click (to select) and Shift Click (to position) the item in the list as viewed in the list box . This process (with a requery) redisplays the list with the items in the right order but does not update the "Display Order" field. This is Ok for the user because the order the item is shown in the list is in effect the display order in his perception

To write the data back to the table I thought that having an "update" button with some code to go through the records as displayed in the list making the first item "Display order = 1", second item "Display Order = 2" and so on would be fine and easy.... I WAS WRONG! It's this bit I'm struggling with

Maybe I'm totally wrong in doing this but - the list box is linked to the forms recordset and in trying to update the list and recordset with the new display order I'm not getting updates to either and to make matters worst Access falls over when I try to run the bit of code that I think should work

Any ideas

Thanks

Chris

 

[Wayne Morgan]

What is the "Display Order field"? How is the listbox tied into the form's
recordset? Is the bound field of the listbox bound to one of the form's
recordset fields? What is the Row Source of the listbox, is it a from a
separate table?

You may need 3 fields for the data in the listbox. A text field for the user
to read, an ID field to link to the other table(s), and an Order By field
that you will fill in as you move items in the listbox. You would use this
field in a query to sort on. After you change the order, you will need to
requery anything relying on that order (i.e. the form), not just the
listbox.

--
Wayne Morgan
Microsoft Access MVP

Wayne,

Thanks for the tip. I'm trying to go a bit further and have already

managed to use list box click (to select) and Shift Click (to position) the
item in the list as viewed in the list box . This process (with a requery)
redisplays the list with the items in the right order but does not update
the "Display Order" field. This is Ok for the user because the order the
item is shown in the list is in effect the display order in his perception.

To write the data back to the table I thought that having an "update"

button with some code to go through the records as displayed in the list
making the first item "Display order = 1", second item "Display Order = 2"
and so on would be fine and easy.... I WAS WRONG! It's this bit I'm
struggling with.

Maybe I'm totally wrong in doing this but - the list box is linked to the

forms recordset and in trying to update the list and recordset with the new
display order I'm not getting updates to either and to make matters worst
Access falls over when I try to run the bit of code that I think should
work.

 

[Guest]

Well I have got it working, due I think to having "vocalised" the problem on the forum and being promted by your comments. But in doing so I have committed the cardinal sin of not quite understanding how the code is working!

I'm going through it at the moment to suss it out.

What I've ended up with is a list box displaying the data the user needs in order to decide what relative order the items should be in. He can then select and item (the list changes colour to indicate this) and click on its correct position at which point the new "display order" - effectively a field to order by - is written to the table, the list box is updated and the colour of the list returned to normal ready for the next selection.

It could be more refined but it works....

Thanks for your help.

 

[Wayne Morgan]

Your welcome. It sounds like an excellent solution.

--
Wayne Morgan
Microsoft Access MVP

Well I have got it working, due I think to having "vocalised" the problem

on the forum and being promted by your comments. But in doing so I have
committed the cardinal sin of not quite understanding how the code is
working!

I'm going through it at the moment to suss it out.

What I've ended up with is a list box displaying the data the user needs

in order to decide what relative order the items should be in. He can then
select and item (the list changes colour to indicate this) and click on its
correct position at which point the new "display order" - effectively a
field to order by - is written to the table, the list box is updated and the
colour of the list returned to normal ready for the next selection.