0

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?

  • Please clarify what you mean by "report" and "template". Do you expect this information to appear on screen? Or do you want a printable report to be generated? (If so, do you also need to print the "empty template"?) Access Forms and Reports can be used separately or together and they have similar design elements, but they have different purposes. A useful, complete answer will depend on what the final purpose is. Perhaps you'll want both a dynamic form and a printable report, but you need to define the requirements so that you can determine which is appropriate. – C Perkins Jan 9 '18 at 4:42
  • Thanks for the thoughtful question. I want to have a printable report that can show trends in the form of charts as well as data about the vendor. I will never at any point need the empty template. Maybe a form / report combo is the best bet but I'm not sure how they would best be used together. By report I mean I've been using the report object but it could be anything that displays data pleasantly. By template I mean the queries and data displays (like text boxes) are set up already, but the data has yet to be determined. – EmRoBeau Jan 9 '18 at 16:38
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.