Cascading form box with multi-select and multiple column popup fai

[karen]

Hi, I've looked at all of the other combo box posts but none of the solutions
work for my set up. Please help. I want cascading form boxes that when an
item within is selected a list of values to help the user choose the correct
item is provided, then, when the correct item(s) are selected (set up for
multi-select) the second combo box is updated based on the selections from
the first.

In addition, the update occurs after the user has clicked the corresponding
update button that sends a list of their selections for easier view to a list
box. The list box is being updated but the second combo box is not being
updated. Also, the first combo box is displaying the default value as an
option, but i don't want it to because it creates a null value in the list
box if the user selects the default value.

Please tell me what you need to help me solve this. Here is the code I used
with the update button under 'on click' to update the list box and second
combo box once items from the first combo box are chosen.


Private Sub Show_My_Selections_Empire_Click()
Dim oItem As Variant
Dim sTemp As String
Dim iCount As Integer
Dim strSQL As String

Kingdom.Enabled = True
My_Selections_Empire.Enabled = True

iCount = 0

If Me!Empire.ItemsSelected.Count <> 0 Then
For Each oItem In Me!Empire.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Me!Empire.ItemData(oItem)
iCount = iCount + 1
Else
sTemp = sTemp & ", " & Me!Empire.ItemData(oItem)
iCount = iCount + 1
strSQL = "Select Distinct Taxonomy_And_Variables.Kingdom,
Taxonomy_And_Variables.Synonyms_Kingdom,
Taxonomy_And_Variables.Common_Kingdom, Taxonomy_And_Variables.SubKingdom,
Taxonomy_And_Variables.Common_SubKingdom from Taxonomy_And_Variables where
(((Taxonomy_And_Variables.Empire) = (IN" & sTemp & ")))"

Me!Kingdom.RowSource = strSQL
Me!Kingdom.Requery

End If
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If
Me!My_Selections_Empire.Value = sTemp

 

[Douglas J. Steele]

Combo boxes do not support multiselect, so there's no way to achieve what
you want.

If you really meant list boxes (which do support multiselect), there are a
number of problems with your code.

Your SQL statement is wrong, and you're calculating it in the wrong place.

Try:

If Me!Empire.ItemsSelected.Count <> 0 Then
For Each oItem In Me!Empire.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Me!Empire.ItemData(oItem)
iCount = iCount + 1
Else
sTemp = sTemp & ", " & Me!Empire.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
strSQL = "Select Distinct Kingdom, Synonyms_Kingdom, " & _
"Common_Kingdom, SubKingdom, Common_SubKingdom " & _
"From Taxonomy_And_Variables " & _
"Where Empire IN (" & sTemp & ")"
Me!Kingdom.RowSource = strSQL
Me!Kingdom.Requery
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

That assumes that Empire is a numeric field. If it's text, you'll need

If iCount = 0 Then
sTemp = sTemp & "'" &Me!Empire.ItemData(oItem) & "'"
iCount = iCount + 1
Else
sTemp = sTemp & ", '" & Me!Empire.ItemData(oItem) & "'"
iCount = iCount + 1
End If

Exagerated for clarity, that's

If iCount = 0 Then
sTemp = sTemp & " ' " &Me!Empire.ItemData(oItem) & " ' "
iCount = iCount + 1
Else
sTemp = sTemp & ", ' " & Me!Empire.ItemData(oItem) & " ' "
iCount = iCount + 1
End If

--


Doug Steele, Microsoft Access MVP

(no private e-mails, please)