Creating a report that fills in when the user makes a data selection from a combobox?

Question

I want to make a report that acts as an empty template, but when the user makes a selection from a combo box or pop up window, the template fills with all of that data related to that primary key.

For example, my database stores information about purchases. I want the user to be able to select a vendor from a drop down list and see all the pertinent information such as how much money we spend with them, how long they have been an active vendor, and charts that show trends over time.

How can this be done if the vendor name is a primary key in the table?

Answer 1

There are many ways to filter and produce reports based on user input within Access. Access is flexible enough that to explain even one approach can either require entire tutorials or be too specific to be generally useful. A lazy response would be to simply mark this question as too broad, but similar questions have been asked repeatedly on Stack Exchange forums (i.e. Stack Overflow), but with incomplete answers, so I think it justifies at least some direction here.

First, search the web for something like "access report from combobox" and you should get links to various websites and video tutorials that have plenty of examples and hints.

A vary loose outline of how I would go about this:

• The general technique should be to base the report on a query that references a ComboBox control on a Form.
  • For example, a Form named Vendor Detail with a ComboBox control named Vendor Name could be referred to in the Report query like ... WHERE MyTable.Vendor = [Forms]![Vendor Detail]![Vendor Name] (This is of course only a snippet of the complete query SQL.)
  • DO NOT place the ComboBox on the report itself, even though Access will allow you to do this. Such attempts will likely lead to unexpected, buggy behavior.
  • This technique requires that the Form remain open with a valid value selected in the ComboBox. If the Report were to be opened directly without the Form, it would display an "ugly" prompt asking for the [Forms]![Vendor Detail]![Vendor Name] value. You could optionally type in a value and continue producing the report, but that is not very user friendly.
• A Form can include a button--alongside the ComboBox--that opens and/or refreshes the report when a different Vendor is selected.
  • The button event can execute either a Macro or a VBA code to open the report. The details for this are beyond the scope of this answer, but can be found by searching the web and/or Stack Overflow.
  • The report can either be displayed on screen as a preview before printing, or it can be printed directly.

The other details of the report creation or the query it is based on are also beyond the scope of this answer, but there are no other special requirements, so all other aspects can be handled like any other Access Report.