Using Combo Boxes to generate Reports

Topic

New Reply

This is one of the more difficult programming things I have attempted, and since I am asking for help it means I haven’t figured the stupid thing out.

Here are the Details:

1. I have 3 Reports
2. I have a work facility of 27 different sections for which the database is tracking information

Without having to create 81 distinct reports, or create parameter queries where the user must type in their section name “perfectly” to get it to work, I would like to try and do the following:

Create a pop-up form. Place 2 combo boxes on it. One with the 27 section names in it. The other with the 3 Report names (which may go to 6). The user would then select their section and what type of report they want, press an OK button and “poof”, a great report.

I would even like to to add a fancy feature of putting (2) date range boxes on the pop-up form so they can search by date range.

Putting fantasy into reality would be great, but as I stated earlier I am struggling with how to get the section combo box to work like a “filter” while designating which report to send the filtered query information to.

As always, I do appreciate the help I receive. Thanks.

Reply | Quote

Replies

This could be done a couple of ways. I’d first try a parameter query which would reference the control values on your popup form in the criteria row or where clause of the queries on which your reports are based. Once the form is created use the expression builder to get the correct syntax for referring to the form controls in your queries. As for the date range you can use the Between…And expression in the criteria row or where clause as well.

Select * From tblWorkFlow
Where [FacilityID] = [Forms]![FromName]![FormControlName] AND [ProductionDate] Between [Forms]![FormName]![Date1ControlName] And [Forms]![FormName]![Date2ControlName]

As for opening the selected report in the click event of your “OK” button use the openform method and substitute the control name for the report name argument:

DoCmd.OpenReport Me.ControlName

Reply | Quote

I just got through downloading a database from Helen Feddema’s site that does just that. It was code26.zip under the Code Samples section for Access.

Reply | Quote

Judy,

Thanks. The sample database provided just want I needed.

Reply | Quote

I set up a system that, although it doesn’t use any fancy VBA, is quite efficient. It uses a table with a field for each potential criteria for generating a report. A form links to this table, allowing the one and only record in the table to be edited, but no record can be added or deleted. The values for the fields are restricted to values in a combo box; the values for the combo boxes come from data tables containing the possible choices. The user opens the form, selects the criteria, and clicks a button on the form to continue processing. The button closes the form and opens the report. The query that generates the reports pulls the values from this table as criteria. Close the report, and the user returns to the input decision form.

To adapt this to your needs, I’d set up a form that includes a field for Section, with three buttons below. The user chooses a section, and clicks one of the three “Report Type” buttons, which would then fire off the desired report. Use two input forms, and you could designate one as a “Global” report, and the other would allow for date selection.

Think of it like a custom “switchboard”, moving between levels. First form: “Generate report for all dates, or a range of dates?” Second form: [2a] “Choose Section & Report Type” or [2b] “Choose Section, Date Range, & Report Type” depending on the user’s first selection.

We only work with two reports, so it was easier to set up different forms/buttons for each report. The potential is also there to include a report number/description field, and have a report-generating macro read this field in as a “Condition” to the macro. Based on the value of this field, different reports would or would not be generated.

Your “fantasy” is currently being installed as our reality at a number of our locations — designed for non-Access users to use with ease. I think your reality can’t be too far off either.

(One additional benefit of this setup is that criteria do not need to re-entered if different reports are to be run for the same Section, since the criteria values are actually stored in a table and can be referenced repeatedly without re-input.)

Reply | Quote

Hi David,

This may be a little off the subject, but I really like your idea of storing the chosen criteria in a table for later use! I use forms for user input to filter reports all of the time, but it never occurred to me to store it for later use…WOW! Ya’ learn something new every day!
Many thanks

Reply | Quote

For multiple reports generated on the same basic set of variable criteria, it works like a charm. Also, if you have a date-selection function, you can keep one set of criteria while shifting dates… Conversely, you can keep the dates static and shift other variables to run a series of “Division” reports for the same time period.

Reply | Quote