How to Create an Access Database Dashboard or Switchboard
How to create a Microsoft® Access database dashboard, or switchboard, that's user-friendly and easy for the developer to maintain.
Last updated on 2020-07-26 by David Wallis.
Buttons and Yet More Buttons
The first thing you see on opening many Access databases is a dashboard form populated with buttons. Pushing these provide the means for the user to navigate the database, opening forms, reports and so on.
These are some of the disadvantages of dashboards of buttons:
- In a database that has many forms and reports, you may find it equipped with a dashboard, such as the one above, in whichsome buttons link to sub-dashboard forms. Yet more buttons users have to click to get at what they want.
- The choice of buttons and their arrangement on the form may not present an intuitative path for the users through their usual work flow.
- Possibly the developer didn’t plan the dashboard properly at the prototype stage and the buttons have remained as they’ve always been. How to Design and Create Forms in Microsoft Access includes coverage of planning as an essential part of database developemnt.
- When new forms and reports are introduced, dashboard forms need modification. This can be time consuming for the developer and hence costly for the client.
- Revised or completely new dashboards can prove disruptive for users obliged to adjust to new button sequences.
An Alternative Approach to Dashboard Design
This is an example of a dashboard design I suggest as an alternative to a button-festooned one:
Users are presented with lists covering all aspects of their database work. A double-click on a list item takes them straight to the appropriate form or report without need for any steps through navigation sub-forms .
In topics below I explain how to set up such a form. Set up once, this form and its supporting components can be imported into any database in need of a dashboard.
Adding to the lists and changing the order in which items appear is very easy and quick to achieve.
You’re not limited to the lists shown in the picture above. In a variation, I have a fourth list headed Export to Excel for clients who want data dumped out to Excel so that users can do their own thing without altering the records in the database.
There is a table for each list on the dashboard form. The tables are part of the front-end of a split database. That is, they’re contained in the file the user opens, not in the database's back-end data file.
This is the table that supplies the list of Main Screens on the dashboard:
This table is named tluNavigation. Its Navigation field supplies the list appearing on the dashboard; the Form field identifies the form that opens when the user double-clicks an item in the list.
The IsInList field provides control over whether or not an item appears in the dashboard list. This provides for clients requiring slightly different “versions” of the database, e.g. one for each departments.
As you proceeed through the development phases of a database, you’ll be adding forms (and reports). Each new one you’ll add to the table. ListOrder provides a quick way of determining the order of list items on the dashboard — no need to shuffle of records in the table itself.
Tables named tluNavigationReports and tluNavigationLists act for the two other lists. Both of these tables follow the design of tluNavigation.
Lists on the Dashboard Form
These two properties of each list box control on the dashboard form need particular attention to make the form work:
- Row Source contains the details of the forms and reports appearing in the lists
- On Dbl Click Event launches the user’s choice of form or repor.t
Row Source Property
For the Main Screens list, named lbMainScreens on the form, this is the query used as the basis for its Row Source poperty:
Saved, it can be the query named as the Row Source property. Alternatively it can provide the SQL statement that you copy and past into the property.
On Dbl Click Event Property
Each list on the form has an On Dbl Click event. For the Main Screens, list this is the event code:
Private Sub lbMainScreens_DblClick(Cancel As Integer)
On Error GoTo errHandler
frm$ = .lbMainScreens.Column(2)
Select Case .lbNav
DoCmd.OpenForm frm$, acFormDS
DoCmd.OpenForm frm$, acNormal
MsgBox Err.Description, vbCritical, "Error Opening Screen"
Resume procDoneEnd Sub
You can add Case statements to accommodate opening certain forms in other modes, e.g. adding a new record.
Your Support for dmw TIPS
If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website, keeping it free of advertising.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —
Thanks, in anticipation.