Code to generate reports filtered on multi-select list box

  • Thread starter Thread starter pringb
  • Start date Start date
P

pringb

Hi,

I've got a form with 2 objects, one is a list of months, the other is
multi-select list box.

eg.X (Month)

1,2,3

eg.Y (Multi-select List Box)
a,b,c

When the user select value X and mulitple values from Y I want t
generate a separate filtered report for each multi-select item and ope
it eg . Rep1 filtered on Y-a,X-1 and rep2 filtered on Y-c,X-1,etc..

This is my code so far !

Dim varLoop As Variant

For Each varLoop In Y.ItemsSelected
strLinkCriteria = "([ReportFilterFieldY]) IN Me.Y.ItemData(varItem)
AND ([ReportFilterFieldX]) IN (Forms!Formname!X)"
DoCmd.OpenReport "Reportname", acViewPreview, , strLinkCriteria
Next varLoop
End If

The code falls to pieces where I've got "IN Me.Y.ItemData(varItem)" -
think I've got the right idea but can't get the syntax right.

Any help would be much appreciated

Bruc
 
One problem that you have is trying to use variables inside the text string
for strLinkCriteria. You must concatenate the actual values from those
variables, not the variables.

For example:
strLinkCriteria = "([ReportFilterFieldY]) IN " &
Me.Y.ItemData(varItem) & " AND ([ReportFilterFieldX]) IN (" &
Forms!Formname!X & ")"

Second problem I see is that you're using varLoop as the looping parameters,
but the code references a varItem.
 
Back
Top