DataWright Information Services

Consulting and Resources for Excel and Access

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

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:

the rowsource for the menu listbox

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


  • 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:

the menu form with teh completed listbox