469,328 Members | 1,324 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

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

Seth Schrock
2,962 Expert 2GB
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?
Apr 13 '11 #1

✓ answered by 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

4 29628
beacon
579 512MB
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
Apr 14 '11 #2
Seth Schrock
2,962 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSelectStatus_AfterUpdate()
  2.     DoCmd.OpenQuery "qryPeopleStatus", acViewNormal, acEdit
  3.     DoCmd.OpenReport "People", acViewReport
  4.     DoCmd.Close acQuery, "qryPeopleStatus"
  5.     DoCmd.Close acForm, "frmReportbyStatus"
  6. 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.
May 9 '11 #3
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
Nov 1 '15 #4
zmbd
5,400 Expert Mod 4TB
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
Nov 2 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Andrew Meador | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.