Query Criteria based on Multi Selection of a List Box

[gheyman]

Can a Query Criteria based on Multi Selection of a List Box?

Form = frmCustomerRecord
My List Box = "ListCustomer"

 

[stumac]

it can't do it directly, you need to build the query string in vba.

 

[gheyman]

Any help with that?

 

[btorrean]

Can you post some of the code you have already? That may help us provide you with a possible answer.

Brian

Brian J. Torreano

 

[gheyman]

I would if I had some. I'm vey New at Access VBA I asked the question if it's possible to make sure I wasn't chasing my tail.

 

[Micron]

Take a look here: https://msdn.microsoft.com/VBA/Access-VBA/articles/listbox-itemsselected-property-access
What we also don't know is which column you want to get the criteria from, although the first is the logical guess. Instead of using debug.print in your loop, as the example shows, you'll concatenate the selected field parts to string (I'd call this strWhere) and append it to the beginning part of your sql statement. Depending on your need, that statement could end like " ... IN('" and you append each piece along with a comma separator. Or you can simply append AND or OR along with the selected field parts.
Hope that helps.

 

[Micron]

Untested, but should be close to what you need to enumerate a list box. The assumption is that the IN clause would work. If not, adjustments will be needed:

Function BuildSql() 
Dim ctl As Control 
Dim varItem As Variant
Dim strCrit As String, strSql As String

Set ctl = Forms!frmCustomerRecord.ListCustomer
strCrit = ""
strSql = "SELECT SomeField, SomeField2, etc. FROM etc. WHERE SomeField IN ("

For Each varItem In ctl.ItemsSelected 
 strCrit = strCrit & ctl.ItemData(varItm) & ","
Next varItm

strCrit = Left(strCrit,Len(strCrit)-1)
strSql = strSql & strCrit & ");"

'what goes here depends on what you want to do next. 
'Make this the definition of a new or existing query?
'Run the sql? Set it to be the record source for a form or report?
'Execute the sql (if it is an Action query type)?

End Function

 

[gheyman]

Thanks! Very much appreciated. I'll give it a go in the morning

 

[Micron]

OK, but you'll notice that I flagged one of your threads in the AccessForums.net site as being cross posted. I thought you would know the etiquette around that, and answered your question here. Now I see that you posted this question there also, and thanked them for an answer. This is the sort of behaviour that will very likely curtail the responses you get. Please check this out and see why
https://www.excelguru.ca/content.php?184

 

[gheyman]

Thanks, I didnt know. Just trying to learn and thought many teachers with various views was a good way. Most times there are more than one way to tackle a problem. Will curtail double posting without letting it be known. I almost always Thank the responder which I understand closes the posting so no one continues to work it. My apologies to all