Listbox ListIndex - List Box Move Item in Microsoft Access 3


	Back to List Box Move Item 3 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Listbox ListIndex

Gary Becker

2 years ago

I'm puzzled by the listbox index. I have a table with vehicles and the table has a field SortOrder. Some of the vehicles have been sold. The row source for my ListBox is a Query where the DisposedDate Is Null and Order BY [SortOrder]. So, some of the underlying table's records are not included in the listbox. I thought that the listbox index would begin with 0 and be numbered sequentially, i.e. 0, 1, 2, 3, and so on. Instead, the Listbox.listindex appears to be the VehicleID--the Autonumber from the table. I'm wanting to move records up & down in the list box by adjusting their [SortOrder], but because all of the records from the underlying table are not in the listbox, the [SortOrder] in the ListBox has missing numbers that are assigned to the vehicles that have been sold. I was thinking that I could use the listbox index to get the [SortOrder] numbers of the records that are being swapped and update the [SortOrder] that way. So after all that, my original question is about ListBox.listindex and why it isn't 0, 1, 2, 3 and so on.

	Kevin Robertson @Reply 2 years ago
The ListIndex of a List Box does start at 0. The ID from the Table/Query is usually the bound column which is the VALUE of the List Box.

	Richard Rost @Reply 2 years ago
If you have a BOUND list box that's pulling table data, you don't really ever need the ListIndex property because, like Kevin said, you can refer to the ID. ListIndex is pretty much only ever used for UNBOUND boxes where that's how you determine which item is selected.

Gary Becker OP @Reply

2 years ago

I am using the following code. My table actually has three different sort columns. I use a SQL statement to set the Listbox rowsource with the appropriate sort field in column(1).  Since the records in the ListBox are filtered, and some SortOrder numbers are missing, I used this code to move the record up and swap the SortOrder numbers even though the numbers themselves may not be sequential.  That is why I think I need the ListBox index to get the SortOrder values, because the ListBox.index is sequential. So far, it seems to work. Sometimes I discover bugs.

Private Sub UpBtn_Click()

    Dim ID As Long
    Dim T As Long, B As Long

    'check for no selection
    If IsNull(ListBox) Then Beep: Exit Sub

    'check for first item
    If ListBox.Column(1) = 0 Then Beep: Exit Sub

    'get the id of the previous item
    ID = ListBox.Column(0, ListBox.ListIndex - 1)
    B = ListBox.Column(1) 'Is the current ListBox selection
    T = ListBox.Column(1, ListBox.ListIndex - 1) 'Is the row ABOVE the current selection

    'swap records
    If MySortOrder = "King" Then
        CurrentDb.Execute "UPDATE tblVehicleList SET SortOrder =" & [T] & " Where VehicleID=" & ListBox
        CurrentDb.Execute "UPDATE tblVehicleList SET SortOrder =" & [B] & " Where VehicleID=" & ID

    ElseIf MySortOrder = "County" Then
        CurrentDb.Execute "UPDATE tblVehicleList SET HCOrder =" & [T] & " Where VehicleID=" & ListBox
        CurrentDb.Execute "UPDATE tblVehicleList SET HCOrder =" & [B] & " Where VehicleID=" & ID

    ElseIf MySortOrder = "IMA" Then
        CurrentDb.Execute "UPDATE tblVehicleList SET IMAOrder =" & [T] & " Where VehicleID=" & ListBox
        CurrentDb.Execute "UPDATE tblVehicleList SET IMAOrder =" & [B] & " Where VehicleID=" & ID

    End If

    'requery the box
    ListBox.Requery

This thread is now CLOSED. If you wish to comment, start a NEW discussion in List Box Move Item 3.

Next Unseen