2 List Boxs - Multiple Select - select from single box CHWRoma 28.09.2006 14:01:02 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

RE: 2 List Boxs - Multiple Select - select from single box TonyT 28.09.2006 19:35:01 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" wrote: [Quoted Text] > 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 >

RE: 2 List Boxs - Multiple Select - select from single box CHWRoma 28.09.2006 19:51:02 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" wrote: [Quoted Text] > 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" wrote: > > > 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 > >

RE: 2 List Boxs - Multiple Select - select from single box TonyT 30.09.2006 08:13:02 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 String [Quoted Text] >>Dim strCriteria2 as String Dim strSQL As String Set db = CurrentDb() Set qdf = db.QueryDefs("qryMultiSelect") >>If Me!List_Provider_Name.ItemsSelected.Count >=1 Then For Each varItem In Me!List_Provider_Name.ItemsSelected strCriteria = strCriteria & ",'" & Me!List_Provider_Name.ItemData(varItem) & "'" Next varItem >>Else: MsgBox "You did not select anything from the list" _ , vbExclamation, "Nothing to find!" '(optional) End If strCriteria = Right(strCriteria, Len(strCriteria) - 1) >>If Me!List_Provider_OtherItem.ItemsSelected.Count >=1 Then For Each varItem In Me!List_Provider_OtherItem.ItemsSelected strCriteria2 = strCriteria2 & ",'" & Me!List_Provider_OtherItem.ItemData(varItem) & "'" Next varItem >>Else: MsgBox "You did not select anything from the list" _ , vbExclamation, "Nothing to find!" '(optional) End If strCriteria2 = Right(strCriteria2, Len(strCriteria2) - 1) >>If Len(strcriteria)>=1 then >> If Len(StrCriteria2)>=1 then >> strSQL = "SELECT * FROM dbo_PROVIDER_VENDOR " & _ "WHERE dbo_PROVIDER_VENDOR.VENDOR_NAME IN(" & strCriteria & ") AND dbo_PROVIDER_VENDOR.VENDOR_OTHERITEM IN(" & strCriteria2 & ");" >> Else: strSQL = "SELECT * FROM dbo_PROVIDER_VENDOR " & _ "WHERE dbo_PROVIDER_VENDOR.VENDOR_NAME IN(" & strCriteria & ");" >> End If >>ElseIf Len(StrCriteria2)>=1 then >> strSQL = "SELECT * FROM dbo_PROVIDER_VENDOR " & _ "WHERE dbo_PROVIDER_VENDOR.VENDOR_OTHERITEM IN(" & strCriteria2 & ");" >>Else:msgbox"Nothing selected from either List" >> Exit Sub >>End If 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" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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 > > >