MultiSelect ListBox 2

[zimmer9]

I have a list box for States of the U.S. and 2 associated option buttons.

The option buttons were meant to toggle the Multiselect property of the listbox between Multiselect None or Simple.
One option button which I made the default is labeled as Group By (the choices are ALL, Group A states and Group B states).

The second option button allows the user to select individual state(s) and I wanted to change the Listbox to Muliselect when the user chooses the option button Individual States.

The listbox has a default value of lstStates.Multiselect equal None.

Is it possible for the Listbox to be non Multiselect upon the Form Load. Then if the user clicks on the option button for Individual States, the list box changes to a multiselect state.

When I use VBA code as follows, I get the error message
Runtime Error '2448' You can't assign a value to this object'

Is there another way for me to code the toggling of the Multiselect property of the Listbox (between 0(none) and 1 (simple) to avoid this runtime error I get ? Frame60 represents the object group for the 2 option buttons.



Private Sub Frame60_AfterUpdate()
Dim strOptStateSelect As String
Select Case Me.Frame60.Value
Case 1
strOptStateSelect = "G"
Me!lstStates.MultiSelect = 0
Call UpdateValueToStateGrList
Case 2
strOptStateSelect = "I"
Me!lstStates.MultiSelect = 1
Call UpdateValueToStateIndList
End Select
End Sub

Private Sub UpdateValueToStateGrList()
Dim strSQLGrState As String
strSQLGrState = Chr(34) + "ALL" + Chr(34) + ";" + Chr(34) + "FALL STATES" + Chr(34) + ";" + Chr(34) + "SPRING STATES" + Chr(34) + ";"
Me!lstStates.RowSourceType = "Value List"
Me!lstStates.RowSource = strSQLGrState
'Me!lstStates.MultiSelect = 0
Me!lstStates.Requery
End Sub

Private Sub UpdateValueToStateIndList()
Dim strSQLIndState As String
strSQLIndState = "SELECT DISTINCT STATEFS FROM tblStatesAll ORDER BY STATEFS"
Me!lstStates.RowSourceType = "Table/Query"
Me!lstStates.RowSource = strSQLIndState
'Me!lstStates.MultiSelect = 1
Me!lstStates.Requery
End Sub

 

[FancyPrairie]

The multi-select property can only be set in Design View.

 

[FancyPrairie]

Accidently hit the Submit button too soon. In the beforeupdate event of the list box, check to see if an item has already been selected, if so, cancel the current selection.

Or, have 2 list boxes (one on top of the other). One is multiselect the other not. One is visible the other not. Then, just toggle the visible properties.

 

[zimmer9]

I tried the approach of 2 list boxes with 1 on top of the other. 1 is hidden while the other is visible.

The first listbox is titled lstStates (grouped states: requires no multiselect).

The second listbox is titled lstStatesInd (individual states: requires a multiselect listbox).

The 1st list box that I display initally works fine on the form load. When I switch to the 2nd list box, that also works fine.

However, I encounter a problem when I switch back from the 2nd list box to the 1st list box. When I switch back to the first list box, I am not able to click on any rows on the list box. For that matter, I am not able to click on any place on the form.

One thing I noticed is that when I right click over the 1st listbox (after I switch back from the 2nd to the 1st listbox) to observe the properties form, it shows that I am on the Option Group: Frame (not the 1st list box). When I then close the properties window, I am than able to click on the form once again. Now if I right click, the properties window is pointing correctly to the 1st listbox.

The following is my code. I tried to deselect the users choices on a list box when a user switched from one listbox to the other by clicking on the option buttons.

Private Sub Form_Load()
Call UpdateValueToStateGrList
lstStatesInd.Visible = False
End Sub

Private Sub Frame60_AfterUpdate()
Dim strOptStateSelect As String
Dim varItem As Variant
Dim lst As Access.ListBox

Select Case Me.Frame60.Value
Case 1
strOptStateSelect = "G"
lstStates.RowSource = Chr(34) + "ALL" + Chr(34) + ";" + Chr(34) + "FALL STATES" + Chr(34) + ";" + Chr(34) + "SPRING STATES" + Chr(34) + ";"
Set lst = lstStates
For Each varItem In lst.ItemsSelected
lst.Selected(varItem) = False
Next
lstStatesInd.Requery
lstStatesInd.Visible = False
lstStates.Visible = True

Case 2
strOptStateSelect = "I"
lstStatesInd.RowSource = "SELECT DISTINCT STATEFS FROM tblStatesAll ORDER BY STATEFS"
Set lst = lstStatesInd
For Each varItem In lst.ItemsSelected
lst.Selected(varItem) = False
Next
lstStates.Requery
lstStates.Visible = False
lstStatesInd.Visible = True

End Select
End Sub

 

[zimmer9]

I can pinpoint my problem to the following offending code:

Set lst = lstStates
For Each varItem In lst.ItemsSelected
lst.Selected(varItem) = False
Next

Do you know how I would rewrite this erroneous code ?

When I remove this snippet of code, my application works fine.

 

[zimmer9]

I'm a step closer. The following logic works fine for the multiselect list box but not for the single select listbox that I am working on. My question is how would I deselect a single select list box.

Set lst = lstStates
For Each varItem In lst.ItemsSelected
lst.Selected(varItem) = False
Next

 

[RoyVidar]

You can try assigning vbNullstring to it:

[tt]lstMultiselectNone = vbNullString[/tt]

Sometimes there's a bit of challenge with your approach of deselecting with multiselect listboxes too, another way is to reset it's rowsource:

[tt]me!lstStates.rowsource=me!lstStates.rowsource[/tt]

Roy-Vidar

 

[FancyPrairie]

What RoyVidar said will work. However, an FYI, single select and multiselect list boxes are handled differently when attempting to loop thru them to see what is selected. Check out the ListIndex property in the following code.

Also, I wouldn't bother to requery the list box when you set its visible property to false. Rather, requery it when you set it to true.

Private Sub Frame60_AfterUpdate()
    Dim strOptStateSelect As String
    Dim varItem As Variant
    Dim lst As Access.ListBox

    Select Case Me.Frame60.Value
       Case 1
          strOptStateSelect = "G"
          lstStates.RowSource = Chr(34) + "ALL" + Chr(34) + ";" + Chr(34) + "FALL STATES" + Chr(34) + ";" + Chr(34) + "SPRING STATES" + Chr(34) + ";"
          Set lst = lstStates
          For Each varItem In lst.ItemsSelected
              lst.Selected(varItem) = False
          Next
          lstStatesInd.Requery
          lstStatesInd.Visible = False
          lstStates.Visible = True
                
       Case 2
          strOptStateSelect = "I"
          lstStatesInd.RowSource = "SELECT DISTINCT STATEFS FROM tblStatesAll ORDER BY STATEFS"
          
          lstStatesInd.Selected(lstStatesInd.ListIndex) = False

          lstStates.Requery
          lstStates.Visible = False
          lstStatesInd.Visible = True
                        
       End Select
End Sub

 

[zimmer9]

I am trying to use the ListIndex property to deselect the item selected on a non-Multiselect listbox.

In my Case 1 code which follows:
I tried using a VBA line of code as follows:

Me.lstStates.ListIndex = -1

I got back the error message:
Runtime Error 7777
You've used the ListIndex property incorrectly

How would I use the ListIndex property to deselect the users selection from a listbox named lstStates which is non-Multiselect ?


Private Sub Frame60_AfterUpdate()
Dim strOptStateSelect As String
Dim varItem As Variant
'Dim lst As Access.ListBox
Dim intI As Integer
Select Case Me.Frame60.Value
Case 1
strOptStateSelect = "G"
Me.lstStates.RowSource = Chr(34) + "ALL" + Chr(34) + ";" + Chr(34) + "FALL STATES" + Chr(34) + ";" + Chr(34) + "SPRING STATES" + Chr(34) + ";"

Me.lstStatesInd.Visible = False
Me.lstStates.ListIndex = -1
Me.lstStates.Requery
Me.lstStates.Visible = True

 

[RoyVidar]

Setting to vbNullString didn't work?

If you wish to use the .ListIndex, try setting focus to the listbox control first

Roy-Vidar

 

[zimmer9]

RoyVidar Rules !!!!!
You solved my problem.

 

[FancyPrairie]

zimmer9, if you look at the code I posted (copy of yours with the correction) you will see how to do it. It is the 6th line from the bottom. It looks like this;

lstStatesInd.Selected(lstStatesInd.ListIndex) = False

 

[zimmer9]

Could you tell me how I would modify the following code so that if the user deselected all the listbox items (this is a multiselect listbox) and left the listbox (with no items selected), that the application would automatically select (hightlight) the first item on the listbox (as if the user selected the first item) ?

Right now, with the following code, if the user deselects all the items on the listbox, the cursor won't allow access to any controls on my form except for the listbox.

Private Sub lstStatesInd_AfterUpdate()
Dim lst As Access.ListBox
Set lst = Me.lstStatesInd
Me.lstStatesInd.SetFocus
If lst.ItemsSelected.Count = 0 Then
Me.lstStatesInd.Visible = True
Me.lstStatesInd.ListIndex = 0
End If

End Sub

 

[zimmer9]

I figured out my mistake

the last line should be

not Me.lstStatesInd.ListIndex = 0
s/b Me.lstStatesInd.Selected(0) = True