Move Up/Down in List Box ?

  • Thread starter Thread starter slimshady
  • Start date Start date
S

slimshady

I'm trying to make a very simple To Do list with a list box that lets
me move items up or down in the list. Can anyone show me how to do
this in Access 2007. It appears to be a LOT harder than it looks. I
currently have a table with a Task field and Priority field with
integers and the list box sorts descending by Priority.

Thanks !
 
if you have the integers being changed to change the order....and the listbox
is based on a query that has a sort by those integers; then just
refresh/requery the listbox and it will reflect the new order
 
Are you using a ValueList for the Record Source Type or Table/Query?

I almost never use value lists, and this is one time where using a table is
definately easier. Assuming you have a table (tbl_ToDoList) with fields
(Action and Priority).

I generally create two command buttons (cmd_Up and cmd_Down) which call a
subroutine and pass it a value. The key to changing the priority values is
noting that you only need to change two values, and that they are basically
exchanging their values. So if you want to change 4->3 and 3->4, then you
subtract the current priority from the sum of the two priorities, and you
get the sum of their two priorities by taking 2 * the number of the selected
priority and adding the direction you want to move (-1 or 1) to that value.
So to move an item from 3 to 4, you take 2 * 3 + 1 = 7 and subtract 3 from
it. It works the same for going from 4 to 3, 4 * 2 - 1 = 7 subtract 4 = 3.

The subroutine looks something like (this is untested as I'm not on a
computer with one of my apps that performs this function):

Private Sub ListSort(lst as listbox, Direction as integer)

'Accepts passed values identifying the listbox and a direction
'(-1 to move up, 1 to move down)
'Assumes that the user knows the name of the table used by the listbox,
'that the priority numbers in the Priority field are sequential, with
no gaps,
'and that the Priority field is the BoundColumn in the list.

Dim strSQL as string
Dim lngPriority as long

lngPriority = lst.Value
strSQL = "UPDATE tbl_ToDoList " _
& "SET [Priority] = " & 2 * lngPriority + Direction & " -
[Priority] "
& "WHERE [Priority] BETWEEN " & lngPriority _
& " AND " & lngPriority + Direction
'This should translate to something like:
'UPDATE tbl_ToDoList SET [Priority] = 7 - [Priority] WHERE [Priority]
BETWEEN 3 and 4
Currentdb.Execute strsql, dbFailOnError

lst.requery
lst.Value = lngPriority + Direction

'I usually include these two lines in the list boxes click event as
well,
'so the user cannot even select the Up button if the first item in the
list is selected
'The only difference is that in the lists click event I use me.cmd_Up
instead of
lst.parent.form.cmd_UP.Enabled = lst.Value > 1
lst.parent.form.cmd_Down.Enabled = lst.Value < lst.ListCount

End Sub

HTH
Dale
 
I posted this in reply to a message in the general public.access newsgroup.
It's virtually the same question as yours. Someone wanted help with a
survey application where users could move items in a list box up or down to
rank them by preference.

=====================

Here's one way to do it. I'm sure there are other ways, some of which will
be easier and more efficient.

Start with a table to hold your option and its rank. Rank should be single
or decimal data type (you'll need fractions).

Put a list box on your form bound to the table. Your rowsource would be
something like "SELECT Option from tblOptions ORDER BY Rank".

Put two command buttons next to the list box, one to move an option up, the
other to move it down. When the user clicks the button, open a recordset,
move to the selected item in the listbox, and increase or decrease it's
rank. In the code below, I am decreasing the rank by 1.5. This would
effectively move the option upward in preference. You have to
increase/decrease the rank by enough to make the option lower/higher than
the next value.

Dim db as database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM MyTable", dbOpenDynaset)

rs.FindFirst "Option = '" & Me!MyListBox & "'" 'I'm assuming option
is text
rs.Edit
rs!Rank = rs!Rank - 1.5
rs.Update
rs.Close

The next step will be to resequence your options so they are again ranked 1
to 5. This will allow the user to change the rankings as many times as
he/she needs to.

Dim x as integer
Set rs = db.OpenRecordset("SELECT * FROM MyTable ORDER BY Rank",
dbOpenDynaset)
Do While Not rs.EOF
x = x + 1
rs.Edit
rs!Rank = x
rs.Update

rs.MoveNext
Loop
rs.Close

Set rs = Nothing
Set db = Nothing

Finally, requery the listbox to redisplay the options in the correct order.

Hope this helps.


Jane said:
Hi all,

I am trying to create a survey with access, and I have a question in it
which states " Put these 5 options in order of importance" then I have 5
options, but how can I do this in access?

Any help would be greatly appreciated

Thanks
Click to expand...


=============
 
Back
Top