Ever wanted to base a chart off of a datasheet and have the chart automatically updated based on whatever filters the user may apply?

I did!

I started off doing some Googling and was surprised to find nothing on the subject.

Then I turned to my fellow MVPs for guidance and was surprised that this seemed to be an unusual request. With a little back and forth I managed to put together a functional approach.

So How Is It All Done Then?

I started out thinking that I could simply add code to the Datasheet’s Apply Filter event and be done, but this wasn’t the case. The issue here is that it would fire after each filter change. So if the user wants to apply multiple filters, the code will fire multiple times, and thus update the chart each time. This is not only annoying, but can also slow down the entire process depending on the network, quantity of data, … involved in the process.

So What To Do Then?

My next idea was to add my code to the Datasheet’s Current event. It was a step in the right direction, but this still wasn’t perfect because it would fire every single time I’d move between the records, but at least it would allow me to adjust the filters to my hearts content before running.

The Final Solution

Then I got the idea, how about only allowing the Datasheet’s Current event to fire only once! … but how? The solution was a simple one, use a global variable to control the Current Event running the code. By using a combination of both events: Datasheet’s Apply Filter & Current events I managed to put together an elegant and, so far, reliable solution.

I use the Apply Filter to set the global variable to TRUE which will then enable the Current event to run our code, but after running it one time, the Current event then resets the global variable back to FALSE, so the next time it is called, when we move between records, it will not run our code again for no reason.

Option Compare Database
Option Explicit

Private Const sModName = "Form_ObjectSpecs_Data"
Private bNewFilter As Boolean


Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    On Error GoTo Error_Handler

    bNewFilter = True 'We've changed the filter(s) so set the Global 
                      'variable so our code will run to update the chart
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: " & sModName & "\Form_ApplyFilter" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Public Sub Form_Current()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database

    If bNewFilter = True Then
        Set db = CurrentDb
        'Empty the Temp Table
        db.Execute "DELETE FROM ChartData;"
        With Me.RecordsetClone
            .MoveFirst
            Do While Not .EOF
                'Populate the Temp Table with the currently filtered records from the Datasheet
                db.Execute "INSERT INTO ChartData (ObjectID) VALUES(" & ![ObjectID] & ");"
                .MoveNext
            Loop
        End With
        Me.Parent.Form.Graph2.Requery 'Requery/Update our chart
        bNewFilter = False 'Set the Global Variable to False so the code doesn't run again
    End If
    
Error_Handler_Exit:
    On Error Resume Next
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: " & sModName & "\Form_Current" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

The last thing I did was setup my form to automatically run my code upon opening.

Private Sub Form_Load()
    On Error GoTo Error_Handler

    'Set the Global variable so our code will run to update the chart
    bNewFilter = True
    Me.Form_Current

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: " & sModName & "\Form_Load" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

What You’ll Also Notice

In my example, I am using a temporary table to store the data used to base the chart on. This is why the code start off by deleting the table of all of it’s content and then repopulates it with a list of records from the current filter(s).

This would not be my preferred method, but using a dynamic value based list for the chart has limits that could not be ignored (especially for my original use) and thus the temporary table approach was deemed to be the best solution. That said, if your value list will be limited, then dynamically adjusting the chart’s Row Source does work (it was my original solution until my datasheet contained too much data). The same approach would be adopted but instead of emptying the table and repopulating it, you would simply build a new Row Source based on the Datasheet’s record source.

Download a Demo Database

Feel free to download a demo copy by using the link provided below: