By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Remembering multi-select list box selections

P: 28
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 ReStoreListSelection 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:

Expand|Select|Wrap|Line Numbers
  1. Function StoreListSelection(Lst As ListBox) As Boolean()
  2. Lst = Forms![frmSearch]![lstType]
  3.     Dim lngIndex As Long
  4.     Dim blnTemp()  As Boolean
  6.     ReDim blnTemp(Lst.ListCount - 1)
  8.     For lngIndex = 0 To Lst.ListCount - 1
  9.         blnTemp(lngIndex) = Lst.Selected(lngIndex)
  10.     Next
  12.     StoreListSelection = blnTemp
  14. End Function
  16. Sub ReStoreListSelection(Lst As ListBox, Selected() As Boolean)
  18.     Dim lngIndex As Long
  20.     For lngIndex = 0 To Lst.ListCount - 1
  21.         Lst.Selected(lngIndex) = Selected(lngIndex)
  22.     Next
  24. End Sub
  26. Public Function fMultiSort()
  27. Dim strSQL As String
  28. Dim Lst As ListBox
  29. Dim chkMulti, chkLease As CheckBox
  30. Dim blnSelectLease() As Boolean
  31. Dim blnSelectMulti() As Boolean
  33. 'See if we have actual Records
  34. If DCount("[Building_Type]", "tblBuilding_Type_List") = 0 Then Exit Function
  35.     'Initialize list box
  36.     Set Lst = Forms![frmSearch]![lstType]
  38.    'Initialize check boxes
  39.     Set chkMulti = Forms![frmSearch]![chkMulti]
  40.     Set chkLease = Forms![frmSearch]![chkLease]
  42.     If chkMulti = False Then
  43.        strSQL = "SELECT DISTINCT [Building_Type] FROM tblBuilding_Type_list WHERE [Enable_Units] = False ORDER BY [Building_Type];"
  44.        blnSelectLease = StoreListSelection(Lst)
  45.        Lst.RowSource = strSQL
  47.        If blnSelectLease(False) Then
  48.           'do nothing
  49.        Else
  50.        ReStoreListSelection Lst, blnSelectLease
  51.        End If
  52.     Else
  53.         If chkMulti = True Then
  54.            strSQL = "SELECT DISTINCT [Building_Type] FROM tblBuilding_Type_list WHERE [Enable_Units] = True ORDER BY [Building_Type];"
  55.            blnSelectMulti = StoreListSelection(Lst)
  56.            Lst.RowSource = strSQL
  58.            If blnSelectMulti(False) Then
  59.               'do nothing
  60.            Else
  61.            ReStoreListSelection Lst, blnSelectMulti
  62.            End If
  63.         End If
  64.     End If
  65. End Function
Also here is the code for the AfterUpdate event of one of my checkboxes:

Expand|Select|Wrap|Line Numbers
  1. Private Sub chkMulti_AfterUpdate()
  2. If chkMulti = True Then
  3.    chkLease = False
  5.    Call fMultiSort
  7.    cmdLease_Single.Enabled = False
  8.    cmdLease_Detail.Enabled = False
  9.    cmdSingle.Enabled = True
  10.    cmdSummaryDetail.Enabled = True
  11.    sfrmSearch.Enabled = False
  12.    sfrmSearch.Visible = False
  13.    sfrmLease.Visible = False
  14.    sfrmLease.Enabled = False
  15.    sfrmMulti.Enabled = True
  16.    sfrmMulti.Visible = True
  18.    If sfrmMulti.Form.RecordSource = "" Then
  20.       'Make the multi subform controls invisible
  21.       sfrmMulti![txtLocation].Visible = False
  22.       sfrmMulti![txtAddress].Visible = False
  23.       sfrmMulti![txtType].Visible = False
  24.       sfrmMulti![txtPrice].Visible = False
  25.       sfrmMulti![txtDate].Visible = False
  26.       sfrmMulti![txtUnits].Visible = False
  27.       sfrmMulti![txtPrice_Unit].Visible = False
  28.       sfrmMulti![txtGIM].Visible = False
  29.       sfrmMulti![txtID].Visible = False
  30.       sfrmMulti!cmdMore.Visible = False
  31.     End If
  33.     Else
  34.         Call fMultiSort
  36.         cmdSingle.Enabled = True
  37.         cmdSummaryDetail.Enabled = True
  38.         cmdLease_Single.Enabled = False
  39.         cmdLease_Detail.Enabled = False
  40.         sfrmSearch.Enabled = True
  41.         sfrmSearch.Visible = True
  42.         sfrmMulti.Enabled = False
  43.         sfrmMulti.Visible = False
  44.         sfrmLease.Visible = False
  45.         sfrmLease.Enabled = False
  46. End If
  47. End Sub
Any help would be greatly appreciated.
Jun 6 '12 #1
Share this Question
Share on Google+
15 Replies

P: 759
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Dim StoredMulti, StoredSingle 'This variables will store the selected items
  6. Private Sub Form_Load()
  7.     Select Case frmOption
  8.         Case 1
  9.             lstBuildings.RowSource = "q_Multi" 'Multi-family
  10.         Case 2
  11.             lstBuildings.RowSource = "q_Single" 'Single-family
  12.     End Select
  13. End Sub
  15. Private Sub frmOption_BeforeUpdate(Cancel As Integer)
  16. Dim i As Long
  17.     Select Case frmOption.Value
  18.         Case 1 'Multi
  19.             Call StoreSelected(StoredSingle)
  20.             lstBuildings.RowSource = "q_Multi" 'Change the list box
  21.             Call RestoreSelection(StoredMulti)
  22.         Case 2 'Single
  23.             Call StoreSelected(StoredMulti)
  24.             lstBuildings.RowSource = "q_Single" 'Change the list box
  25.             Call RestoreSelection(StoredSingle)
  26.     End Select
  27. End Sub
  29. Private Sub RestoreSelection(StoredIn)
  30. On Error GoTo Ex
  31. Dim i As Long
  32.     For i = 0 To UBound(StoredIn)
  33.         lstBuildings.Selected(StoredIn(i)) = True
  34.     Next i
  36. Ex:
  37. End Sub
  39. Private Sub StoreSelected(StoreIn)
  40. Dim strStoreIn As String, i As Long
  41.     strStoreIn = ""
  42.     For i = 0 To lstBuildings.ListCount - 1
  43.         If lstBuildings.Selected(i) Then
  44.             strStoreIn = strStoreIn & i & ","
  45.         End If
  46.     Next i
  47.     StoreIn = Null
  48.     If strStoreIn <> "" Then
  49.         strStoreIn = Left(strStoreIn, Len(strStoreIn) - 1)
  50.         StoreIn = Split(strStoreIn, ",")
  51.     End If
  52. End Sub
Attached Files
File Type: zip (31.8 KB, 81 views)
Jun 7 '12 #2

P: 28
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?
Jun 7 '12 #3

P: 28
I was thinking, what if I added a third option for building sales and make this the default? This may solve my problem.
Jun 7 '12 #4

P: 759
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 !
Jun 7 '12 #5

P: 28
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:
Expand|Select|Wrap|Line Numbers
  1. Dim StoredMulti, StoredBuilding, StoredLease
  2. Dim strSQL, strSQL_Multi As String
  4. Private Sub Form_Load()
  6. strSQL = "SELECT DISTINCT [Building_Type] FROM tblBuilding_Type_list WHERE [Enable_Units] = False ORDER BY [Building_Type];"
  7. strSQL_Multi = "SELECT DISTINCT [Building_Type] FROM tblBuilding_Type_list WHERE [Enable_Units] = True ORDER BY [Building_Type];"
  9. frmOption.Value = Null
  10. '    Select Case frmOption
  11. '        Case 1
  12. '            lstType.RowSource = strSQL
  13. '        Case 2
  14. '            lstType.RowSource = strSQL
  15. '        Case 3
  16. '            lstType.RowSource = strSQL_Multi
  17. '    End Select
  18. End Sub
  20. Private Sub StoreSelected(StoreIn)
  21. Dim strStoreIn As String, i As Long
  22.     strStoreIn = ""
  23.     For i = 0 To lstType.ListCount - 1
  24.         If lstType.Selected(i) Then
  25.             strStoreIn = strStoreIn & i & ","
  26.         End If
  27.     Next i
  28.     StoreIn = Null
  29.     If strStoreIn <> "" Then
  30.         strStoreIn = Left(strStoreIn, Len(strStoreIn) - 1)
  31.         StoreIn = Split(strStoreIn, ",")
  32.     End If
  33. End Sub
  35. Private Sub RestoreSelection(StoredIn)
  36. On Error GoTo Ex
  37. Dim i As Long
  38.     For i = 0 To UBound(StoredIn)
  39.         lstType.Selected(StoredIn(i)) = True
  40.     Next i
  42. Ex:
  43. End Sub
  45. Private Sub frmOption_BeforeUpdate(Cancel As Integer)
  46. Dim i As Long
  47.     Select Case frmOption.Value
  48.         Case 1 'Building Sales
  49.             Call StoreSelected(StoredMulti)
  50.             lstType.RowSource = strSQL
  51.             Call RestoreSelection(StoredBuilding)
  52.         Case 2 'Leases
  53.             Call StoreSelected(StoredMulti)
  54.             lstType.RowSource = strSQL
  55.             Call RestoreSelection(StoredBuilding)
  56.         Case 3 'Multi-Family
  57.             Call StoreSelected(StoredBuilding)
  58.             lstType.RowSource = strSQL_Multi
  59.             Call RestoreSelection(StoredMulti)
  60.     End Select
  61. End Sub
Jun 7 '12 #6

P: 393
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:


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.
Jun 7 '12 #7

P: 28
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.
Jun 7 '12 #8

P: 393
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. :)
Jun 7 '12 #9

P: 28
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.
Jun 7 '12 #10

P: 28
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.
Jun 7 '12 #11

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

The code is (see also the attachment):
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Dim Stored_1, Stored_2, Stored_3
  5. Dim WhereToStore As Long
  7. Private Sub Form_Load()
  8.     Select Case frmOption
  9.         Case 1
  10.             lstBuildings.RowSource = "q_1"
  11.             WhereToStore = 1
  12.         Case 2
  13.             lstBuildings.RowSource = "q_2"
  14.             WhereToStore = 2
  15.         Case 3
  16.             lstBuildings.RowSource = "q_3"
  17.             WhereToStore = 3
  18.     End Select
  19. End Sub
  22. Private Sub frmOption_BeforeUpdate(Cancel As Integer)
  23.     Select Case WhereToStore
  24.         Case 1
  25.             Call StoreSelected(Stored_1)
  26.         Case 2
  27.             Call StoreSelected(Stored_2)
  28.         Case 3
  29.             Call StoreSelected(Stored_3)
  30.     End Select
  31. End Sub
  33. Private Sub frmOption_AfterUpdate()
  34.     Select Case frmOption.Value
  35.         Case 1
  36.             lstBuildings.RowSource = "q_1"
  37.             Call RestoreSelection(Stored_1)
  38.             WhereToStore = 1
  39.         Case 2
  40.             lstBuildings.RowSource = "q_2"
  41.             Call RestoreSelection(Stored_2)
  42.             WhereToStore = 2
  43.         Case 3
  44.             lstBuildings.RowSource = "q_3"
  45.             Call RestoreSelection(Stored_3)
  46.             WhereToStore = 3
  47.     End Select
  48. End Sub
  52. Private Sub RestoreSelection(StoredIn)
  53. On Error GoTo Ex
  54. Dim i As Long
  55.     For i = 0 To UBound(StoredIn)
  56.         lstBuildings.Selected(StoredIn(i)) = True
  57.     Next i
  59. Ex:
  60. End Sub
  62. Private Sub StoreSelected(StoreIn)
  63. Dim strStoreIn As String, i As Long
  64.     strStoreIn = ""
  65.     For i = 0 To lstBuildings.ListCount - 1
  66.         If lstBuildings.Selected(i) Then
  67.             strStoreIn = strStoreIn & i & ","
  68.         End If
  69.     Next i
  70.     StoreIn = Null
  71.     If strStoreIn <> "" Then
  72.         strStoreIn = Left(strStoreIn, Len(strStoreIn) - 1)
  73.         StoreIn = Split(strStoreIn, ",")
  74.     End If
  75. End Sub
Attached Files
File Type: zip (50.3 KB, 94 views)
Jun 8 '12 #12

P: 393
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.
Attached Files
File Type: zip (27.0 KB, 108 views)
Jun 8 '12 #13

P: 28
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.
Jun 8 '12 #14

P: 28
Thanks immensely Mihail, everything works now.
Jun 8 '12 #15

P: 759
Glad to help you.
I have liked the challenge with your list box and I answer to your original question. But, for the future, keep in mind the very good advices from Dsatino.
Jun 8 '12 #16

Post your reply

Sign in to post your reply or Sign up for a free account.