Passing parameters to report

[Shidlowsky]

I've been asked to fix someone's form & report. The form lets users select a person or persons, then print a report for all selections. However, the report that's used is based on a query that prompts for the person's name. Can I pass the name (or names) as a paremeter so the prompt no longer appears?

Howard Shidlowsky

 

[JimHorton]

Howard,
You can't send parameters in the way you want to, but you can just adjust the query and either open the report with a WhereCondition in the docmd.openReport call. If it's a list of names (ie, the form has a multi-select listbox for selecting names), just make the WhereCondition be "Name IN(" & strCommaDelimitedList & &quot".
To get the strCommaDelimitedList, just loop through the Selected items of the listbox and build the comma-delimited list into that variable.
--Jim

 

[Shidlowsky]

Thanks Jim.
That's what I thought I would have to do.
I thought I'd try passing a parameter from the form to a test report first to make sure I could do it. I use the first employee selected & send it to the report (which I have open) using Forms![myformname]![textboxname] so the code looks like
Forms![myformname]![textboxname]= MyListBox.ItemData(intCnt)
The report isn't getting the value but it isn't complaining either. Any ideas?

Howard

 

[JimHorton]

Howard,
You can't send anything to the report once it's open. You can filter to refer back to the form, though.
--Jim

 

[Shidlowsky]

Jim,

Does that mean I have to code the report to start the form instead of the other way around? Or do I refer to the items I selected? Do I have to store them in a global variable & use that?

Ideally, I'd select employee in the form, click on a Preview or Print button & have my code dispaly or print report with data for selected employee. Is that not possible in Access? Can't I use something like like
DoCmd.OpenReport stDocName, acPreview, , "Reports![test]![Emp] = MyListBox.ItemData(intCnt)"

Howard

 

[JimHorton]

Howard,
Your example was close, it'd be
DoCmd.OpenReport stDocName, acPreview, , "[Emp] =
MyListBox.ItemData(intCnt)"

You just remove the reports![test]... This will work for a single selection. To make it work for multiple, just do a loop prior to the docmd.openreport, and build a comma-delimited list into a variable, and in the Where section of docmd, say
"[emp] IN(" & strMyList & &quot"

If I've understood you correctly, that should work.
--Jim

 

[Shidlowsky]

Jim,

I guess my example was close but not close enough
The report still doesn't get the employee name. FYI, [Emp] is an unbound text box, which I assume is correct.

Howard