Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-19-2018, 12:23 PM   #1
KathCobb
Newly Registered User
 
Join Date: Jun 2012
Posts: 24
Thanks: 2
Thanked 0 Times in 0 Posts
KathCobb is on a distinguished road
multiple selection list box to open query not working

I have a selection form to filter criteria and open a query (then export to excel). I thought I had it working properly but it appears not.

I have three combo boxes and one list box. The 3rd combo box (cboCompany) limits the choices to only that companies plans in the list box. (cboSelectPlanType)<<I changed it from a combo box to a list box, hence the improper naming. Anyway, I created a text box that was to be invisible and where the selections would be and then run the query. It just shows all values instead of the one selected or none depending on what criteria I use. Not sure what info to supply but here's what I have for the after update event of the list box:

Code:
Dim lst As Access.ListBox
Dim varItem As Variant
Dim strBuild As String
Set lst = Forms![frmSelectToExport]![cboSelectPlanType]
If lst.ItemsSelected.Count > 0 Then
  For Each varItem In lst.ItemsSelected
    strBuild = strBuild & lst.ItemData(varItem) & ","
  Next varItem
    Me![txtSelected] = Left$(strBuild, Len(strBuild) - 1)
End If
End Sub
I did not write the code, I did a lot of googling and found it, but it works to put the criteria into the text box (txtSelected)

In my query, under Table: tblInsuranceCompanyPlanLnk Field: fkInsurancePlanTypeID I have the following criteriapointing to the list box, which I suspect is the problem:

Code:
[Forms]![frmSelectToExport]![txtSelected]

The list box does show the fkInsurancePlanTypeID--or at least I believe it does, it shows the ID number followed by a comma then the next ID number. Do I have to say something in my criteria to say there are multiple values? I tried setting that criteria to the list box, but that returned everything. How I have it set now returns nothing.

As additional information, below is the after update event from cboCompany that populates the list box (also didn't write that all myself, part of it was already in the DB).

Thank you for any help you can provide.


Code:
Private Sub cboSelectCompany_AfterUpdate()

     Dim strSQL As String
    Dim strWhere As String
   
    strWhere = " WHERE (1=1)"
 
   If (IsNull(Me.cboSelectCompany) = False) Then strWhere = strWhere & " AND (fkInsuranceCompanyID=" & Me.cboSelectCompany & ")"
 
   
    strSQL = "SELECT DISTINCT tblInsurancePlanType.pkInsurancePlanTypeID, tblInsurancePlanType.PlanType " & _
             "FROM tblInsurancePlanType LEFT JOIN tblInsuranceCompanyPlanLink ON tblInsurancePlanType.pkInsurancePlanTypeID = tblInsuranceCompanyPlanLink.fkInsurancePlanTypeID " & _
             strWhere & _
             "ORDER BY tblInsurancePlanType.PlanType;"
    Me.cboSelectPlanType.RowSource = strSQL
    Me.cboSelectPlanType.Requery
    Me.cboSelectPlanType = 0
End Sub

KathCobb is offline   Reply With Quote
Old 10-19-2018, 01:47 PM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 805
Thanks: 0
Thanked 182 Times in 182 Posts
June7 will become famous soon enough
Re: multiple selection list box to open query not working

Your narrative says criteria is pointing to listbox but code shows textbox.

If you only want one selected item from listbox, don't need to loop, but shouldn't hurt.

If you want to allow multiple selections from listbox, can't use dynamic parameterized query.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 10-19-2018, 06:13 PM   #3
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 664
Thanks: 0
Thanked 208 Times in 195 Posts
moke123 will become famous soon enough
Re: multiple selection list box to open query not working

Not sure I'm following but it seems to me you may be looking for an In clause.
Something like ...
Code:
Where fkInsurancePlanTypeID in (1,3,4,6)

moke123 is offline   Reply With Quote
Old 10-20-2018, 08:51 AM   #4
KathCobb
Newly Registered User
 
Join Date: Jun 2012
Posts: 24
Thanks: 2
Thanked 0 Times in 0 Posts
KathCobb is on a distinguished road
Re: multiple selection list box to open query not working

I googled some more after posting and I guess I need the Items selected collection or something? I didnít understand how it could apply to my query parameter...which maybe isnít possible? Can I pass a criteria to my query from a multi select list box? I have both...a list box and a text box. I have it set up now so that when I select an item in the list box, it is creating a string in a text box of the selected IDs with commas. Can that string be used to create a query criteria?

I have a lot going on with this query and have zero understanding of SQL, thatís why I keep trying to use query criteria. All the joins....are just beyond my understanding, which is the same with VBA for a query. Should I post the SQL code the query has created? There might be more not working than I think.
KathCobb is offline   Reply With Quote
Old 10-20-2018, 09:09 AM   #5
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Micron is on a distinguished road
Re: multiple selection list box to open query not working

Your could wrap your comma separated values in an IN clause - like IN(val1, val2, val3...)
I'm trying to make sense of the code posted but struggle with what's in some of the variables. If your csv elements are text, you'll have to modify your string to wrap each element in single quotes ' in order to make use of the IN clause. Then strWhere would be something like

strWhere = strWhere & "IN('" & strCSV & "')".

Note that I show starting and ending single quotes. If your csv string began and ended with single quotes, you wouldn't add them as I did here.
EDIT
this strBuild = strBuild & lst.ItemData(varItem) & ","
would become
strBuild = strBuild & lst.ItemData(varItem) & "','"
in order to inject single quotes between the csv's. Then you'd need to add one ' in the beginning and ending of the IN clause as I've shown.

Last edited by Micron; 10-20-2018 at 09:15 AM.
Micron is offline   Reply With Quote
Old 10-20-2018, 11:23 AM   #6
KathCobb
Newly Registered User
 
Join Date: Jun 2012
Posts: 24
Thanks: 2
Thanked 0 Times in 0 Posts
KathCobb is on a distinguished road
Re: multiple selection list box to open query not working

Iím not sure I understand all of your references.... I have never used the In Clause or know where to put it exactly. The strWhere in the code I posted is being used to populate the list box to narrow its choices, i posted it for reference. It is not being used to pass the values of the selections. Which is why I am wondering if I should start from scratch and build my query criteria either in SQL or VBA. I would need some help with that also though.

My Selection boxes on the form are as follow
1st: Select an Agent or All
2nd: Select a County or All
3rd: Select a Company or All
>>When selecting company, a multi select list box is populated with specific Plan Types<<
4th: Select Plan Type. I have it set to Extended so you can hold the shift and select all.

5th: text box set to invisible that passes the ID from PlanType List Box which I thought was the way to get the query to limit to these choices.

I also have two option group buttons that each have 3 choices. And I have an IIF statement in my criteria for these. But I think that the SQL May have used AND for these two fields and I might want OR?

I also noticed that because I am using a union query as the rowsource for my first three combo box to include ďAllĒ, the IIF Is Null criteria I have I think is eliminating records that do not have any entry in the fields, which is not what I wanted. For example, sometimes there is client info being entered and the entry person forgets to put in a county. I still need that name to show up when All is selected.
Like I said, there is a lot going on with the query. I donít know know a lot about strWhere uses, nor Joins, and I get confused about the use of double or single quotes.

In a nutshell thatís what Iím trying to accomplish. Is there any tutorial or other info I could provide to get me where I need to go? This database has 10,000 records across multiple tables and I would have no idea how to delete all these records to post a copy. Can I export tables and have them be empty? Sigh, it seems this all should be much easier.
I appreciate any help anyone can provide.
KathCobb is offline   Reply With Quote
Old 10-20-2018, 11:38 AM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,890
Thanks: 92
Thanked 1,694 Times in 1,572 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: multiple selection list box to open query not working

Kath
If you're suggesting uploading but with your tables emptied you can
EITHER copy each table in turn and then paste Structure Only.
OR run a DELETE query on each table n turn.

IMPORTANT
Make A COPY of your database and do this on the copy

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-20-2018, 04:14 PM   #8
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Micron is on a distinguished road
Re: multiple selection list box to open query not working

If you looked it up as did I just now (to provide information on it) no wonder you'd be confused. Apparently what I should have said is the IN operator, not clause. As a clause, it has more to do with identifying tables in remote databases. As an operator, I had a link you could look at but I'm not allowed to post links yet, so rather than do a work-around, I will have to pass it to Isaldogs to post it if he will, or you can google it an look for an O'Reilly link.

In the meantime, to try it out for yourself, create a select query with a couple of fields, at least one that has more than one value in the field. Enter a single criteria in that field, run it and see how many records are returned. Go back to design and in that same field, enter IN (val1, val2) where val1 and 2 are values you know are in that field. If the values are text, be sure to surround each with " and separate the values with commas (as in "val1", "val2") and see the difference when you run it again. The returned records should only be the ones that have both values in that field. If the values are numeric, do not use " but still use commas to separate values.


Based on my understanding of your posts, I thought you were building sql in code; specifically the WHERE clause which is why earlier I had this set aside:
Code:
Dim strWhere As String

If lst.ItemsSelected.Count > 0 Then
  For Each varItem In lst.ItemsSelected
    strBuild = strBuild & lst.ItemData(varItem) & "','"
  Next varItem
    strBuild = Left(strBuild, Len(strBuild) -1)
    strWhere = "WHERE sometable.somefield IN('" & strBuild & "') " & ...
    Me![txtSelected] = strBuild
End If

EDIT: link added by Isladogs as requested by Micron:https://www.oreilly.com/library/view...8/ch01s03.html

Last edited by isladogs; 10-20-2018 at 05:11 PM.
Micron is offline   Reply With Quote
Old 10-20-2018, 04:32 PM   #9
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Micron is on a distinguished road
Re: multiple selection list box to open query not working

Kath; I composed a long reply complete with code and it disappeared when I hit submit. I'm posting this short note just to let you know I wasn't ignoring your last comment, but also to test and see what happens this time.
Micron is offline   Reply With Quote
Old 10-20-2018, 04:48 PM   #10
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Micron is on a distinguished road
Re: multiple selection list box to open query not working

My post prior to this one tells me that simply because I forgot that I can't post links yet (I was in Advanced view when it let me know) my post got dumped after I removed the link and hit submit. That is disgusting considering how much time I put into it. If that's not why then hopefully it's not something that I did.

Anyway, google IN operator but not IN clause because I just discovered they're different & maybe that confused you. I found a decent explanation where O'Reilly was the link target but I'm not allowed to post it for you. I suggested that you create a query based on a table and use a pertinent value from a field as criteria and see how many records you get. Go back to design, remove the criteria and in it's place put IN ("value1", "value2") where value# 's are values that exist in the field. Run it and see how many records now. There should be more because you've asked for multiple criteria. If value# is numeric, remove the " but keep the comma separators. Basically, it's just like stringing together a bunch of OR's.

I thought you were building sql WHERE clause in code from multiple listbox values based on my perhaps flawed understanding of your posts. Sorry if that's not the case. Regardless, I'm sure you can get where you need to be with a little help.
Micron is offline   Reply With Quote
Old 10-20-2018, 05:14 PM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,890
Thanks: 92
Thanked 1,694 Times in 1,572 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: multiple selection list box to open query not working

The last 3 posts by Micron were automatically moderated - probably because of trying to add a link in post #8. That's why they seemed to disappear.
If this happens again, please click the report button so any of the moderators can solve the problem

I've now approved these and at Micron's request, I've now added the missing link

Posting to trigger email notifications

EDIT:
It may help to consider the IN operator as a shorter way of writing multiple OR values in e.g. query criteria.
For example, instead of writing "ABC" or "DEF" or "GHI" or "XYZ" ... , just write IN ("ABC","DEF","GHI","XYZ")
If there are a lot of items to include, using IN makes it far quicker to build your criteria
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 10-20-2018 at 05:40 PM.
isladogs is offline   Reply With Quote
Old 10-20-2018, 06:53 PM   #12
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 664
Thanks: 0
Thanked 208 Times in 195 Posts
moke123 will become famous soon enough
Re: multiple selection list box to open query not working

here's an example with a bunch of multi-select list box stuff.
There is one example of using "In" in a where clause and setting the rowsource of a list box.
Attached Files
File Type: zip ListboxExamples.zip (69.6 KB, 5 views)
moke123 is offline   Reply With Quote
Old 10-23-2018, 07:51 AM   #13
KathCobb
Newly Registered User
 
Join Date: Jun 2012
Posts: 24
Thanks: 2
Thanked 0 Times in 0 Posts
KathCobb is on a distinguished road
Re: multiple selection list box to open query not working

Thank you everybody!! I apologize for not replying sooner, I've been sick and have not had a chance to look this over. I will try it today or tomorrow and post back my results. Thank you agin, I really appreciate everyone's help
KathCobb is offline   Reply With Quote
Old 10-26-2018, 07:56 AM   #14
KathCobb
Newly Registered User
 
Join Date: Jun 2012
Posts: 24
Thanks: 2
Thanked 0 Times in 0 Posts
KathCobb is on a distinguished road
Re: multiple selection list box to open query not working

Hello again...

Turns out I had the answer the whole time in a sample. To continue using query expressions, which I am able to understand easier, I needed to put an expression in the Field box that pointed to my text box (this is the box that multi-set list box is passing the ID values of the selections made) for me its :

Code:
[Forms]![frmSelectToExport]![txtSelected]
Then in The Criteria field I used "Like"

Code:
Like "*" & [fkPlanTypeID] & "*"
The query now filter to my selections.

KathCobb is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Selection List Box as Criterea for Query Jameo Forms 9 09-17-2015 06:20 AM
Query by Multiple Selection List Box zyxwvu44 Queries 8 07-26-2007 01:37 PM
Query Criteria from multiple selection list box feathers212 Forms 4 06-19-2007 10:55 AM
Multiple Selection List Box on Form & saving selection delilah84au Forms 0 10-08-2006 10:45 PM
List Box Multiple Selection for query criteria carvind Queries 8 08-27-2001 11:23 PM




All times are GMT -8. The time now is 10:34 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World