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.
|Data Type (size)
|Visible description in the menu
|Must match an existing form or report name
|Form or Report
|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
|Monthly Sales Report
|Quarterly Sales By Rep
|Quarterly Sales By Division
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:
|Row Source Type
|Table / Query
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: