Sometimes you have a form or report that is based on a parameter query. The automatic parameter window is boring and cannot be configured. You can create your own custom dialog, with a little help from some Visual Basic code or macros. This is especially helpful if the query has several parameters, but it can be useful for even one parameter.
Advantages of a Custom Dialog:
To be able to use a dialog to accept values for your parameters, you need several things:
In the parameter query, instead of a parameter with directions, like [Enter a Class (A, B, or D):], you must refer to the control on the custom dialog form, like [Forms]![ChooseAssignments]![WhichClass] where ChooseAssignments is the name of your custom dialog form and WhichClass is the name of the control where you enter or pick a value. The exclamation points in the expression are important! (When reading such expression aloud, read the ! as "bang".)
A custom dialog does not look like a normal window and usually behaves differently, too.
Appearance: The following properties on the Format tab of the Property Sheet for a form create the look of a dialog:
Behavior: Dialogs are usually popup, modal windows. These properties are on the Other tab in the Property Sheet for the form.
A popup window stays on top of all other windows until you close it. You can switch to another window, assuming that your popup window is not covering the whole screen. Some Help windows behave this way.
A modal window keeps you from switching to a different window until you close the modal window, either by clicking its Close button or by completing the choices in the window and clicking its Submit or OK button. Dialogs are often modal windows.
A modeless window (Modal = No) is an ordinary window. You can switch to other windows as you wish.
A macro is a way to do a bit of programming without having to know any programming!
Access provides a Macro window with a drop list of possible actions. At the right is a list of these actions. It is quite long! We will not be getting too deep into macros in these lessons.
Access 2007 uses the same dialog for macros as previous versions. Access 2010, 2013, and 2016 use a new look that puts the arguments for each part of the macro sequence with the step. Very helpful.
In a Property Sheet, you can assign a macro to any of the events for the object, such as OnOpen or OnClose or OnClick or OnFormat or OnPrint.
For example, you could assign a macro to the OnClick event of a command button on a form. The macro could open another form or a report or it could run an action query of some sort.
The macro in the illustration is from the Language Arts Classes database. It runs a sequence of update queries to update a table with letter grades based on scores. This macro was not attached to any other object. I just ran it directly when I needed to convert scores to letter grades at the end of each grading period.
Most actions have arguments. In Access 2007 these show in the bottom of the window when an action is selected. In Access 2010, 2013, and 2016 the arguments show in the bottom part of the entry.
For example, the action OpenQuery has 3 arguments: the name of the query to run, what view to show it in (Datasheet, Design, Print Preview, PivotTable, PivotChart), and what mode (add, edit, or read-only).
The action SendKeys is used to do automatically what you would normally do with the keyboard. This should be used only for automating keyboard responses like pressing the ENTER key or selecting a particular item on a form.
AutoExec macro: Any macro saved
with the name AutoExec will automatically
run whenever the database is opened. You could use such a macro to
immediately open a switchboard dialog or to open the form or report that you need first every day.
![]() |
Step-by-Step: Custom Dialog |
![]() |
What you will learn: | to create a select query, a simple report, and a custom form to modify a form to choose values for the Parameter query to use List Box Wizard to use the Command Button Wizard to edit a query to use form values as parameters to test query, form, report interaction to add a macro to OnOpen event of report to open form for parameters to add a macro to OnClose event of report to close hidden form to add a macro to OnClick event of command button to hide form |
Start with: , resource files, worldtravel-Lastname-Firstname.accdb from folder databases project4 as updated in the previous lesson
In a previous lesson you created and saved a parameter query, QStaff-WhatCountry. This query required you to type in the name of a country or region and the query would then produce a datasheet of staff members from that country/region. If you mistyped the country name, the query would find no records. To be able to select a country from a list of the existing countries would certainly help! You will create a form that uses a list box to show the possible countries. If the list were longer than 6 or so, you might want to use a combo box instead.
Remember, you need several parts to wind up with a report that uses a custom dialog.
You will create a simple report that will show the Staff info and takes its parameters from your custom form. You will need some macros and event procedures (programming code) to handle the form and the resulting report.
Open the new query in Design View.
The original query was designed to put the name parts together.
Problem: Field name start with Expr1: or similar
Access lost track along the way of what table the fields came from
Solution:Just delete the extra characters.
Uncheck the Show buttons so that only SFullName and Country will show in the results.
The sorting will still work.
Run the query to verify your design.
Save the query and close it.
This is not a parameter query yet, but it will be!
The query must be closed to be used as the source of a new report.
You have to wait until after the form is created because you need to know the name of the form and name of the control on the form in order to create the parameter.
You will create a report and later change it to use a parameter form first. We are thinking ahead for some of the formatting choices, which work better when only one Country group shows.
On the Create ribbon tab, click the Report button to create an autoreport based on this query.
A simple report opens in Layout View. It uses a table to position the controls.
Format the Country control in the group header as Bold and Blue.
Next you will create an unbound form that will collect parameter information. "Unbound" means that it is not tied to any table or query. You must add controls using tools from the Controls palette on the ribbon.
A list box shows a list of items in a fixed-size box. If the control is too short to show the whole list, a scroll bar appears. It is useful for short lists because it does not have to be held open with the mouse like a combo box does.
You can only choose one item from a list box.
Source for the List: You can type in the items you want to see easily. But, if the list could be used by
several different forms, you can create a table as the
source of the list box. That lets you update the list in just one spot, the table.
Click in the Detail section, at the left underneath the label you just created.
A control appears and the List Box wizard starts to help you with this control.
Problem:
Wizard does not start
The command to use Control Wizards is off.
Solution: Delete the blank
list box. Repeat the steps above to click on the command, Use Control Wizards, to turn it on and try again.
In
the second page, leave the number of columns as 1 and type in the choices:
Argentina
Australia
USA
Use the down arrow key to move to the next row. The ENTER key moves the dialog to the next step.
If necessary, resize the label for the list box and move the control and its label to positions similar to the illustration.
Notice that the new control does not show the actual list while in Design View.
Switch to Form View.
Now you can pick a country, but nothing happens.
You could add a macro to the list to make it create the report when you click on a country. But that becomes very annoying for users who miss their click. A command button is a better method.
Editing the List: Below the list box in Form View there is an Edit button. Click the button to add new values immediately. There is a property for this feature, of course. It's on the Data tab in the Property Sheet- Allow Value List Edits. Changing that property to No would prevent the user from adding a new country to the list from the form. How much do you trust the users of this form? How often might a new country need to be added to the list?
Once your user has picked a country, you need a way to open the report and use that choice. A command button is the right control. A wizard helps by creating some code for you! The code will automatically be attached to the OnClick event in the button's Property Sheet.
With
the button still selected, look at the Property Sheet.
(Open it if necessary by clicking the Property Sheet button on the ribbon tab.)
Click on the ellipsis button for the On Click property.
Access 2007: A dialog opens where each action in the macro shows in a row. The arguments for the action show at the bottom of the dialog but only when that action is selected in the top.
Drop the list of actions beside OpenReport to see what actions are available.
Access 2010, 2013, 2016:
A new tab opens to show the macro in a stepped outline format. Each action shows its arguments directly below it. The display can be collapsed to show only the actions but the default is to display the arguments.
Switch to Form View.
How does your form look? If necessary, make adjustments to the position
or size of the controls. (For the illustration the window was reduced in size.)
There are some unnecessary parts to this window, including the record selector at the left, the scrollbar to the right, and the navigation buttons at the bottom. You can use the Property Sheet to hide them.
On
the Other tab, make the following changes:
Pop Up = Yes
Modal = Yes
The PopUp property makes the form open as a dialog on top of the Access window instead of in a tab.
The Modal property requires the user to make a choice in the popup form before continuing.
Select a country on the form.
Problem: Cannot select country
Ding. Unknown field. You typed CountryList in the Control Source property instead of the control's Name property.
Solution: Edit the Property Sheet for the list box.
Right click on the form and select Design View.
The form switches to Design View but the report is still open.
Problem:
Switching to Form Design View for a dialog
These changes prevent you
from using a ribbon button to switch to Form Design view.
Solution: The right click menu includes that command... unless you right click on a control or label.
The query that is the Record Source for the report is currently not using a parameter.
You must make the query look to your new form for the value for the field Country.
Right click the Criteria row in the Country column and select Zoom.
The Zoom window opens, giving you a better view of what you are about to type.
Run the query to see if your expression is working.
Whoops. A normal parameter dialog opens. The query cannot get a current value from a form that is closed! But if the form is open, you cannot run the query because the form is Modal. Yikes! This is a bit complicated!
You could change the form again so that it's not a Modal form. It may be easier to check it from the report.
When objects depend on other objects, it is a good idea to close all of them and try from the beginning to see if everything works as you thought it should. To use your new custom form, where must you start??
Select the country Argentina and click the button Open Report.
The report opens underneath the form and shows just the staff members in Argentina. Success!
Problem: The report failed.
Your expression has an error.
Solution: Go back to the query and inspect the expression for the Criteria row in the Zoom window (where you can see the whole expression). If necessary, use the Font button in the Zoom window to enlarge the text to make it easier to read. Fix any errors. Look for spaces, which are hard to spot.
Choose to open the Macro Builder and click on OK.
The Macro Builder opens in its own tab, ready for you to create the first macro.
Access 2007 and 2010, 2013, 2016 have different macro windows but the same steps.
Click the down arrow at the end of the Action text box to open a list of possible actions.
Click on OpenForm.
A new entry appears.
Scrolling long drop
list:
When you drop the list of actions and type a letter, the long list
scrolls to the entries that start with that letter.
In the arguments boxes, select the following from each of
the drop lists:
Form Name: Parameter-Staff-fromCountry
View: Form
Window Mode: Dialog
Why set Dialog mode? The Dialog mode sets the Pop Up and Modal properties to Yes. You already did that in the form's
properties. Using Dialog mode here, does something more! It guarantees
that the report will wait for you to enter values before formatting
the report. Very important!
The parameter form already has a macro to open the preview of the report. Now you will add a macro to hide the form, while leaving it open. That lets the report fetch the current value for Country.
On
the second action line, select SetProperty.
You will use this action to change a property of the parameter
form.
Users do not always do things in the order that you planned. Test your new objects by opening the form directly as well as by opening the report directly.
Testing the Report:
With the parameter form and the report both closed, double-click the report in the Navigation Pane.
The parameter form appears.
Problem: Report shows with 0 records
The report did not wait for a country to be selected. The dialog's mode is incorrect.
Solution: Edit the macro OnOpen for the report and change the Window Mode to Dialog.
Close the report.
The report and its hidden custom dialog close.
How do you know if an
invisible form has closed?
Try to open the report again. If the form already has a value selected in
it, then it did not close before!
You must close the report before using the parameter form again. The report will not update even if you see the form and make a different selection.
Switching Views: Once your report is open, the custom dialog will appear again if you switch views. But changing the choices does not change anything!
If you set the macro to open Print Preview instead of Open Report, you will get an error about not being able to switch views because code is running. There is some very tricky timing of actions to work with. All of those Events like On Open, On Activate, On Current, etc. come in a particular sequence. Getting your parameter into the sequence and formatting the preview is apparently more complex than going to Report View. Who knew?!