DMW logo
tel 01732 833085
tonbridge · kent · UK

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.

Access dashboard

These are some of the disadvantages of dashboards of buttons:


An Alternative Approach to Dashboard Design

This is an example of a dashboard design I suggest as an alternative to a button-festooned one:

MSA dashboard

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:

Navigation table

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 Property

For the Main Screens list, named lbMainScreens on the form, this is the query used as the basis for its Row Source poperty:

Navigation list query

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

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) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification

“The key metric of whether you’ve
succeeded is what fraction of your
employees use that dashboard everyday.”

Keith Rabois