Code to generate reports filtered on multi-select list box

[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

 

[Ken Snell]

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.

 

[pringb]

thanks - got it to work now - ;