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.
- 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.
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.
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.
Dashboard Tables
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
Dim frm$
With Me
frm$ = .lbMainScreens.Column(2)
Select Case .lbNav
Case 3
DoCmd.OpenForm frm$, acFormDS
Case Else
DoCmd.OpenForm frm$, acNormal
End Select
End With
procDone:
Exit Sub
errHandler:
MsgBox Err.Description, vbCritical, "Error Opening Screen"
Resume procDone
End SubYou can add Case statements to accommodate opening certain forms in other modes, e.g. adding a new record.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —