2 questions on multi select list parameters

[pofe333]

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.

 

[pbaldy]

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

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

 

[CraigDolphin]

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.

 

[pofe333]

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?

 

[pofe333]

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.

 

[pbaldy]

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.

 

[CraigDolphin]

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.

 

[pofe333]

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?

 

[boblarson]

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

 

[pbaldy]

"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.

 

[pofe333]

"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.

 

[pbaldy]

Did you take the CostCenter criteria out of the query?

 

[pofe333]

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 & ")"

 

[pofe333]

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.

 

[pbaldy]

Can you post the db?

 

[pofe333]

Can you post the db?

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

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.

 

[CraigDolphin]

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

 

[pofe333]

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.

 

[pbaldy]

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.

 

[pofe333]

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