Move items up and down in a datasheet sub-form

[Snowflake68]

I would like to reproduce the functionality that you get in some windows forms which allow you to move items up and down a list via arrow buttons.

I have a subform with a datasheet which the user can already sort by a chosen category (I have already built this functionality which works well). However they now want to be able to sort the list by moving a record up or down by selecting the record and move it within the datasheet.

The list is currently sorted by a hidden column with a numerical value and I would like to be able to increment this number with each click of the down button or decrement it with each click of the up button. But I would then need to increment or decrement all of the other records accordingly.

Also as the form is a datasheet I am not able to add a button so it probably would need to be two additional columns with OnClick events on an embedded image (no idea if this will work though).

Not really sure how to go about this so any suggestions would be very much appreciated.

 

[Ranman256]

you cant in datasheet.
it might be possible in a list box, but that's a lot of work for a 'gimmick'.

 

[CJ_London]

or change your datasheet to continuous form - can be made to look like a datasheet but with buttons. Or make your datasheet a subform to a single form to hold the buttons.

Principle would be

use selects a row in the datasheet
user then clicks the up or down button on the main form
vba modifies the algorithm for the sort column and resorts the datasheet

As Ranman says - a lot of work.

 

[jdraw]

As others have said, it's considerable work for "what exactly".
I have done it with Listbox.

 

[Snowflake68]

Thanks everyone for your input, it does seem a lot of work so may end up putting the customer off from doing this. The only other way is to allow them to just type in the number in the order by column and refresh it on an after update. Not very user friendly but a lot less work.

 

[MarkK]

This doesn't seem that hard.
1) Select the row (record the ID on current)
2) click a button to change a value in that row (move it up or down) (add or subtract 1.5, say)
3) re-sort and re-number all the rows from scratch (so your sorting scheme doesn't get out of hand)
4) requery the form
5) re-select the row from 1)

 

[CJ_London]

add or subtract 1.5, say

problem is if the user clicks twice that become 3 which then 'clashes' with another row.

Also need code to ignore trying to move the top row up or the bottom row down

 

[sneuberg]

I gave this a shot in the attached database. I don't think you can use an image for this so I used a textboxes with a Wingding3 characters for the up and down arrows. Please check this out and see if the concept would work for you.

 

[arnelgp]

just for fun.
open moveupdownmain form.

 

[Snowflake68]

I have been looking at this again this afternoon and have a possible solution. The only thing I need to get working is the renumbering. I have added two columns one called 'Up' and the other called 'Down' both have default values which are in the form of up and down arrows (just used the ASCII characters for the arrows). So I then have an OnClick event updating the value in the column that contains the number for the sort order and I can get it to add or subtract 1 from the value for the row I want to move but how do I renumber the other rows? I am using the SetProperty action in a macro to do this and then requery the object but how now need to find a way of updating all of the other values (some will need to increase and the others will need to decrease).

 

[Snowflake68]

just for fun.
open moveupdownmain form.

Apologies I completely missed your input. Thanks for this it looks exactly what I need but just got to figure out a way to get it to work with my existing code.

Thanks so much for your help it is very much appreciated.

I let you know how I get on.

Screenshot attached showing what I have so far.

 

[sneuberg]

So I then have an OnClick event updating the value in the column that contains the number for the sort order and I can get it to add or subtract 1 from the value for the row I want to move but how do I renumber the other rows? .

I think any approach renumbers the other rows would really slow things down and is not necessary. In my approach you just swap the sorting numbers.

I suggest you look at my and arnelgp's implementations for ideas.

 

[Snowflake68]

I think any approach renumbers the other rows would really slow things down and is not necessary. In my approach you just swap the sorting numbers.

I suggest you look at my and arnelgp's implementations for ideas.

thanks, I completely understand what you are saying however I do need to retain the sort order functionality that I already have which enables the user to sort the list by different columns which they can chose and the forms default sort order uses just one column to sort it after copying and pasting everything back into the form this sort order is also used to export the list in the user defined sort order so I do need to do the renumbering.

I will look at the code that arnelgp's has provided and see how I can incorporate it so that my application can use both. Not sure how easy its going to be but I will give it a try.

Thanks again for your input.

 

[jdraw]

Just saw others have provided a sample.
Here's one I did.

 

[arnelgp]

a simplier way to do this on listbox.

 

[jdraw]

Nice! Thanks for sharing

 

[gemma-the-husky]

well you can do it with a datasheet, surely.

The datasheet will be sorted by your putative sort order. In order to get the effect you want, you need to use real numbers for the sort order, to avoid the clashes, and you do not need to renumber every item.

So given records with sortorder 4.0, 5.0, 6.0 - in order to move the bottom one up a place the sort order needs to be made less than the one before, but more than the one before that.

So (eg) change the sort order of item 6.0 to be 4.5 and you get what you want.

you now have sort orders, 4.0, 4.5 and 5.0. If you now want the top one to become the second one, you do the same process, and end up with 4.5, 4.75 and 5.0

 

[arnelgp]

hello, my i reply on your code.
sample 4.0, 4.5, 5.0
1. move top to second
4.5, 4.75, 5.0
2. again move top to second
4.75, 4.85?, 5.0
3. do it again
4.85?, 4.95?, 5.0
4. do it again
4.95, 5.0?, 5.0
5. if you do it again it will be on the third row.

 

[sneuberg]

hello, my i reply on your code.
sample 4.0, 4.5, 5.0
1. move top to second
4.5, 4.75, 5.0
2. again move top to second
4.75, 4.85?, 5.0
3. do it again
4.85?, 4.95?, 5.0
4. do it again
4.95, 5.0?, 5.0
5. if you do it again it will be on the third row.

Now quite that fast If you run the code below you will find that it takes 50 times before that happens using doubles. Of course its not good if it ever happens

Public Sub InBetween()

Dim Second As Double
Dim Third As Double
Dim Count As Long
Second = 4.5
Third = 5#
Count = 0

Do While Second <> Third
    Second = (Second + Third) / 2
    Count = Count + 1
Loop
Debug.Print Count

End Sub

 

[Snowflake68]

a simplier way to do this on listbox.

Thanks for this however I really need to keep it as a datasheet as there is so much more functionality involved within the application that would need major work to change it. Thanks anyway

I really just need to find a way to renumber the records in the datasheet so I will keep trying.