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

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: the menu form with teh completed listbox