Hello,

I need major help with multi select list boxes. I've researched for hours, but am stuck on two things.

#1 - One of my queries (that leads to a report) is a combination of fields from two related tables. For some reason, the multi select box on the form I created always returns null values for the query when placed in the criteria of a field from the related table. Is this normal?

#2 - I am not familiar with code and can't seem to integrate this list to filter my report. I've gathered I should be creating a button on the form with an On Click event, it's the creating of the code for the event that I have trouble with. What is the friendliest way for a non-programmer such as myself?

Thanks to anyone that can provide some help.

Yes, a multiselect listbox always returns Null. See if this helps:

http://www.baldyweb.com/multiselect.htm

I think the only method to use multiselect list boxes as criteria in a query is to build the SQL string using vba to loop throught the listbox items, and if the item is selected, then add this to a comma-separated list which is used in conjunction with the 'In' operator.

So the final sql for a simple select query would be something like

Select *
From tblName
Where (tblName.TextFieldName In ('selectedvalue1','selectedvalue2','selectedvalue3 '));

There are several examples showing this appraoch on the forum if you search for them.
http://www.access-programmers.co.uk/forums/showthread.php?t=70196
http://www.access-programmers.co.uk/forums/showthread.php?t=45947
http://www.access-programmers.co.uk/forums/showthread.php?t=52946

And also see...
http://www.mvps.org/access/forms/frm0007.htm

So if you absolutely do not want to code then I think you're stuck.

Yes, a multiselect listbox always returns Null. See if this helps:

http://www.baldyweb.com/multiselect.htm

I'm sorry, let me clarify that 1st question a little. I tested this with two queries. If I select just one entry from the multi select list, and use that as a criteria for a query that pulls from one table, it works. I know it won't work once I select multiple values without coding. But still testing, if I select one entry from the list and use it as a criteria in a query that pulls data from two releated tables instead of one, it no longer works. That leads me to believe that it may not work on queries combining fields from different tables. Is this correct?

I think the only method to use multiselect list boxes as criteria in a query is to build the SQL string using vba to loop throught the listbox items, and if the item is selected, then add this to a comma-separated list which is used in conjunction with the 'In' operator.

So the final sql for a simple select query would be something like

Select *
From tblName
Where (tblName.TextFieldName In ('selectedvalue1','selectedvalue2','selectedvalue3 ');

There are several examples showing this appraoch on the forum if you search for them.
http://www.access-programmers.co.uk/forums/showthread.php?t=70196
http://www.access-programmers.co.uk/forums/showthread.php?t=45947
http://www.access-programmers.co.uk/forums/showthread.php?t=52946

And also see...
http://www.mvps.org/access/forms/frm0007.htm

So if you absolutely do not want to code then I think you're stuck.

Craig.

In the above code, would I need to fill in the 'selectedvalue1' in the SQL manually with my choices in the list, or is this where the looped code would fill it in automatically?

My goal is to create reports from a query, filtered by what's in the multi select list.

My goal is to create reports from a query, filtered by what's in the multi select list.

Did you look at my sample? That's exactly what it does.

The code would build the string dynamically based on what you selected in the listbox....and if you look at the example Paul linked, that's likely exactly what happens.

Did you look at my sample? That's exactly what it does.

I did, but here's where I get lost on due to lack of coding knowledge:

If Me.lstEmployees.ItemsSelected.Count = 0 Then

What is the 'Me'in that line. I see '1stEmployees' is the list name on the form. Then it looks like I only need to switch the report name to open and the field of the query I want it to filter, correct?

What is the 'Me'in that line.
Here - from my glossary on my web page:

This is a programming shortcut which lets the programmer refer to the current CLASS object. Most of the time, in Access, you would be referring to a form or report using this. It can also apply to custom classes as well, but is less prevalent unless custom classes are being used.

Let's say you have a form named "frmMain" and you have a text box there called "txtEntry" and you wanted to refer to that text box in code. A fully-qualified reference would be Forms!frmMain.txtEntry but you could also refer to it using the shortcut (as long as the code is ON frmMain): Me.txtEntry

"Me" is a shortcut used in VBA code to refer to the object containing the code. Thus, in code behind a form, Me refers to that form.

Yes, you should be able to change the report, listbox and field names to yours. You would take this criteria out of the query, as this code is doing it instead.

"Me" is a shortcut used in VBA code to refer to the object containing the code. Thus, in code behind a form, Me refers to that form.

Yes, you should be able to change the report, listbox and field names to yours. You would take this criteria out of the query, as this code is doing it instead.

Almost there now! Thanks to Paul, Craig, and Bob. I am getting an error when running the report though. My query is based on a date criteria. When I click my run report button, the date criteria pops up as usual (2 popups, one for start and one for end), and then a criteria box pops up for the CostCenter (which are in my multi select list box.) It doesn't seem as though this code is picking up my selection in the list properly.

Did you take the CostCenter criteria out of the query?

Did you take the CostCenter criteria out of the query?

Yeah, that was the first thing I checked. Debug returns the error on the last line of code:

DoCmd.OpenReport "rptSummary", acPreview, , "CostCenter IN(" & strWhere & ")"

This may be something I did. I had a field called Cost Center instead of it being CostCenter, and when I changed it this started popping up when I run the report. I must be missing the field being updated to the new heading somewhere.

Can you post the db?

Can you post the db?

I would be embarrased for anyone to look at this awful excuse for a databse :D

Apart from that though, it does have some company information and I haven't set any security settings since I'm the only one currently using it. I'm pretty sure it's due to this Cost Center field though. I can't leave it as Cost Center because when it runs the last line of code I get a missing operator error. If I change it Cost_Center or CostCenter, I seem to be missing somewhere along the line a field that is still looking for Cost Center.

Try surrounding all references to 'Cost Center' with '[Cost Center]' (without the quotes)

Try surrounding all references to 'Cost Center' with '[Cost Center]' (without the quotes)

Spectacular! That got me past the parameter popup. Now I just need to figure out why my report shows #Error instead of results.

I can understand about confidential info. Make sure to check the fields in the report, including Sorting and Grouping, which is where these things often hide.

I can understand about confidential info. Make sure to check the fields in the report, including Sorting and Grouping, which is where these things often hide.

I suspect you're exactly right on it being somewhere in the sorting and grouping; I didn't even look there. My reports work without the form, but when I run the form I get no data. Would youlook at my code and see if I'm forgetting something:

Private Sub cmdReport_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

If Me.lstCostCenter.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Cost Center"
Exit Sub
End If

Set ctl = Me.lstCostCenter
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

strWhere = Left(strWhere, Len(strWhere) - 1)

DoCmd.OpenReport "rptSummary", acPreview, , "[Cost Center] IN(" & strWhere & ")"
End Sub

As a suggestion: you could make a copy of the db to share, and remove all real data and repopulate with some fake data enough to demonstrate the issue. Sometimes it's a lot easier to delve in hands-on than play 20 questions ;)

Add this right before the OpenReport line:

Debug.Print strWhere

which will print the contents of that variable to the VBA Immediate window. If you don't see the problem, post the results of that here along with the data type of [Cost Center].

Change this:

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

to this:


strWhere = "[Cost Center] In ("

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "', "
Next varItem
strWhere = Left(strWhere, Len(strWhere)-2 & ")"


And then Change this:

DoCmd.OpenReport "rptSummary", acPreview, , "[Cost Center] IN(" & strWhere & ")"

to this:

DoCmd.OpenReport "rptSummary", acPreview, , strWhere

As a suggestion: you could make a copy of the db to share, and remove all real data and repopulate with some fake data enough to demonstrate the issue. Sometimes it's a lot easier to delve in hands-on than play 20 questions ;)

That's a great idea. I may do that if I can't get this nailed down pretty quickly.

Add this right before the OpenReport line:

Debug.Print strWhere

which will print the contents of that variable to the VBA Immediate window. If you don't see the problem, post the results of that here along with the data type of [Cost Center].

I will try this in a few moments along with Bob's suggestion next.

Change this:

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

to this:


strWhere = "[Cost Center] In ("

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "', "
Next varItem
strWhere = Left(strWhere, Len(strWhere)-2 & ")"


And then Change this:

DoCmd.OpenReport "rptSummary", acPreview, , "[Cost Center] IN(" & strWhere & ")"

to this:

DoCmd.OpenReport "rptSummary", acPreview, , strWhere

Bob, I get an error on strWhere = Left(strWhere, Len(strWhere)-2 & ")" above. It says expecting operator in the code builder. If I put a parenthesis on it the error when running the command says mismatch. I'll work on creating a copy of the db with sensitive info removed to post next week.

sorry, forgot a paren:

strWhere = Left(strWhere, Len(strWhere))-2 & ")"

sorry, forgot a paren:

strWhere = Left(strWhere, Len(strWhere))-2 & ")"

I get Runtime Error 13, Type mismatch.

I'm think I'm going to have to post up my db. I've got a buddy who works with code, so I'll see if he might be able to look at it this weekend. All 3 of you have gotten me way beyond where I was and I appreciate it very much! I'll check back after the weekend if I'm still stuck.

Go ahead and post it. If Cost Center is numeric then you would want to leave off the quotes during the loop for the list box.