Solved Move selected to top of listbox

[oxicottin]

I have a multi select listbox which is a long list, so I wanted to move the selected items to the lists top, so I won't have to scroll through the list upon reopening the form.

 

[MarkK]

If you want to persist, between openings and closings of the form, the selected state of individual rows in a ListBox, you will need to persist the selected state of each row somewhere, somehow. Do you have such a structure in place?

 

[Minty]

The other option would be to store the selected items in a separate table and reload them on form opening.
I would use two listboxes, one you select from (the current list) and second one with the selected items in it, and have command buttons to move from one list to the other and vice versa. Something like

 

[MajP]

I assume you are storing the selected values in a table somewhere. If that is the case then your listbox query should be ordered by Selected then by other fields. In the afterupdate you would requery bringing selected values to the top.

 

[Pat Hartman]

You seem to be using the multi-value field. The RowSource query is what controls what you see. The problem is that you don't have access to that when you use this feature. If you want the list to work YOUR way, then get rid of the multi-value field. Add a child table and replace the listbox with a subform. That way you have control over how it looks and how it sorts and you don't need to write a lot of code as you would if you attempted to handle this with an unbound list box as the others are suggesting.

 

[oxicottin]

I am using a multi listbox that is unbound and saves the selections id's as a comma-delimited string in my table. @Pat Hartman I dont want to use a bunch of code like you said. I still want to save the selections as a comma-delimited string in my table though. Can you show me an example please?

Attached is what I have as of now towards the multiselect listbox

Thanks,

 

[MajP]

Not sure if I would do it this way, but this does what you are asking.

I have a table with selection IDs saved. 1,3,5...

I think you could do this without code because you should be able to get a query to sort the selected ones to the top, but without seeing your database here is a generic code to do that.
The listbox needs to be set as value list

On the forms current event
1. get the list of selections
2. get a query that pulls the selected values and write to the listbox
3. get a query of the not selected and write to the listbox
4. highlight the N selected ones

Public Sub CreateList()
  Dim strSql As String
  Dim rs As DAO.Recordset
  Dim I As Integer
  Dim NumberSelected
  
  ClearSelections
  If Not Me.Selections & "" = "" Then
    strSql = "SELECT tblSelections.SelectionID, tblSelections.Selection FROM tblSelections where SelectionID IN (" & Me.Selections & ") order by Selection"
    'Debug.Print strSql
    Set rs = CurrentDb.OpenRecordset(strSql)
    
    'do the selected ones first
    Do While Not rs.EOF
      Me.lstSelections.AddItem rs!selectionID & "; " & rs!Selection
      NumberSelected = I
      I = I + 1
      rs.MoveNext
    Loop
    'select
     strSql = "SELECT tblSelections.SelectionID, tblSelections.Selection FROM tblSelections where SelectionID NOT IN (" & Me.Selections & ") order by Selection"
    
    Set rs = CurrentDb.OpenRecordset(strSql)
     Do While Not rs.EOF
      Me.lstSelections.AddItem rs!selectionID & "; " & rs!Selection
      I = I + 1
      rs.MoveNext
    Loop
  Else
    'add all items if none selected
    NumberSelected = -1
    strSql = "SELECT tblSelections.SelectionID, tblSelections.Selection FROM tblSelections order by Selection"
     Set rs = CurrentDb.OpenRecordset(strSql)
    Do While Not rs.EOF
      Me.lstSelections.AddItem rs!selectionID & "; " & rs!Selection
      I = I + 1
      rs.MoveNext
    Loop
  
  End If
 
  For I = 0 To NumberSelected
      Me.lstSelections.Selected(I) = True
  Next I

End Sub
Public Sub ClearSelections()
  Dim I As Integer
  For I = Me.lstSelections.ListCount - 1 To 0 Step -1
     
    lstSelections.RemoveItem (I)
  Next I
End Sub
Private Sub lstSelections_AfterUpdate()
  Dim I As Integer
  Dim idx As Variant
  Me.Selections = Null
  For I = 0 To Me.lstSelections.ItemsSelected.Count - 1
    idx = Me.lstSelections.ItemsSelected(I)
    If Me.Selections & "" = "" Then
      Me.Selections = Me.lstSelections.ItemData(idx)
    Else
      Me.Selections = Me.Selections & "," & Me.lstSelections.ItemData(idx)
    End If
  Next I
  Me.Selections.SetFocus
  Me.Selections.Value = Me.Selections.Text
  CreateList
End Sub

 

[arnelgp]

I have a multi select listbox which is a long list, so I wanted to move the selected items to the lists top, so I won't have to scroll through the list upon reopening the form.

..so you only want it to be ordered (ordered by selected first, then the rest) when the form opens.
you can change recordsource on the current event of your form.

Private Sub Form_Current()
'Uses module mod_MultiListbox

    Call ClearListbox(Me.lstDefects)  'Clear the listbox
    
    If Not IsNull(Me.SelectedIDs) Then
        
        '/ arnelgp
        ' change recordsource
        ' so that the selected are first on the list
        '
        Dim sql As String
        sql = "SELECT tbl_ProductDefects.DefectID, tbl_ProductDefects.Defect, 1 As Priority " & _
                "FROM tbl_ProductDefects " & _
                "WHERE DefectID IN (" & Me.SelectedIDs & ") " & _
                "UNION " & _
                "SELECT tbl_ProductDefects.DefectID, tbl_ProductDefects.Defect, 2 As Priority " & _
                "FROM tbl_ProductDefects " & _
                "WHERE DefectID NOT IN (" & Me.SelectedIDs & ") " & _
                "ORDER BY 3, 1;"
                
        Me.lstDefects.RowSource = sql
        '
        '/ end of code
        
        Call SelectLBX(Me.lstDefects, Me.SelectedIDs)  'Gathers data for listbox
        DoListboxStuff  'Call function
        
    End If
    
End Sub

 

[Eugene-LS]

so I wanted to move the selected items to the lists top

Private Function GetListValues() As Variant
Dim sVal$, idx%
    For idx = 0 To Me.lstDefects.ListCount - 1
        If Me.lstDefects.Selected(idx) = True Then
            sVal = sVal & csDelimeter & Me.lstDefects.ItemData(idx)
        End If
    Next idx
    
    If Len(sVal) > Len(csDelimeter) Then
        GetListValues = Mid(sVal, Len(csDelimeter) + 1)
    End If
End Function

Private Sub SetItemsSelected(vArr As Variant)
Dim idx%, iVal%
' set selections from array
    For iVal = 0 To UBound(vArr)
        For idx = 0 To Me.lstDefects.ListCount - 1
            If Me.lstDefects.ItemData(idx) = vArr(iVal) Then
                Me.lstDefects.Selected(idx) = True
             End If
        Next idx
    Next iVal
End Sub

Private Sub DoListboxStuff(Optional blnNoMsg As Boolean)
Dim sSQL$, sVal$, idx%, iVal%, vArr As Variant

    
    If Me.lstDefects.ItemsSelected.Count = 0 Then
        Me.lstDefects.RowSource = csDefaultRowSource
        If blnNoMsg = False Then _
            MsgBox "There are no selected items!", vbExclamation
        Exit Sub
    End If
    
    For idx = 0 To Me.lstDefects.ListCount - 1
        If Me.lstDefects.Selected(idx) = True Then
            sVal = sVal & csDelimeter & Me.lstDefects.ItemData(idx)
        End If
    Next idx
    
    sVal = GetListValues & ""
    vArr = Split(sVal, csDelimeter) ' save selection in array

' new RowSource for lstDefects:
    sSQL = "SELECT DefectID, Defect FROM" & vbCrLf & _
        "   (SELECT * FROM tbl_ProductDefects ORDER BY Defect) as Q01" & vbCrLf & _
        "   WHERE DefectID IN (" & sVal & ")" & vbCrLf & _
        "UNION ALL" & vbCrLf & _
        "SELECT DefectID, Defect FROM" & vbCrLf & _
        "   (SELECT * FROM tbl_ProductDefects ORDER BY Defect) as Q01" & vbCrLf & _
        "   WHERE DefectID NOT IN (" & sVal & ")"
    'Debug.Print sSQL
    Me.lstDefects.RowSource = sSQL
    
    SetItemsSelected vArr
End Sub

Private Sub Form_Current()
Dim vArr As Variant
    If Not IsNull(Me.SelectedIDs) Then
        vArr = Split(Me.SelectedIDs, csDelimeter)
        SetItemsSelected vArr
        DoListboxStuff True 'Call function
    End If
End Sub

 

[Pat Hartman]

I am using a multi listbox that is unbound and saves the selections id's as a comma-delimited string in my table.

The NO code solution is a many-side table and a subform. This also normalizes your schema and makes other things easier and also no or little code.

OK, the solution isn't NO code but it's 8 lines since I added error trapping which is 6 of the 8. So to make the sample work, technically you need two lines of code. The Hold subform needs to requery the defects subform each time you click on a new line. The defects subform, needs to manually store the FK in its before Insert event to keep the tables linked. What you have is a many-many relationship and that require three tables. Hold, Defect, HoldDefects. The third table is referred to as a junction table. I also added RI which is required in every sound database.

What you end up with is a normalized schema that will be easy to work with going forward. Also because you are using a subform, the subform doesn't list the unused defects. It only lists the used ones and they are sorted, although not immediately.

The hold subform is bound to a query that limits selections to the selected Hold.

 

[oxicottin]

Thanks everyone for the examples, ill play with them when I get back to work after the weekend

 

[Pat Hartman]

While we all appreciate likes, liking every post isn't necessary and it doesn't help anyone who finds this thread later. It is far better to be specific in your thanks and identify which solution you decided to use whether it is one suggested by us or a composite of other ideas.

Several actual experts responded to your question but much to my surprise, I was the only one who mentioned the design flaw and offered a normalized solution (probably because it's Saturday and they aren't working). Everyone else stuck to the "how do I do this bad thing" answer. And you got at least one "good" answer. But as with all Band-Aids used to fix inferior designs, this one requires code. Code that most novices couldn't conceive of and probably won't understand either. The solution based on the normalized tables and which properly implements a m-m relationship REQUIRES only 2 lines of code and two queries with where clauses. The other 6 lines are for validation to protect a user who makes a mistake. You will also find that moving on to other tasks will be simplified by the properly normalized schema and allow you to use simple joins rather than complex code.

Here's a more complete m-m solution so you can see other ways in which this model is used.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk