Unanswered: User-friendly way to customize sort order
I have some very large lookup tables.
I have added an "Order" column where I can assign each record a number so that I can sort the lists in the order I want rather than the order the items were entered.
The lists are getting rather large and hand entering the "Order" value is now a major pain.
Does anyone know where I can get a drag and drop ordering form similar to the Tab Order wizard? One where I can just drag the record to the appropriate place in the list and drop it. Then the code changes the order column for me.
Has anyone built one of these before? Would you be willing to share?
There's nothing like that built into Access but you can build it yourself although with some coding effort.
Assuming you have an endless-form you can catch the MouseUp event and check the Y variable it gets passed. If it it <0 you have dragged the mouse up from the control, if it is > the controls height it has been dragged down. At a closer look you'll have to also consider the position of the control on the detail range. Thus: Y<(-Control.Top) means drag up, Y>(-Control.Top+Detail.Height) means drag down. Of couse you want to know how far the user dragged, basically how many (complete) detail areas the user dragged over considering the offset from the control top:
DragAmount = fix((Y+Control.Top)/Detail.Height). The rest should be a piece of cake, here some pseudo code for moving up
make room in your table for the entry:
UPDATE sometable SET sort = sort + 1
WHERE sort >= CurrentSortValue+DragAmount
move the entry:
UPDATE sometable SET sort = CurrentSortValue+DragAmount
WHERE sort = CurrentSortValue+1
fill the gap:
UPDATE sometable SET sort = sort - 1
WHERE sort > CurrentSortValue
This approach still has some major drawbacks. Important is here the sort value is gapless, otherwise the DragAmount won't directly relate to the actual values of the sort field. And it's a real pain getting the value of a record relative to the current one in position (you could by opening a cursor with the form's recordsource or jump between the record in the form itself). The other one is the limitation to the current visible records, you can't move the mouse over the edge of the visible area and scroll the form. If you do so the record will be moved higher but without visual aid. A nice hint for the user would also be the changing of the mouse cursol while dragging. Yo could either use the screen object (which doesn't give you a whole lot of different corsor types) or get into some win32 api for that.
I know it's codable. I just wanted to know if anyone had done it before. I'm trying to avoid reinventing the proverbial wheel.
I have googled a few similar things out there but nothing that actually reorders a reassigns a sort column value.
I was toying with the idea of dropping the sort column and just using the autonumber. Whenever a change was made to the order the algo would copy all records from change point forward, delete them from the table and paste them back in. Then just allow everything to sort by the newly assigned autonumber as ussual.
Seems like a lot of overhead though. Some of these lists are several thousand records. I could imagine a 10 second delay everytime I move a record to another position near the top.
If anyone has seen or done anything like this let me know. I could probably even swing a few bucks for clean solution.