Move records in a dataset

[Guest]

I can probably build this myself, but thought there may be some code
somewhere to enable me to move records in a dataset form up or down. For
example, I might want to move the record on line 5 up to line 3 and have
current lines 3 and 4 moved down. Think the function in setting references
in VBA.
Does anyone know where I can get my hands on some code samples?

 

[Tim Ferguson]

For
example, I might want to move the record on line 5 up to line 3 and
have current lines 3 and 4 moved down. Think the function in setting
references in VBA.

You need to pick a ORDER BY criterion and set it; either in the underlying
SQL or in a .Filter property. I don't think there is any method of randomly
sorting records: it's not a very databasey sort of thing to do.

You might find that this job is easier in Excel. It would take maybe half a
dozen lines of VBA to get the data from a Jet table into a spreadsheet, and
a couple more to do the swapping.

Hope that helps


Tim F

 

[Guest]

Thanks Tim. I was looking to add a field to the table which would be a sort
order, and when an item is moved, renumber the sort order. I am sure it can
be done but it is a tedious piece of code to write and I thought someone may
have already done it.

 

[Tim Ferguson]

Thanks Tim. I was looking to add a field to the table which would be
a sort order, and when an item is moved, renumber the sort order. I
am sure it can be done but it is a tedious piece of code to write and
I thought someone may have already done it.

It's not at all tedious code: it's about two or three minutes in the GUI to
add another field. It's incredibly tedious for the users to renumber all
the fields themselves, even though all they have to do in the end is to
click "Sort on field" or whatever.

Is there really no field, combination of fields, or calcuation on
combinations of fields that would predict the order?

The suggestion to go with an alternative UI would be to make it easier for
your users, not for you!!

All tbe best


Tim F

 

[Guest]

What I was intending to do was to add a sort field and update it
programitacally when a "Move Up" or "Move Down" button was selected. If
record 5 of 10 was selected, and the "Move Up" button was clicked, records 4,
6, 7, 8, 9, and 10 have their sort field incremented by 1, and record 5 is
reduced by 1.

 

[Tim Ferguson]

If
record 5 of 10 was selected, and the "Move Up" button was clicked,
records 4, 6, 7, 8, 9, and 10 have their sort field incremented by 1,
and record 5 is reduced by 1.

I don't think your example quite makes sense, but this is the sort of
thing:

update mytable
set sortfield = -1
where sortfield = 5

update mytable
set sortfield = sortfield + 1
where sortfield > 5

update mytable
set sortfield = 5
where sortfield = -1

I still think I'd do it in Excel...

Best wishes


Tim F