2 List Boxs - Multiple Select - select from single box

[CHWRoma]

I have two list boxes in my form. they are both set up to choose more then
one item in each list box but you also have to choose at least one item from
each list box. I need to be able to also pick one item from one box and not
the other, and vice/versa. Example:

List Box 1 - dog, cat, child
List Box 2 - litter, food, toys

I want to be able to chose dog but not anything from List Box 2 - litter /
food / toys

Here's the code I am using.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
For Each varItem In Me!List_Provider_Name.ItemsSelected
strCriteria = strCriteria & ",'" &
Me!List_Provider_Name.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM dbo_PROVIDER_VENDOR " & _
"WHERE dbo_PROVIDER_VENDOR.VENDOR_NAME IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryMultiSelect"
DoCmd.RunMacro "Open and Close Provider Name Table"


Thanks for any help.......Roma

 

[TonyT]

That code appears to be for one box only, not the 2 you mentioned, so
presumably you have another similar one for the second combobox, can you not
just check string lengths and then offer 2 different strSQL statements
depending on the result?

TonyT

 

[CHWRoma]

I only sent the one box code as they are so similiar. I had thought about
your suggestion but wasn't exactly sure how to go about writing the 2
different strSQL statements. I'm a newbie at all this

 

[TonyT]

Try moving the code below to the On_Click event of a new command button
called 'cmdResults' or something similar, leaving the after_update event for
each listbox empty.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As StringDim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
For Each varItem In Me!List_Provider_Name.ItemsSelected
strCriteria = strCriteria & ",'" &
Me!List_Provider_Name.ItemData(varItem) & "'"
Next varItem, vbExclamation, "Nothing to find!" '(optional)
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
For Each varItem In Me!List_Provider_OtherItem.ItemsSelected
strCriteria2 = strCriteria2 & ",'" &
Me!List_Provider_OtherItem.ItemData(varItem) & "'"
Next varItem, vbExclamation, "Nothing to find!" '(optional)
End If
strCriteria2 = Right(strCriteria2, Len(strCriteria2) - 1)
"WHERE dbo_PROVIDER_VENDOR.VENDOR_NAME IN(" &
strCriteria & ") AND dbo_PROVIDER_VENDOR.VENDOR_OTHERITEM IN(" &
strCriteria2 & ");""WHERE dbo_PROVIDER_VENDOR.VENDOR_NAME IN(" &
strCriteria & ");""WHERE dbo_PROVIDER_VENDOR.VENDOR_OTHERITEM IN(" &
strCriteria2 & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryMultiSelect"
DoCmd.RunMacro "Open and Close Provider Name Table"

Watch out for wordwrap - some is spaced to show what I mean here and some
isn't,
try copy and pasting into code and remove required spaces - >> denotes added
code.

hopefully this will work, if not let me know where it falls over!!!

TonyT..

 

[CHWRoma]

TonyT, I have to apologize for the time that has passed since you were so
kind to put this information out there for me. The project I was working on
was put on hold and I was given another one, which is keeping me too busy to
try your code. I promise, by mid November I will be able to give this a
try.....I do appreciate your help with this and am sorry that I allowed so
much time to go by before responding.

Thanks,
Roma