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:

  Home | About | Links | Contact | Excel | Access