Creating a form and report menu
When you build a database, one of the problems that you need to
solve is how to provide your users with a navigation system. The
default in Access is to create a Switchboard, but this has
limitations, not the least of which being that you cannot have more
than 8 buttons on a menu screen. You can solve this by having
multiple screens but it gets unwieldy for large databases. This
tutorial shows a flexible way to build a menu, based on a listbox.
You can have as many or as few items as you like; you can adapt the
sort order and the displayed name to suit; and, with an extra field
in the underlying table, you can restrict access to areas of your
database depending on the user’s permissions.
This tutorial shows the listbox and table that form the basic
building blocks of a more sophisticated system.
Creating the table
You will need to create a table to hold the menu information. The table
will be called tlkFormReportMenu; the fields are shown below.
| Field | Data Type (size) | Description |
|---|---|---|
| DisplayName | Text (50) | Visible description in the menu |
| ObjectName | Text (50) | Must match an existing form or report name |
| ObjectType | Text (50) | Form or Report |
| SortOrder | Number (Long Integer) | Adjust to suit desired display order in the menu |
Add records to the table. The records used for the sample menu are shown
below:
| DisplayName | ObjectName | ObjectType | SortOrder |
|---|---|---|---|
| Orders Form | frmOrders | Form | 1 |
| Clients Form | frmClients | Form | 2 |
| Monthly Sales Report | rptMonthlySales | Report | 4 |
| Quarterly Sales By Rep | rptQtrByRep | Report | 5 |
| Quarterly Sales By Division | rptQtrByDiv | Report | 6 |
| Products Form | frmProducts | Form | 3 |
Creating the menu
Create a new form called frmMenu. Add a Command button called
cmdClose, and in the Click event put this code:
DoCmd.Close acForm, Me.Name
Add a list box and call it lstPick. Adjust the
Properties as shown below:
| Property | Value |
|---|---|
| Column Count | 4 |
| Bound Column | 2 |
| Column Widths | 6cm;0cm;0cm;0cm |
| Row Source Type | Table / Query |
| The Row Source for the list box uses all fields from the table you created in the previous step. To get to the query editor of the listbox, select the Rowsource line and then click the … button at the end of the row. Set up the query grid as shown in the image: |
 |
Now create the event code that launches the forms and reports. In
the After Update event of the listbox place this code:
Private Sub lstPick_AfterUpdate()
Select Case Me.lstPick.Column(2)
Case "Form": DoCmd.OpenForm Me.lstPick, acNormal
Case "Report": DoCmd.OpenReport Me.lstPick, acViewPreview
End Select
End Sub
Note:
- Usually the first column of a combo or list box is the bound
column. In this case, the form or report name is in column 2, so
setting this to the bound column simplifies the code. - In the Select Case statement I have used a short-cut syntax
to place each case on a single line. The colon separates the
case from the resulting output. - The code checks the contents of the third field for “Form”
or “Report” but it refers to Column(2) because column numbers
for combo and list boxes start from 0 in VBA.
| Save the form, format it as desired, and you’re ready to use it. Click any item in the listbox to launch the corresponding form or report. The final version looks like this: |
 |
