How do I open a report based on a ComboBox selection?

[Seth Schrock]

I have a database that is keeping track of returned mail. Each piece of returned mail has a status. I want to be able to view reports for all the returned mail in each status. I could create a button for each, but I would rather select the report from a ComboBox or ListBox. I have the combobox getting its values from a statuses query. I don't know if this is too complicated, but I would like for it to work like this: Select a status from the combobox, click a submit button or run something in the AfterUpdate event that creates a query for just the status that I selected and then have it build a report from the query. The report would be built off of a template. I vagely remember something about being able to create a query that asked for a selection criteria and then ran the query. Is it possible to do something like this and have the criteria be assigned by the combobox?

[beacon]

Hi Seth,

Take a look at this tutorial: http://www.fontstuff.com/access/acctut08.htm

You can use the general idea to accomplish what you've set out to do. If different selections on your form will require different report "templates", you'll just use a conditional statement (like If...Then...Else, or Select Case) to determine which report to call, and then call it.

You could build a query on demand, but I've found it's easier to consider the possibilities for each item in the combo box and try to create an individual query for each...unless, of course, you want to use the same query and just filter the results based on the parameter passed from the form (which is what the tutorial explains).

Hope this helps,
beacon

[Seth Schrock]

This was exactly what I was looking for. It told me how to create the report that would pull its criteria from a combo box by putting this in the criteria field:[Forms]![frmReportbyStatus]![cboSelectStatus]. I then created a report that was based on the query. Here is the code that I ended up with:

Private Sub cboSelectStatus_AfterUpdate()
    DoCmd.OpenQuery "qryPeopleStatus", acViewNormal, acEdit
    DoCmd.OpenReport "People", acViewReport
    DoCmd.Close acQuery, "qryPeopleStatus"
    DoCmd.Close acForm, "frmReportbyStatus"
End Sub

In the After Update event of the combo box, it runs the query and then opens the report. The report is the populized with the correct information. Then you close the query (which will be left open if you don't do this) and then close the dialog box. Works like a charm.

[Edgarc1981]

Sorry, I am relatively new to this. Would this code not just always pull the report names and query names you have within it? What if you have two or three different reports to pull from the combo box, wouldn't the Code always pull the same report?

I guess I am missing how the criteria code decides the query to be generated and then the report.

Thanks, this has been helpful!


@Seth Schrock

[zmbd]

Edgarc1981: You need to follow the link in Post#2 and follow the tutorial.

In summary:
+ The same query is called; however, the criteria changes based on the control's value.... say we're looking for blue t-shirts for a sales report one time and a red t-shirts at a different time. Same query, different criteria for the search.

+ The same report that uses the afore mentioned query is called each time; however, one time it's reporting for the Blue t-shirts and the other time for Red.

... instead of colours and t-shirts, Seth is using the status of the mail. Same thing... just different names.


One does not need to create a new report for each instance nor does one need to create a new query for each instance, that is what the parameter is for as mentioned in the tutorial.

Follow the tutorial. If you need further clarification please start a new thread referring back to this thread for context