How to enable multi select listbox selections to filter report

[vent]

Hi all

So previously I had a form with a combo box that when the user selected an item, a report would open displaying all the records that carried that field item (in this case, agencies that use a particular program code). It worked nicely, however now I need to allow the user to make multiple selections and I know a combo box doesn't allow multiselections however I added a listbox and it's record source is the query that links the two tables together (tblAgencyInformationNew and tblProgramCodes).

SELECT DISTINCT qryAgencyProgramCodes.ProgramCode, qryAgencyProgramCodes.ProgramName
FROM qryAgencyProgramCodes
ORDER BY qryAgencyProgramCodes.[ProgramCode];

I enabled the multi select property to simple on the list box but my question now is, does anyone know the vba or can point me to some good examples of how this can be accomplished? I was told that an IN CLAUSE could be of use and looking at examples but so far nothing has really helped. You guys are great and any feedback is appreciated!

 

[Ranman256]

multi select is the hard way to do this. It requires programming.
The easier way is to use single select, dbl-click it,
this runs an append query to add the item to a 'picked list' table., tPicked.

then just join the tPicked table to the data table and you only get THOSE items.
NO PROGRAMMING.
see photo:

 

[arnelgp]

code snippet to get all selected items
in listbox:

...
...
Dim var As Variant
Dim strReturn As String
For Each var In Me.Listbox0.ItemsSelected
strReturn = strReturn & Me.Listbox0.ItemData(var) & ","
Next
If Len(strReturn) > 0 Then strReturn = "fieldName In (" & Left(strReturn, Len(strReturn) - 1) & ")"


'****
fieldName is the field you want to filter

then you pass the strReturn as Where condition of your Report.

docmd.OpenReport "yourReport",,,strReturn

 

[vent]

multi select is the hard way to do this. It requires programming.
The easier way is to use single select, dbl-click it,
this runs an append query to add the item to a 'picked list' table., tPicked.

then just join the tPicked table to the data table and you only get THOSE items.
NO PROGRAMMING.
see photo:

View attachment 68153

Hi thanks for reply. I like this approach however, with an append query aren't I basically adding the same records to the table? Do I turn the AgencyProgramCodes query into an append query?

 

[vent]

code snippet to get all selected items
in listbox:

...
...
Dim var As Variant
Dim strReturn As String
For Each var In Me.Listbox0.ItemsSelected
strReturn = strReturn & Me.Listbox0.ItemData(var) & ","
Next
If Len(strReturn) > 0 Then strReturn = "fieldName In (" & Left(strReturn, Len(strReturn) - 1) & ")"


'****
fieldName is the field you want to filter

then you pass the strReturn as Where condition of your Report.

docmd.OpenReport "yourReport",,,strReturn

Hi thank you for your code but when I tried using it, the report automatically turn into pdf. Which might be helpful later but for now i just need for report to open with all selected records.

 

[pbaldy]

Does this help?

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

 

[vent]

Does this help?

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

Very much however the last part:

DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ")"

I'm having a bit of trouble with, not sure what I should replace EmpID with. I tried CodeID because that was in the query, but i keep getting this


Enter Parameter Value
CodeID
________________________|


I will continue to play around with it. Thank you.

 

[pbaldy]

You'd use the name of the field in the report's source query that you want the criteria applied to.

 

[vent]

You'd use the name of the field in the report's source query that you want the criteria applied to.

I tried using ProgramCode but I got an error saying:


Data type mismatch in criteria expression

 

[pbaldy]

Did you use the appropriate line within the loop? One was for numeric, the other for text. Can you attach the db here?

 

[vent]

Did you use the appropriate line within the loop? One was for numeric, the other for text. Can you attach the db here?

Sorry not sure what this means exactly but I removed the second comma after acPreview, and put "ProgramCode IN(" & strWhere & ")"

And I'm not sure I can because there are over 2000 records and may be sensitive data.

 

[pbaldy]

No, the commas need to be as they are. I meant to use this line if the data type of ProgramCode is Text:

strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

The other "strWhere = ..." is used for numeric data types.

 

[vent]

Yes, ProgramCode is text field. So just to be sure.

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.lstProgramCodes.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 program code"
Exit Sub
End If

'add selected values to string
Set ctl = Me.lstProgramCodes
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & "," << this is text
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)<< this is numeric?
'open the report, restricted to the selected items
DoCmd.OpenReport "rptAgencyProgramCodes", acPreview, , "ProgramCode IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

Is this is true should I simply comment out the numeric line?

 

[pbaldy]

No, I was talking about the two lines within the loop. Try

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.lstProgramCodes.ItemsSelected.Count = 0 Then
  MsgBox "Must select at least 1 employee"
  Exit Sub
End If

'add selected values to string
Set ctl = Me.lstProgramCodes
For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"  
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "rptAgencyProgramCodes", acPreview, , "ProgramCode IN(" & strWhere & ")"

 

[vent]

No, I was talking about the two lines within the loop. Try

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.lstProgramCodes.ItemsSelected.Count = 0 Then
  MsgBox "Must select at least 1 employee"
  Exit Sub
End If

'add selected values to string
Set ctl = Me.lstProgramCodes
For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"  
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "rptAgencyProgramCodes", acPreview, , "ProgramCode IN(" & strWhere & ")"

This almost worked. I get a filtered report at least, however if I make more than one selection, I only get one selection and not the others.

 

[pbaldy]

Add

Debug.Print strWhere

right before the OpenReport line and check the Immediate window for what it contains when you select multiple items:

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

 

[vent]

Add

Debug.Print strWhere

right before the OpenReport line and check the Immediate window for what it contains when you select multiple items:

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

In the Immediate window is this:

'0869', '1077'

The selections I made

 

[pbaldy]

That should work. Records exist for both selections? I understand if you can't attach the real db; is a representative sample possible?

 

[vent]

That should work. Records exist for both selections? I understand if you can't attach the real db; is a representative sample possible?

Sure thing, i'll upload one shortly

 

[vent]

That should work. Records exist for both selections? I understand if you can't attach the real db; is a representative sample possible?

So I implemented your code onto this sample database and it worked. If i selected say 2020 and 1212, the report showed up as:

Agency One 2020
Agency Two 2020
Agency Three 1212
Agency Four 1212
Agency Five 1212

Now I'm guessing it has to do with the report itself. Since there's over 2000 records, I'm guessing the first selection of records are visible but for some reason, I'm not seeing the second selection of records. Thoughts?