How to Create a Custom Dashboard in MS Access

A custom dashboard in MS Access is a form that shows key numbers and lists at a glance—order counts, revenue totals, overdue items, or top customers—without opening multiple reports. You build it from summary queries, text boxes and subforms bound to those queries, and optional charts. This guide gives you exact steps to create the form, wire up the data, refresh it on load or on demand, and avoid the performance traps (like dozens of DSum/DCount calls). You need existing tables and a few aggregate queries; the dashboard form itself is usually unbound. For the base form and controls, Access forms and Access queries are the foundation.

What a Custom Dashboard in MS Access Actually Is

In Access, a dashboard is typically one main form (often unbound) that displays summary data: totals, counts, and short lists. The data comes from queries (totals queries, or select queries that return one row). You put that data on the form by: (1) binding a text box ControlSource to a query that returns one value (e.g. a query named qryTotalOrders with one row and one column), or (2) using a subform whose RecordSource is a summary or list query, or (3) using domain functions like DSum/DCount (simplest but slow if you have many). The best approach for performance is (1) or (2)—queries run once; domain functions can run many times and hit the data repeatedly.

What You Need Before You Build the Dashboard

You need: tables with the data you want to summarize (e.g. Orders, OrderDetails, Customers); at least one totals or aggregate query per KPI (e.g. total sales, count of orders this month, count of overdue items); and a form. Create the queries first. Example: for “Total Sales This Month” create a query that sums the order total where OrderDate is in the current month. For “Top 10 Customers” create a query that groups by CustomerID/CustomerName, sums order amount, and returns the top 10 (use TOP 10 in the SQL or set the query’s Top Values property). Name the queries clearly (e.g. qryDashboardTotalOrders, qryDashboardTopCustomers) so the dashboard form can reference them.

Step-by-Step: Create the Dashboard Form and Wire KPIs

1. Create a blank form: Create → Form Design (blank form). Set the form’s RecordSource to empty. Save as frmDashboard (or similar). Resize so you have room for several sections.
2. Add text boxes for single-value KPIs: For each KPI that is one number (e.g. total orders, total revenue), add a text box. Set the ControlSource to =DSum("OrderTotal", "Orders", "OrderDate >= #" & DateSerial(Year(Date), Month(Date), 1) & "#") for a dynamic value, or for better performance bind the form to a query that returns one row with multiple columns: create qryDashboardKPIs that selects Count(*), Sum(Amount), etc. in one query; set the form’s RecordSource to qryDashboardKPIs and bind each text box to the column (e.g. TotalOrders, TotalRevenue). The one-query approach is faster and easier to maintain.
3. Add labels: Label each text box (e.g. “Total Orders”, “Revenue This Month”) so users know what they’re looking at.
4. Add subforms for lists: For “Top 10 Customers” or “Recent Orders”, add a subform control. Create a small form (or use a datasheet) whose RecordSource is qryDashboardTopCustomers (or qryRecentOrders). Place the subform on the dashboard and set its Source Object to that form. The subform shows the list; it refreshes when the dashboard loads (or when you requery it in VBA).
5. Add a Refresh button (optional): Add a button that runs VBA to requery the form and each subform (see code below). So users can refresh without closing and reopening.
6. Set as startup form (optional): File → Options → Current Database → Display Form: choose frmDashboard so the dashboard opens when the database opens. Or open it from a switchboard or menu.

One Query for All KPIs (Faster Than Many DSum/DCount)

Instead of 10 text boxes each with a DSum or DCount (which can mean 10 separate queries every time the form loads), use one totals query that returns one row with multiple columns. Create a query in SQL View like the one below—adjust table and field names to match yours. Set the form’s RecordSource to this query and bind text boxes to TotalOrders, TotalRevenue, OverdueCount. The form opens one record; all values load in one go.

SELECT
    (SELECT COUNT(*) FROM Orders WHERE OrderDate >= DateSerial(Year(Date()), Month(Date()), 1)) AS TotalOrders,
    (SELECT Nz(Sum(OrderTotal), 0) FROM Orders WHERE OrderDate >= DateSerial(Year(Date()), Month(Date()), 1)) AS TotalRevenue,
    (SELECT COUNT(*) FROM Orders WHERE Status = 'Overdue') AS OverdueCount;

In Access SQL you can use DateSerial(Year(Date()), Month(Date()), 1) for “first day of current month”. If the query is slow (e.g. large Orders table), add indexes on OrderDate and Status. For more complex logic, use a saved query as the form’s RecordSource and base it on this idea.

VBA: Refresh the Dashboard on Load and on Button Click

To refresh all data when the form opens and when the user clicks Refresh, use the form’s Load (or Open) event and a button that calls the same refresh routine. Requery the form (so the one-row KPI query runs again) and each subform control.

Private Sub Form_Load()
    RefreshDashboard
End Sub

Private Sub RefreshDashboard()
    On Error Resume Next
    Me.Requery
    Me!subformTopCustomers.Requery
    Me!subformRecentOrders.Requery
    ' Add more subform names as needed
End Sub

' Button Click: set the button's On Click to: =RefreshDashboard()
' Or create: Private Sub cmdRefresh_Click()
'                RefreshDashboard
'            End Sub

Replace subformTopCustomers and subformRecentOrders with the actual names of your subform controls. If a control is not present, the Requery line will error; use On Error Resume Next for optional subforms or check that the control exists before calling Requery.

Adding a Chart to the Dashboard

Access can show a chart on a form via a chart control (insert → Chart) bound to a query, or a subform that is a chart. The chart’s RowSource is usually a query that returns categories and values (e.g. MonthName, Sum(Amount) grouped by month). Keep the chart query simple and indexed so the dashboard stays fast. For many data points or complex charts, consider exporting the query result to Excel and charting there, or using a report with a chart and opening it from the dashboard. For report-based summaries, Microsoft Access reports can be opened from a dashboard button.

Common Mistakes When Creating a Dashboard in MS Access

• Using DSum/DCount in many text boxes: Each call runs a separate query. With 15 KPIs that’s 15 queries on load and on every refresh. Use one query as the form RecordSource with multiple columns, or a small set of queries, and bind the text boxes to those columns.
• No indexes on filtered columns: Dashboard queries often filter by date or status. Add indexes on OrderDate, Status, and similar columns so the totals queries run quickly.
• Subform not refreshed: After the main form loads, subforms may still show old data until they are requeried. Call SubformControl.Requery in Form_Load and in your Refresh routine.
• Dashboard opens slowly: If the form has many subforms and heavy queries, open with a simple “Loading…” message or hide subforms until the main KPI query has run, then populate subforms (lazy load) so the user sees something quickly.

When a Custom Dashboard in MS Access Is Not the Right Tool

Use a dashboard when you need a quick, at-a-glance view inside Access. Do not use it for real-time data on very large tables (millions of rows) without summary tables or indexed aggregates—the queries will be slow. For live reporting over huge datasets, use pass-through queries to a server (e.g. SQL Server) or pre-aggregate into a small summary table that you refresh on a schedule. For printing or formal reports, use reports opened from the dashboard rather than trying to make the form itself a report.

Key Takeaways

• A custom dashboard in MS Access is a form (usually unbound or bound to one summary query) that shows KPIs and lists via text boxes and subforms bound to queries.
• Build aggregate queries first; then create a form and bind text boxes to one KPI query (one row, many columns) for better performance than many DSum/DCount controls.
• Use subforms for “Top N” or “Recent” lists; requery the form and each subform in Form_Load and in a Refresh button.
• Index date and status columns used in dashboard filters; avoid dozens of domain functions on one form.
• For very large data, use summary tables or server-side aggregation instead of live aggregates on the dashboard.

Frequently Asked Questions

Conclusion and Next Steps

A custom dashboard in MS Access is a summary form fed by queries: one query for single-value KPIs and subforms for lists. Build the queries first, bind the form and subforms to them, and refresh with Requery in code. Avoid overusing DSum/DCount; index filtered columns; and for very large data use summary tables or server-side aggregation. Access forms and Access queries cover the basics; switchboard in MS Access can open the dashboard from your main menu. If you need a dashboard built or optimized for your database, contact us for a free quote.