Multiselect listbox results as query criteria using a hidden textbox

[Joey C]

Hi There,

I,ve searched past postings with a similar problem, but I have not find an answer to the following:

I have a multiselect listbox, and I want to use the items selected as criteria for a query.

I use a function with code like this to generate a SQL string with the values selected:

Dim strSQL As String
strSQL = "("
With Me!ListBox
For Each varItem In .ItemsSelected
strSQL = strSQL & """" & .ItemData(varItem) & """" & ")" & " Or (IdDoctor="
Next varItem
End With

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 15)

HiddenTextBox = strSQL

The function creates a SQL string like this: “01" Or IdDoctor ="02" Or IdDoctor ="03"

I can use this SQL string to filter a form or report (if applied to the form / report filter property in the On open method.

But I can NOT use the SQL string as criteria for a query.

I have a hidden textbox (in the same form) that receives the SQL string from the function described above, and I want to use this hidden textbox as criteria in a parameter query, but the query will not accept the string if I have more than 1 item selected in the listbox.

My query looks like this:

Select [Doctors].[DoctorName]
From Doctors
Where [IdDoctor]=[HiddenTextBox]

(TextBox = "01" Or IdDoctor ="02" Or IdDoctor ="03"

I,ve also tried the IN alternative (Where IdDoctor In(“01”, “02”, “03”), But still the query won´t take the criteria. (Only works with 1 item selected)

Creating the entire query with code won´t do, because I have another query based on this one.

I found an alternative way, which involves sending the items selected in the listbox to a table, and then I use that table as a join in my query. But this method is not the best solution, since in a multiuser environment, more than 1 person could be using the listbox to write to the table.

I would like to know if it is possible to use the hidden textbox method in a parameter query, or if this is a dead end.

Thanks in advance for your help,

Joey

 

[Jack Cowley]

I have sent a demo database to your email address that I hope will assist you. It uses code to put the criteria selected into an In() statement.

 

[Joey C]

Jack:

Thank you so much!, The answer had eluded me for weeks, and I was lost in a sea of code, but your solution is perfect and above all very easy to implement.

Thanks again for sharing your knowledge .

Joey.

 

[Sevn]

Hello,

I have a similar situation, and would like to know if someone can forward me the a sample DB like the one mentioned (if available). If not; can someone guide me in the right direction.

I have a form that has many combo boxes & list boxes, and I need to have the ability to select multiple items from each list box, and pass the selections to a query's criteria. The query will then run, and report the results based on the criteria selected on the form.

The combo boxes work fine, since they have only one value to pass. This is done by simply using the combo box string via the "Expression Builder".

The list boxes appear to work differently, or I'm doing something wrong. I have the list box setup as Extended, and I performed the same procedure as I did for the combo boxes.

Query Criteria: Like [Forms]![Frm-CPMBySelection-UserEntry]![List47] & "*"

Does this need to be done with VBA, or is there a way to get what I need through the standard Access properties?

 

[gemma-the-husky]

jack will probably send you his code

but you get at select items in a listbox/combobox with

dim itm as variant
for each itm in mylistbox.selecteditems
etc
next

however, deferencing the itm to idenitfy individual columns is awkward, and a bit obscure

also, if you then need to turn those rows into a selectable query, you either need to save them in a temp table, or alternatively set checked flags in the underlying table

There may be other easier ways - i cant recollect doing exactly what you are trying to do, so I cant help further

 

[Sevn]

Hello gemma-the-husky,

I appreciate your help, but I'm not very fluent with VBA quiet yet. I can read through code, and understand what is going on (and alter, if needed). Although; I am NOT a programmer by any stretch of the imagination.

Again; I thank you for trying to help, and hopefully Jake will respond.

 

[boblarson]

Hello gemma-the-husky,

I appreciate your help, but I'm not very fluent with VBA quiet yet. I can read through code, and understand what is going on (and alter, if needed). Although; I am NOT a programmer by any stretch of the imagination.

Again; I thank you for trying to help, and hopefully Jake will respond.

Jack hasn't been on this forum for a long time. He hangs out on Utter Access now.

 

[Hala75]

Please I have the same query Can you send me the demo Dbase