Hi All
I need to be able to move items up and down in a listbox
Thanks

Brendan Bartley

__________________
Brendan Bartley

If the rowsource of the list box is a table, you could conceivably create queries that sort it differently, then make the different queries the rowsource when you want the sort order to be different for each situation.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I think you may have Misinterpret what I am looking for.
I need to move items in a listbox up and down as required by arrow keys
Thanks

Brendan Bartley

That might be easier. If you select an item and then click an arrow key, you can take a snapshot of the listbox in an array, then take the selectedIndex item and subtract 1 or add 1 and rebuild the array and post it back to the listbox. Do you have code so far?

I am not sure if that is how top Access experts would do it. Perhaps one will post.


mmcdonal

Oh, I should mention that you could put the results in a table, and then change the sort order or sort values and requery the listbox as SerranoG suggests. He is a top Access expert. =)

mmcdonal

The Listbox will have items added to them each hour and I need to priorities these depending on their importance importance I have no code as yet to move them up and down the listbox

Brendan Bartley

It sounds like they will have a ranking based on importance. Is that correct? If you tie that ranking to a listbox index schema, then that could work. You rank them once, and then re-rank them using the same mechanism. This gets back to the table solution. Have two columns: RankID, ItemName. RankID would be a number field, sort on RankID. Your processes would be to change the RankID and cascade that change through the rest of the table.

Ex:

RankID ItemName
0 Sam
1 Dave
2 Debbie

The process of adding Joe and making him top rank, would involve adding Joe to the table, settig his rank to 0, and then adding 1 to every record where RanikID >= 0, without acting on Joe.

Then sort the list box by the RankID, and hide that column. It will correspond to the list box index, so you can use the listbox index where needed.

Does that give you any ideas?


mmcdonal

Oh, heck no! But thanks for your vote of confidence!

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Hiya again,

I created a table with two columns, ListID and ListItem. ListID is a number field, indexed but duplicates are allowed. I used this as the source of a list box, which holds Column(0) = ListID, Column(1) = ListItem, Column(0) hidden.

I added a button to add items to the table so:

'This adds an item to the list table with the next PK

Dim sSQL As String
Dim iIndex As Integer
Dim sString As String

'Take the new text value to show up in the list.
If IsNull(Me.Text2) Or Me.Text2 = "" Then
    MsgBox "Please type something first.", vbCritical
    Exit Sub
Else
    sString = Me.Text2
End If

'Take the next number
iIndex = Me.List0.ListCount

'Build the SQL string to insert the new value
sSQL = "INSERT INTO ListSource(ListID, ListItem) " & _
        "VALUES(" & iIndex & ", '" & sString & "')"

'Turn off Access Query wanrings, insert and turn back on
DoCmd.SetWarnings False
DoCmd.RunSQL (sSQL)
DoCmd.SetWarnings True

'Requery the list so the new value shows up.
Me.List0.Requery


Then I created a button to move items up in the list so:

Dim iItem As Integer
Dim iUp As Integer
Dim iListID As Integer
Dim sListItem As String
Dim sSQL As String
Dim rs As ADODB.Recordset

'This is the ListID number from the table as well
'This will be reduced by one
iItem = Me.List0.Value
sListItem = Me.List0.Column(1)

'This is the item's new number in the list table
iUp = iItem - 1

sSQL = "SELECT * FROM ListSource"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Find iItem and iUp and change
rs.MoveFirst
Do Until rs.EOF
    If rs("ListID") = iUp Then
        rs("ListID") = iItem
        rs.Update
    End If
rs.MoveNext
Loop

rs.MoveFirst
Do Until rs.EOF
    If rs("ListItem") = sListItem Then
        rs("ListID") = iUp
        rs.Update
    End If
rs.MoveNext
Loop

'Requery the list so the new value shows up.
Me.List0.Requery
Me.Command4.SetFocus

This code works. I guess you can figure out how to make the items go down in the list. This only moves them up a row at a time.

Did that help?

Sorry I used the INSERT statement. As most Access experts will tell you, you should always select all the records in the table just to insert a single record. (Not a dig at you Greg, at another poster who recommends selecting all 10000000 records just to insert a single record - recommended by top Access experts.)
:D

mmcdonal

Well I am amazed. I didn't realize that we had Top Access Experts that are 5 years old. My grandson who is 7 doesn't act like mmcdonell when he can't get his own way. Obviously mmcdonell, whilst being very clever and extremely well versed in Access development obvoiusly cannot take criticism. It is a shame when someone who obviously has such intelligance has to continually make reference to this in his psotings.

My suggestion for that particular instance was to help a fellow forum member who wanted a quick easy way to build an insert into an new table just created. He appeared to a be a realtively new user of access and wanted him to see the the usefulness of recordsets, whether they be in DAO or ADO. I said at the start of that suggestion that it was "In my opinion". Even our 5 year old superstar admits himself that he cannot use DAO only ADO.

The main reason I posted that comment to you mmcdonnel, was the way in which you dismissed by effort, as if your a god, well let me tell you, your not. Many of your suggestions are, in your own words, ugly. Perhaps if you has suggested an alternative without making me as the poster of the comment feel small, we would not being having this disagreement in the forum.

I suggest you take time to read other peoples suggestions before dismissing them and learn how to respond.

I apologize to all you guys out there in the forum that has had to witness this small spat and I would also like to say, that I have learnt many new tips and tricks from - - - mmcondell and hopefully will continue to do so. I will also conitnue to post replies, but please mmcondell, grow up, its old hat now.