468,272 Members | 2,167 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,272 developers. It's quick & easy.

changing order of items in a list box

This is probably a very simple problem, but I can't figure out the
coding for it.

I have a list box that displays one of my tables. I'd the user to be
able to reorder the items in the list box as they please. I've created
an up and down button but I can't figure out the coding for me to do
so. Can anybody help me out with this? Thanks

Wally

Mar 23 '06 #1
4 13660
Add a number field to the table to keep track of the order you want the
items in. Include this field in the query used for the Row Source of the
listbox. Sort on the number field. When you move an item up or down, swap
its number with the one above or below it then requery the listbox.

The field needs to be in the query, but not the query's output if you don't
want it as another column in the listbox. To hide it from the query's
output, uncheck the Show box for that field.

--
Wayne Morgan
MS Access MVP
"Wally" <wa******@gmail.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...
This is probably a very simple problem, but I can't figure out the
coding for it.

I have a list box that displays one of my tables. I'd the user to be
able to reorder the items in the list box as they please. I've created
an up and down button but I can't figure out the coding for me to do
so. Can anybody help me out with this? Thanks

Wally

Mar 23 '06 #2
Wally wrote:
This is probably a very simple problem, but I can't figure out the
coding for it.

I have a list box that displays one of my tables. I'd the user to be
able to reorder the items in the list box as they please. I've created
an up and down button but I can't figure out the coding for me to do
so. Can anybody help me out with this? Thanks

Wally

You could create a dropdown of your columns (type them in). Call it
ComboSortBy for example. Default the value to whatever you have in the
current sort.

In the AfterUpdate event you could do something similar to
Dim strSQL As String
Dim intPos As Integer
strSQL = Me.ListBoxName.Rowsource
intPos = Instr(strSQL,"Order By")
strSQL = Left(strSQL,intPos-1) & " Order By " & Me.ComboSortBy
Me.ListBoxName.Rowsource = strSQL

If your Rowsource is not a sqlstatement but a query or table name you
will need to modify it. But you should have a general idea on how to do
that from the above code.
Mar 23 '06 #3
I already have a number field in my table. How exactly do you "swap"
numbers? This is the only problem I have a right now. Thanks

Wally

Mar 23 '06 #4
Open a recordset on the table and use a variable to hold the number
temporarily. The numbers need to be unique for each record.

Example (move up one spot), untested but should give the general idea:
Dim db As DAO.Database, rst As DAO.Recordset
Dim lngTemp As Long
Set db = CurrentDb
Set rst = db.OpenRecordset("TableName", dbOpenDynaset)
lngTemp = Me.lstMyListbox.Column(0) 'the column with the number field
With rst
.FindFirst "NumberField=" & lngTemp
.Edit
!NumberField = Me.lstMyListbox.Column(0, Me.lstMyListbox.ListIndex - 1)
.Update
.FindFirst "NumberField=" & Me.lstMyListbox.Column(0,
Me.lstMyListbox.ListIndex - 1)
.Edit
!NumberField = lngTemp
.Update
End With
rst.Close
Set rst = Nothing
Set db = Nothing

You will need to check to see if you are at the first or last row of the
listbox already. If you are, you will need to decide if you want to do
nothing or if you want it to "wrap" around (i.e. the first row move to the
last row if you try to move the first row up).

If the number field in the table has a Unique Index on it, you'll have to
supply a "dummy" number to the first item until you change the second item,
then go back and change the first one to the correct number. This number
field should NOT be your primary key field.

--
Wayne Morgan
MS Access MVP
"Wally" <wa******@gmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...
I already have a number field in my table. How exactly do you "swap"
numbers? This is the only problem I have a right now. Thanks

Wally

Mar 24 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by MickG | last post: by
7 posts views Thread by Lasse Vågsæther Karlsen | last post: by
6 posts views Thread by segue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.