multiple selection list box to open query not working (1 Viewer)

KathCobb

Registered User.
Local time
Today, 03:51
Joined
Jun 29, 2012
Messages
46
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
 

June7

AWF VIP
Local time
Today, 02:51
Joined
Mar 9, 2014
Messages
5,425
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.
 

moke123

AWF VIP
Local time
Today, 06:51
Joined
Jan 11, 2013
Messages
3,852
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)
 

KathCobb

Registered User.
Local time
Today, 03:51
Joined
Jun 29, 2012
Messages
46
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.
 

Micron

AWF VIP
Local time
Today, 06:51
Joined
Oct 20, 2018
Messages
3,476
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:

KathCobb

Registered User.
Local time
Today, 03:51
Joined
Jun 29, 2012
Messages
46
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.
 

isladogs

MVP / VIP
Local time
Today, 10:51
Joined
Jan 14, 2017
Messages
18,186
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
 

Micron

AWF VIP
Local time
Today, 06:51
Joined
Oct 20, 2018
Messages
3,476
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 a moderator:

Micron

AWF VIP
Local time
Today, 06:51
Joined
Oct 20, 2018
Messages
3,476
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

AWF VIP
Local time
Today, 06:51
Joined
Oct 20, 2018
Messages
3,476
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.
 

isladogs

MVP / VIP
Local time
Today, 10:51
Joined
Jan 14, 2017
Messages
18,186
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
 
Last edited:

moke123

AWF VIP
Local time
Today, 06:51
Joined
Jan 11, 2013
Messages
3,852
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.
 

Attachments

  • ListboxExamples.zip
    69.6 KB · Views: 223

KathCobb

Registered User.
Local time
Today, 03:51
Joined
Jun 29, 2012
Messages
46
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

Registered User.
Local time
Today, 03:51
Joined
Jun 29, 2012
Messages
46
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. :)
 

abelmumo

New member
Local time
Today, 11:51
Joined
Jan 26, 2024
Messages
7
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. :)
Hi Kath,
I know it's been years since you solved this problem, which is exactly the problem I am struggling with at the moment.
Is there any chance you could help me clarify your solution?

Where exactly did you set [Forms]![frmSelectToExport]![txtSelected] and Like "*" & [fkPlanTypeID] & "*"?

I manage to have a similar txtSelected textbox with the selected elements on the multi-set list box separated by ",", however usint that textbox as filtering criteria on a query is not working (only when I select 1 value, but not working for multiple selection).

Thank you in advance
 

moke123

AWF VIP
Local time
Today, 06:51
Joined
Jan 11, 2013
Messages
3,852
I'm not sure I follow your question.

You would use *Like* if you have one value, but you would use an "In" clause for multiple values from a multiselect listbox.

Could you clarify what you want to do.
 

abelmumo

New member
Local time
Today, 11:51
Joined
Jan 26, 2024
Messages
7
Hi moke123,


Sorry for poorly explaining myself previously.

I have a form with a list box in which I want the used to be able to make multiple selection of values. The name of the list box is CodigoMasa and exists on the form 1_SelectorConsulta.
I have created the following code

Code:
Private Sub btnCodigoMasa_Click()
    Dim i As Integer
    Dim strCodigoMasa As String
    
    For i = 0 To Me.CodigoMasa.ListCount - 1
        If Me.CodigoMasa.Selected(i) Then
            strCodigoMasa = strCodigoMasa & Me.CodigoMasa.Column(0, i) & ","
        End If
    Next i
    If Len(strCodigoMasa) > 0 Then
        Me.txtCodigoMasa = Left(strCodigoMasa, Len(strCodigoMasa) - 1)
    Else
        Me.txtCodigoMasa = ""
    End If
End Sub

This CODE gives me a concatenated text string with the selected results in a text box called txtCodigoMasa on my form.

I want to use that text as the input for an IN criteria on another query, so, in that query, I set as criteria the following:

Code:
 In([Forms]![1_SelectorConsulta]![txtCodigoMasa])

However, I now I am missing something because this only works when I select only one result. I have seen another responses including building the "where" sentence of an sql query but I am struggling trying to mix it all together to reach the solution to my problem.

Thank you in advance for your help.
 

moke123

AWF VIP
Local time
Today, 06:51
Joined
Jan 11, 2013
Messages
3,852
here's a function I use and an example of filtering with a multiselect listbox.

There's an old version of my function in the example in post #12 which should give you an idea what the function arguments do.
 

Attachments

  • MSList.accdb
    704 KB · Views: 27

theDBguy

I’m here to help
Staff member
Local time
Today, 03:51
Joined
Oct 29, 2018
Messages
21,358
here's a function I use and an example of filtering with a multiselect listbox.

There's an old version of my function in the example in post #12 which should give you an idea what the function arguments do.
Hi. Welcome to AWF!

You might want to start your own thread.

Also, take a look at this article. You should be able to use the same technique.
 

abelmumo

New member
Local time
Today, 11:51
Joined
Jan 26, 2024
Messages
7
here's a function I use and an example of filtering with a multiselect listbox.

There's an old version of my function in the example in post #12 which should give you an idea what the function arguments do.
Thank you very much.

I am taking a look at it and I think I am starting to understand a few things that might help me...

I believe the biggest problem that the database which I need to work on has, is that no numeric primary keys are given to the some tables... Unfortunately I cannot change that, since it is a database already created and used by many people, and corresponds to an official model given by an institution. I might have the opportunity to replicate this database in the future by creating it from scratch again with the same relationships between tables but made through numeric primary keys (which I believe might solve many daily headaches), but at the moment I cannot afford that.

Therefore, when I manage to create a string with the alphanumeric codes acting as keys, and then I try to use that string as filtering criteria on a query, Access is understanding that it needs to find the whole string instead of the alphanumeric codes it contains. Here it is an example:
1706516057841.png

You can see that the items have been selected and delimited by ";" (which is the separator used in the "in()" function in Spanish version of Access I work with (I have also tried to use "," as separator). However, the query "Subprograma" which is filtering the results using that string, does not give any result when multiple selections are made. It works properly when selecting an unique element:
1706516369317.png


I will try to understand in deep your work and see if that helps me solve this issue.
I will be very pleased to receive any feedback or advice, at this point any help is wellcomed.

Best regards.
 

Users who are viewing this thread

Top Bottom