User Selects Data for Report

[Holly_Associated]

Hi Experts,

I have come across something new to me and wanted some advice on how to proceed and what is the "accepted" version of events.

I have created a report, a dynamic method statement actually, and want my user to be able select some data to appear in the report. Let me explain...

Report is rptMS01, it is fed from a query and has some fields to automatically populate [Company], [Site], [Postcode] etc. All of the static text is in CanGrow textboxes to sidestep the report height limit. The report is opened by a button on a form which uses a macro to open only for the current site. All OK so far.

My boss would like a section/text box/subreport to select the personnel involved in each method statement and this to appear on the report.
So, on clicking the button on the form which would usually open the report directly, some kind of intermediary selection form opens instead with a list of all personnel. User can then select which personnel to involve and on clicking OK this appears in the designated box on the report. A CanGrow/Shrink textbox would be ideal!

So, how do I go about this? Do I need to include this in the current query which feeds the report? I know a listbox can have multiple selections but how would I reference this? How do I keep the other fields as well? Should I create a table with the personnel list?...

This is very complex for me so any help is appreciated and please explain as if talking to a three year old!

 

[plog]

My boss would like a section/text box/subreport to select the personnel involved in each method statement and this to appear on the report

This doesn't sound like a report issue, but a data capture issue. The way you describe it you don't want to save those selections but just display them, why? Wouldn't it better to save those selections to a table and then just use them in a sub-report to that report you are running? Otherwise, to rerun this report he would have to redo those selections.

 

[Holly_Associated]

Hi plog,

Yes, you are right. It has been going around in my head since I typed the problem out earlier.

I will work on this from the table angle and post my results. All of the reports (i.e MS01, MS02 etc.) request the same fields but have different static text so it will just be a case of the user selecting which report they want to put the data into rather than what data they want to put in the report... Yay, I like the "light bulb" moments, thank you!