Remembering multi-select list box selections

[rovral]

I have an unbound multi-select list box that is populated from a query that displays types of buildings. I have two check boxes on the form that change the items in the list box when one or the other is set to true, only one check box can be selected at a time. One check box, when selected, displays all types of buildings except for multi-family, while the other check box displays only multi-family buildings.

My problem is if the user selects items in the listbox and then toggles one of the check boxes, the listbox is reset because of the changing of the items available to select. The challenge is to remember what the user selected in the listbox prior to toggling either one of the check boxes. For the most part, my code works to handle this but it only works when one checkbox is toggled on or off and not either or checkbox. I end up with an error that says run-time error 9: subscript out of range in my ReStoreListSele ction function. I suspect this has to do with my array and upon debugging I discovered that the correct value is not being passed back to the listbox that I desire depending on which checkbox is selected. I can't figure out what I need to change in my code to make this happen.

I call a function from a module in the after update of each checkbox to handle this scenario. My code is as follows:

Function StoreListSelection(Lst As ListBox) As Boolean()
Lst = Forms![frmSearch]![lstType]
    Dim lngIndex As Long
    Dim blnTemp()  As Boolean
    
    ReDim blnTemp(Lst.ListCount - 1)
    
    For lngIndex = 0 To Lst.ListCount - 1
        blnTemp(lngIndex) = Lst.Selected(lngIndex)
    Next
    
    StoreListSelection = blnTemp
    
End Function

Sub ReStoreListSelection(Lst As ListBox, Selected() As Boolean)

    Dim lngIndex As Long
    
    For lngIndex = 0 To Lst.ListCount - 1
        Lst.Selected(lngIndex) = Selected(lngIndex)
    Next
    
End Sub

Public Function fMultiSort()
Dim strSQL As String
Dim Lst As ListBox
Dim chkMulti, chkLease As CheckBox
Dim blnSelectLease() As Boolean
Dim blnSelectMulti() As Boolean
  
'See if we have actual Records
If DCount("[Building_Type]", "tblBuilding_Type_List") = 0 Then Exit Function
    'Initialize list box
    Set Lst = Forms![frmSearch]![lstType]
    
   'Initialize check boxes
    Set chkMulti = Forms![frmSearch]![chkMulti]
    Set chkLease = Forms![frmSearch]![chkLease]
        
    If chkMulti = False Then
       strSQL = "SELECT DISTINCT [Building_Type] FROM tblBuilding_Type_list WHERE [Enable_Units] = False ORDER BY [Building_Type];"
       blnSelectLease = StoreListSelection(Lst)
       Lst.RowSource = strSQL

       If blnSelectLease(False) Then
          'do nothing
       Else
       ReStoreListSelection Lst, blnSelectLease
       End If
    Else
        If chkMulti = True Then
           strSQL = "SELECT DISTINCT [Building_Type] FROM tblBuilding_Type_list WHERE [Enable_Units] = True ORDER BY [Building_Type];"
           blnSelectMulti = StoreListSelection(Lst)
           Lst.RowSource = strSQL

           If blnSelectMulti(False) Then
              'do nothing
           Else
           ReStoreListSelection Lst, blnSelectMulti
           End If
        End If
    End If
End Function

Also here is the code for the AfterUpdate event of one of my checkboxes:

Private Sub chkMulti_AfterUpdate()
If chkMulti = True Then
   chkLease = False
   
   Call fMultiSort
   
   cmdLease_Single.Enabled = False
   cmdLease_Detail.Enabled = False
   cmdSingle.Enabled = True
   cmdSummaryDetail.Enabled = True
   sfrmSearch.Enabled = False
   sfrmSearch.Visible = False
   sfrmLease.Visible = False
   sfrmLease.Enabled = False
   sfrmMulti.Enabled = True
   sfrmMulti.Visible = True
  
   If sfrmMulti.Form.RecordSource = "" Then
    
      'Make the multi subform controls invisible
      sfrmMulti![txtLocation].Visible = False
      sfrmMulti![txtAddress].Visible = False
      sfrmMulti![txtType].Visible = False
      sfrmMulti![txtPrice].Visible = False
      sfrmMulti![txtDate].Visible = False
      sfrmMulti![txtUnits].Visible = False
      sfrmMulti![txtPrice_Unit].Visible = False
      sfrmMulti![txtGIM].Visible = False
      sfrmMulti![txtID].Visible = False
      sfrmMulti!cmdMore.Visible = False
    End If
        
    Else
        Call fMultiSort
        
        cmdSingle.Enabled = True
        cmdSummaryDetail.Enabled = True
        cmdLease_Single.Enabled = False
        cmdLease_Detail.Enabled = False
        sfrmSearch.Enabled = True
        sfrmSearch.Visible = True
        sfrmMulti.Enabled = False
        sfrmMulti.Visible = False
        sfrmLease.Visible = False
        sfrmLease.Enabled = False
End If
End Sub

Any help would be greatly appreciated.

[Mihail]

Your check boxes, because are checked alternatively, act as an option group. It is why I use the Option group feature. In fact you can have only one check box. When check the list box show something, when un-check the list box show other thing.

In the attachment you can see a solution based on Option Group feature.
The code is:

Option Compare Database
Option Explicit

Dim StoredMulti, StoredSingle 'This variables will store the selected items

Private Sub Form_Load()
    Select Case frmOption
        Case 1
            lstBuildings.RowSource = "q_Multi" 'Multi-family
        Case 2
            lstBuildings.RowSource = "q_Single" 'Single-family
    End Select
End Sub

Private Sub frmOption_BeforeUpdate(Cancel As Integer)
Dim i As Long
    Select Case frmOption.Value
        Case 1 'Multi
            Call StoreSelected(StoredSingle)
            lstBuildings.RowSource = "q_Multi" 'Change the list box
            Call RestoreSelection(StoredMulti)
        Case 2 'Single
            Call StoreSelected(StoredMulti)
            lstBuildings.RowSource = "q_Single" 'Change the list box
            Call RestoreSelection(StoredSingle)
    End Select
End Sub

Private Sub RestoreSelection(StoredIn)
On Error GoTo Ex
Dim i As Long
    For i = 0 To UBound(StoredIn)
        lstBuildings.Selected(StoredIn(i)) = True
    Next i

Ex:
End Sub

Private Sub StoreSelected(StoreIn)
Dim strStoreIn As String, i As Long
    strStoreIn = ""
    For i = 0 To lstBuildings.ListCount - 1
        If lstBuildings.Selected(i) Then
            strStoreIn = strStoreIn & i & ","
        End If
    Next i
    StoreIn = Null
    If strStoreIn <> "" Then
        strStoreIn = Left(strStoreIn, Len(strStoreIn) - 1)
        StoreIn = Split(strStoreIn, ",")
    End If
End Sub

[rovral]

Remembering multi-select list box selection

Thanks for your reply. That works pretty good. However, I didn't use an option group because I would like to be able to unselect both options. The form is actually a search form and when I click on the search button, different SQL is created depending on which check box is selected but different items appear in the listbox depending on which check box is selected like we already established. So what happens is if the multi-family check box is selected, the listbox displays multi-family buildings. If the lease check box is selected, regular buildings appear in the listbox but SQL is created on the search click event to show only those buildings with leases. If no checkbox is selected, the same regualr buildings still appear in the listbox, only the SQL that is generated in the search click event grabs buildings that have sold. All the SQL stuff works, just need to remember the selections if the user toggles back and forth between check boxes.

Any ideas?

[rovral]

I was thinking, what if I added a third option for building sales and make this the default? This may solve my problem.

[Mihail]

You don't say that in your first post.

Any way, if you decide to add the 3rd check box (and is the last) you can manage the situation using 3 radio buttons instead. I think is no problem for you to add a new button to my form and to add the necessary code.

On the other hand, you can use only your (two) check boxes and, every time one is changed, verify the status for the other one and built code as you need, based on the "template" I have give you.

Good luck !

[rovral]

It works great based on two buttons but when I add a third it doesn't work properly. Do I need to add a third variable such as StoredLease and then use some kind of if statement in the select case? Note that list box selections for lease and building will always be the same.

Here is mu code so far:

Dim StoredMulti, StoredBuilding, StoredLease
Dim strSQL, strSQL_Multi As String

Private Sub Form_Load()
    
strSQL = "SELECT DISTINCT [Building_Type] FROM tblBuilding_Type_list WHERE [Enable_Units] = False ORDER BY [Building_Type];"
strSQL_Multi = "SELECT DISTINCT [Building_Type] FROM tblBuilding_Type_list WHERE [Enable_Units] = True ORDER BY [Building_Type];"
    
frmOption.Value = Null
'    Select Case frmOption
'        Case 1
'            lstType.RowSource = strSQL
'        Case 2
'            lstType.RowSource = strSQL
'        Case 3
'            lstType.RowSource = strSQL_Multi
'    End Select
End Sub

Private Sub StoreSelected(StoreIn)
Dim strStoreIn As String, i As Long
    strStoreIn = ""
    For i = 0 To lstType.ListCount - 1
        If lstType.Selected(i) Then
            strStoreIn = strStoreIn & i & ","
        End If
    Next i
    StoreIn = Null
    If strStoreIn <> "" Then
        strStoreIn = Left(strStoreIn, Len(strStoreIn) - 1)
        StoreIn = Split(strStoreIn, ",")
    End If
End Sub

Private Sub RestoreSelection(StoredIn)
On Error GoTo Ex
Dim i As Long
    For i = 0 To UBound(StoredIn)
        lstType.Selected(StoredIn(i)) = True
    Next i

Ex:
End Sub

Private Sub frmOption_BeforeUpdate(Cancel As Integer)
Dim i As Long
    Select Case frmOption.Value
        Case 1 'Building Sales
            Call StoreSelected(StoredMulti)
            lstType.RowSource = strSQL
            Call RestoreSelection(StoredBuilding)
        Case 2 'Leases
            Call StoreSelected(StoredMulti)
            lstType.RowSource = strSQL
            Call RestoreSelection(StoredBuilding)
        Case 3 'Multi-Family
            Call StoreSelected(StoredBuilding)
            lstType.RowSource = strSQL_Multi
            Call RestoreSelection(StoredMulti)
    End Select
End Sub

[dsatino]

Without reading the code....

I think your best bet would be to use and unbound ComboBox instead of the check boxes. The advantages are that only one scenario can exist so you don't need code to turn off something else, your sql filter for the field is based on a single control, and it just takes up less space.

So for the Multi-Family scenario I would use a two column combo box called cboMF with the following properties:

rowsource: 1;All;2;Multi-Family;3;Single-Family
bound column: 1
column widths: 0;2"
Limit to List: Yes
Allow Edits: No


The user selection would be stored in a global multi-dimensional array we'll call arrUserSelect. The array will have 3 rows and 1 + [maximum user selections] columns. The additional column is to store the combobox value (this is for extensibility of this model).

Assuming the user can select up to 10 items (and assuming option base 0) you would dimension the array:

Dim arrUserSelect(2 ,10)

I would erase and then load the array with the combo values (in column 0) when the form opens just so they're there and always in the same order, but that's up to you.

In the BeforeUpdate event of the combobox you would call a sub that clears all the user selections for the combobox value out of the array (only the appropriate row and only columns 1-10), and loop through list box and capture the selections.

In the AfterUpdate event of the combobox you would call a sub that re-populates the list box and then call a sub that selects anything that exists in the array for that combobox value.


Now for the extensibility part I referred to earlier. Say you also wanted to add a filter for Rent/Own. You would add another combo box just like the first one with 'all' being it's own selectable value and the rowsource being:

1;All;2;Rent;3; Own

The array would be dimensioned arrUserSelect(8 ,10) because there are 9 combinations of user selections. The identifier in this scenario would be a concatenation of the two combo box values.

So a user selection of 'Rent' and 'Single-Family' would have the identifier of 23.

The before and after update events for both combo boxes would run the same subs. So you can add extra controls without modifying interactions to the storage array. Because the identifier is a concatenation, duplicates cannot arise.

[rovral]

Thanks, but I like the user being able to see the choices instead of selecting from a combo box. Besides I almost have it working except for the issue mentioned above and there isn't very much code involved. Also, I am terrible with arrays and your way would require more effort to get the arrays working. The arrays I have now work fine.

[dsatino]

Your call, of course, but the issue and coding mentioned above are caused by your methodology.

Fair warning though, the day will come when you stumble across your own code and wonder 'why would I do this to myself?'.

I've done it many times. :)

[rovral]

No doubt, but for the time being it makes sense and of course I don't foresee the need to change this in the future (I'm probably wrong) lol. I just don't want to spend a lot of time recoding this. Anyway, do you know how to incorporate my existing code to account for three options?

Thanks for your help.

[rovral]

I think in each case I need to account for the third option but I don't know what condition I need to make the if statement. If that is correct.

[Mihail]

This will work for 3 Option Buttons, (and can be modified for any number).

The code is (see also the attachment):

Option Compare Database
Option Explicit

Dim Stored_1, Stored_2, Stored_3
Dim WhereToStore As Long

Private Sub Form_Load()
    Select Case frmOption
        Case 1
            lstBuildings.RowSource = "q_1"
            WhereToStore = 1
        Case 2
            lstBuildings.RowSource = "q_2"
            WhereToStore = 2
        Case 3
            lstBuildings.RowSource = "q_3"
            WhereToStore = 3
    End Select
End Sub


Private Sub frmOption_BeforeUpdate(Cancel As Integer)
    Select Case WhereToStore
        Case 1
            Call StoreSelected(Stored_1)
        Case 2
            Call StoreSelected(Stored_2)
        Case 3
            Call StoreSelected(Stored_3)
    End Select
End Sub

Private Sub frmOption_AfterUpdate()
    Select Case frmOption.Value
        Case 1
            lstBuildings.RowSource = "q_1"
            Call RestoreSelection(Stored_1)
            WhereToStore = 1
        Case 2
            lstBuildings.RowSource = "q_2"
            Call RestoreSelection(Stored_2)
            WhereToStore = 2
        Case 3
            lstBuildings.RowSource = "q_3"
            Call RestoreSelection(Stored_3)
            WhereToStore = 3
    End Select
End Sub



Private Sub RestoreSelection(StoredIn)
On Error GoTo Ex
Dim i As Long
    For i = 0 To UBound(StoredIn)
        lstBuildings.Selected(StoredIn(i)) = True
    Next i

Ex:
End Sub

Private Sub StoreSelected(StoreIn)
Dim strStoreIn As String, i As Long
    strStoreIn = ""
    For i = 0 To lstBuildings.ListCount - 1
        If lstBuildings.Selected(i) Then
            strStoreIn = strStoreIn & i & ","
        End If
    Next i
    StoreIn = Null
    If strStoreIn <> "" Then
        strStoreIn = Left(strStoreIn, Len(strStoreIn) - 1)
        StoreIn = Split(strStoreIn, ",")
    End If
End Sub

[dsatino]

Since you're already getting some help, I don't want to muddy the waters. But here is an example of what I explained. It's slightly different in that it captures after each selection rather than on the change of the list boxes.

Also, some general advice....

Simplicity in design and code is the key. As you build your program out, it will inevitably grow in complexity. As you add functionality, you'll add controls and code. If you're foundation is complex, you'll need more complexity in the code that you build later.

Using your strategy vs. the example I gave, consider this with respect to the Multi-family filter user inputs:

Your way: 2 controls, 4 control states
My way: 1 control, 3 control states

Now consider your intended result of these controls:

1 filter, 3 possibilities

Your inputs should be explicitly aligned with the intended output.

Under your method you need code to handle the extra control state and you need to capture two control values to get a single result. Also, you have an implied user selection. In other words, if a user checks nothing, it's implied that they want everything. The main problem with this is that the user didn't necessarily choose that because sometimes your code controls the check boxes.

Under the method I used, there is no code to handle the control state, only one control value must be captured, and the user selection is always explicit. Hopefully you'll take this advice and use it next time.

Also, with respect to your code, try and make everything as modular and portable as possible. In other words, try to make the code generic and pass the specifics into it.

[rovral]

Thanks for this, I didn't want to recode my search form after many hours of getting it to work the way it is, but I am working on a similar scenario for a future project and I will definitely use this approach. It is pretty slick.

Thanks again.

[rovral]

Thanks immensely Mihail, everything works now.