Prompt Access users for reporting information

Here's a quick and easy way to get information into an Access report at runtime -- without code.

A parameter query prompts a user to enter a criteria value, such as a date or a name, that the query then uses to filter data. A similar technique can be used to include specific information in a report at runtime. It's not the same as filtering the data; rather, you can include data that changes. For instance, let's suppose you want the name of the person who's preparing or running a report to appear on the report. Now, there are a number of ways you could do this, but perhaps the simplest method is to prompt the user to enter his or her name. Using the Order Details report in Northwind, the sample database that comes with Access, here's how to prompt a user for information when opening (running) a report:

  1. Open the report in Design view.
  2. Add a text box for storing and displaying the input value. In this case, the footer's a nice place.
  3. Enter appropriate label text, such as Report Repaired By:.
  4. In the actual text box, enter a simple expression in the following form:


    where prompttext is the text the user will read and respond to. I've entered Please enter your name.

  5. Make adjustments to the new control -- center it, change the font, and so on.
  6. Save and close the report.

When a user opens the report, it will display the prompt shown below. To continue, the user must enter his or her name and click OK. The report footer will then display the name the user entered. You don't have to respond to the prompt. If you ignore it and click OK, the report works just fine, but the input control will be empty.

Keep in mind that there's no way to validate the input value. If a user misspells something, enters a typo, or just responds inappropriately in some way, it will show up that way on the report. With ease comes a bit of risk, so be careful where you use this technique.