MultiSelect List Box Help

[Razor1]

I have 2 multiselect boxes.Box 1 contains models box 2 contains colors related to specfic models. I want to show only the colors for the specfic models in box 2. Box 2 is also a multiselect box.
Example:

Model Color
1 Red
1 White
1 Blue
2 Geeen
2 Yellow
2 Red
3 Blue
3 Orange

If you pick 1 and 3 in box 1 you should see Red,White,Blue and Orange.

Thanks in advance for any help.


Razor1

 

[MajP]

You can use the idea in FAQ701-6099 as the basis for the second listbox query.

 

[Razor1]

I should have mentioned I have a single table with the 2 fields, model and color.
List box 2 should update when list box 1 changes.

Thanks again for any help provided.

Razor1

 

[MajP]

using the faq your second query would be

SELECT distinct color FROM tblSingleTable
WHERE IsSelectedVar("frmYourForm","lboModel",[Model])=-1;

requery on the 2nd listbox got focus event.

 

[Razor1]

I get an invalid reference on this line
If lbo.ItemsSelected.Count = 0 Then

Any ideas?

Function IsSelectedVar(strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
On Error GoTo PROC_ERR
Dim lbo As ListBox
Dim item As Variant
If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lbo = Forms(strFormName)(strListBoxName)
If lbo.ItemsSelected.Count = 0 Then <------ FAILS HERE
IsSelectedVar = True 'return all if no items selected
Else
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) = varValue Then
IsSelectedVar = True
Exit Function
End If
Next
End If

PROC_EXIT:
Exit Function

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT


End Function


Razor1

 

[dhookom]

Razor1,
Show us your SQL. For instance:

SELECT distinct color FROM tblSingleTable
WHERE IsSelectedVar("frmYourForm","lboModel",[Model])=-1;

We have no way of knowing if you used your actual form and control and field names. We can't see your screen.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Also need to know the names of your form and the listbox name. As Duane points out the likely error is you used wrong names.