complex code for a form (1 Viewer)

[Jade]

I have a problem with writing code for multiple selections of check boxes to produce a single report. For example, the person selects sick, vacation, holiday, I want to produce a report to show all of the options selected with the times. There are 15 options to choose from. Someone told me my only option is to have queries for every possilbe choice. Is there an easier way?

 

[Jon Holmes]

Jade,

That someone was wrong. If those 15 choices are all in a multiple select listbox then loop through all the selections and place their values into an In statement. For example, lets say your report field is called Leave and the lookup table for leave contains options 1;Sick;2;Holiday;3;Long Service etc
Initialise your string (eg. Dim strFilter As String) to "Leave IN(" and then loop through the listbox selections.(see the Help Topic "ItemsSelected collection" for more information).
Inside the loop place a statement like strFilter = strFilter & ctl.ItemData(varItm) & ", "
Once all the values have been read, you then tidy up the string with a line like,
strFilter = Left$(strFilter, Len(strFilter) - 2) & ")"
This gets rid of the last comma and you're left with a statement like
strFilter = Leave In(1, 3, 7, 8)
When you open the report, pass this value as the Where clause.

Jon