Full Version: Using a Multiselect List Box to find records
UtterAccess Forums > Microsoft® Access > Access Forms
RedlightG20
Hi!
I have a form which has a list box containing a large number of records (hundreds, soon thousands). The list box is mainly for selecting specific records to view information for through a subform. However, it also needs to be multi-select, so users can select multiple records and copy them to another list box (sort of like a temporary repository for handling later).
I have the list box setup in Extended multiselect mode. I have it coded so that it will pick out the list box row which has the focus (even if others are selected). The problem I'm having is, after selecting one or multiple records, when the AfterUpdate code fires to find a matching record and display its properties in the subform, the focus is taken away from the list box, and all of the user's selections are lost.
THere is the AfterUpdate code:
CODE
Private Sub lstNames_AfterUpdate()
    ' Find the record that matches the control.
    Dim objRSC As Object
    
    Set objRSC = Me.Recordset.Clone
    
    strSysName = Me.lstNames.Column(1, Me.lstNames.ListIndex)
    objRSC.FindFirst "strName = '" & strSysName & "'"
    If Not objRSC.EOF Then Me.Bookmark = objRSC.Bookmark
End Sub

When the last line fires (setting the bookmark), thats when the selections in the list box are cleared out.
Is there perhaps another way to set the bookmark so that focus isn't taken away from the list box (thus holding onto the current selections)?
Thanks!
Bob_L
You can't use a multi-select listbox to do what you are doing. It is going to go find ONE thing. You need to remove this code if you are going to use the listbox in multi-select mode and you won't go to the record on the form like this. You can only go to one record at a time on the form you are on, but you can filter using it. Is that what you would rather do?
fkegley
My suggestion would be to join together the PKs of the ones selected in the list box into a delimited string value that is placed in a hidden text box. Then a query can use the values in the list box as criteria to fetch the desired ones into another form that is then opened.
I have attached my favorite way of doing this.
RedlightG20
Hi guys, thanks for the responses.

I just made a workaround that seems to work exactly how I like it. Now I can use my multiselect listbox to display a single record in a subform without losing the selections I made in the listbox when the listbox loses focus and the subform is updated with the latest results.

But I'll try to explain the issue a little better in case it helps someone in the same boat. I was only trying to bring up the data for only one record from the listbox. So, even if I select 20 records at once, the most recently chosen record is the one I want to display information for in the subform.

But what was happening was, I selected a few records, then in my subform, the correct record is displayed (the most recently selected item from the listbox)... then the focus shifts away from the listbox and all of my selections were wiped out.

Pretty simple solution... I saved all of the listbox selections prior to updating the bookmark. When the bookmark is updated, and the subform is as well, the selections in the listbox are removed, and I simply highlight the listbox again.

Here's the code:
CODE
Private Sub lstNames_AfterUpdate()
nbsp;   ' Find the record that matches the control.
    Dim objRSC As Recordset
    Set objRSC = Me.Recordset.Clone
    
    strSysName = Me.lstNames.Column(1, Me.lstNames.ListIndex)
    objRSC.FindFirst "strName = '" & strSysName & "'"
    
    For Each varListItem In Me.lstNames.ItemsSelected
        arrValues = arrValues & "," & varListItem
    Next varListItem
    
    Me.Bookmark = objRSC.Bookmark
    
    arrValues = Split(arrValues, ",")
    For Each varListItem In arrValues
        If varListItem <> "" Then
            Me.lstNames.Selected(varListItem) = True
        End If
    Next  
End Sub


Anyway, thanks for your assistance guys! Cheers!
Bob_L
Well, glad you got something working to your satisfaction thumbup.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.