Dyanmic report based on user input using 5 variables...

[Swiftraven]

Hi,

I have a form that has 5 input boxes that are used to search my records. The user can use any combination of the 5 boxes and will get the records that meet those conditions (ie. they select Hardware for the type and 777000 for the partnumber, they will get all instances that correspond to these criteria).
The report I have now was created using the wizard. It shows the data for the entire record but shows alot of redundant data (the type field would be hardware and partnumber 777000 for every record listed). What I am needing to do is base the report on the users selection and to put the fields that they input at the top of the report and just fill in the rest of the data fields for all of the records. Like

777000
Hardware

IncidentNumber Problem etc
1 broken ....
2 whatever ...

If they then bring up the search form again they could select any number of fields up to all 5 and have those criteria be put at the top and the rest of the data fill in the report.

This has got me stumped and I was wondering if anyone could head me in the right direction.

Thanks for the help

Jason

 

[Lozere]

Simple but needs to use VBA procedures:

1) In an independant module, create 5 Public variables for your 5 criteria with their appropriate data type.

2) In your form, before to launch the Report, transfer the value of the input boxes to the public variables

3) still after to launch the report, create a Where condition from the contents of your input boxes.
With statements like:
Dim Criteria as string
.....
Criteria="Field1_Name=" & InputBox
Criteria=Criteria & " AND Field2_Name=" & InputBox2
and so on.
Of course, when some boxe are optional you have to use IF condition.

4) then launch the report:
MsgBox &quot;<&quot; & Criteria & &quot;>&quot; ,,&quot;Where Condition&quot;
DoCmd.OpenReport &quot;My_Report&quot;,,,Criteria

Last tuning, add in the Format event procedure of the section where you print the value of the conditions some code to get the value of these condition from the public variables and put them in text boxes