Callback functions in Microsoft Access VBA. Part 2

Little known feature of Microsoft Access

Callback functions can be a little hard to get a hold of but, once you master the technique, they can meet just about any need, no matter how complex. 

This series of three articles will cover:

1. The basics of callback functions
2. Making the callback function configurable
3. Sharing one callback function between several controls

This is the second article. Links to the first and third articles can be found at the bottom of this article. 

---

Dynamic formatting

The previously discussed examples of callback functions have been static in the sense, that - once initialised - they behave the same for as long the form holding the listbox or combobox using it is open.

This is true even if the control is required; contrary to what could be expected, a requery of the control (manually or from code) will not reinitialise the function, only retrieve the generated values again. Strangely, to force a true requery of the control and the function it uses, the only method, I've found, is to reassign the ColumWidths property of the listbox or combobox:

' Requery control.
Control.ColumnWidths = Control.ColumnWidths

It doesn't make sense, but it works.

Having this knowledge, the next question is how to pass parameters to the callback function to make it behave differently. The core function of the function will normally not have to be changed - more likely would it be the count of items listed, the format of these, or the range of values, for example the start date and end date for a list of dates.

To pass and read such changes, a special value of the argument Code can be used.
For the operation of the function, different constants are passed to the function, and the Select Case Code block then controls which steps to take. Now, study the list of constants:


Apparently, there is no value of 2 in use, thus no constant for this value exists. This is not so, however, only is the value for internal use only - to check for the RowSource type of the control. Running some tests will reveal, that calling a callback function later with a value of 2 for the argument Code “does nothing”. This opens an option to call the function to do “something else” than it otherwise is supposed to do, and this “something else” could be to reconfigure the callback function.

To set this up, a named constant is introduced for the purpose:


When called with this constant, also an Id is passed to the function to distinguish between a default call and later custom call:

' Control IDs.
Const DefaultId     As Long = -1
Const ResetId       As Long = 0
Const ActionId      As Long = 1

Using ActionId is the key to configure the function. What to configure and with which value, is passed via two of the remaining arguments: Row and Column, for example a start date, a row count, or a format:

' Setting IDs.
Const StartDateId   As Long = 1
Const RowCountId    As Long = 2
Const FormatId      As Long = 3

The configuration values are passed via Column which, for this reason, is declared as Variant.
To make all this readable, it is held in three levels of Select Case constructs. Here is an example from the function CallUltimoMonthDates:

Case acLBOpenAsVariant
' Id:       Action.
' Row:      Parameter id.
' Column:   Parameter value.
Select Case Id
    Case DefaultId                              ' Default call.
        If Not Initialized Then
            Start = Date
            Year = VBA.Year(Start)              ' Year of the first month to list.
            Month = VBA.Month(Start) + 1        ' Month of the second month to list.
            RowCount = Years * MonthsPerYear    ' Count of rows to display.
            If Control.ControlType = acComboBox Then
                Format(1) = Control.Format      ' Retrieve the display format from the combobox's Format property.
                Control.LeftMargin = LeftMargin ' Adjust left margin of combobox.
            Else
                Format(1) = ListboxFormat       ' Set the display format for the listbox.
            End If
            Initialized = True
        End If
    Case ResetId                                ' Custom call. Ignore custom settings for the current control.
        Initialized = False
    Case ActionId                               ' Custom call. Set one optional value.
        ' Row:      The id of the parameter to adjust.
        ' Column:   The value of the parameter.
        Select Case Row
            Case StartDateId                    ' Start date.
                Start = Column
                Year = VBA.Year(Start)          ' Year of the first month to list.
                Month = VBA.Month(Start) + 1    ' Month of the second month to list.
            Case RowCountId                     ' Count of weeks to list.
                RowCount = Column
            Case FormatId                       ' Format for display.
                If VarType(Column) = vbString Then
                    Format(1) = Column
                End If
        End Select
End Select

It may look a bit convoluted, but the use of meaningful names for the constants makes it easier to follow.

The challenge is to both set the static variables when the function is initialised and to preserve those that should not be altered at a later reconfiguration. If there was no option to preserve them, the function would need to be initialised in full even if only a single parameter was adjusted. This is controlled by the static variable Initialized in the “Default” section of the code. It is set to True after the first call, causing this section to be ignored for all later calls.

In the “Reset” section of the code, Initialized is reset to False, causing the function to reinitialise at a later call having Id set to DefaultId.

The third section, “Action”, is where the function can be reconfigured using the values from the arguments Row and Column. In the function listed here, three parameters can be reconfigured:

• the start date
• the count of rows to list
• the format of the listed dates

Reconfiguration

It would be possible to reconfigure the function by a series of calls with different parameters, but it will be difficult to read and maintain. For this reason, a function has been created, that will reconfigure one, two, or all parameters and requery both the control and the function in one go:

' Set custom parameters for a ComboBox or a ListBox having the function
' CallUltimoMonthDates as RowsourceType.
'
' Usage, where the parameter Object is a ComboBox or a ListBox object:
'
'   Set start date of list:
'   ConfigUltimoMonthDates Object, , #1/1/2020#
'
'   Set count of dates to list (for ComboBox only, ignoreded for ListBox):
'   ConfigUltimoMonthDates Object, , , 10
'
'   Set all parameters:
'   ConfigUltimoMonthDates Object, #4/1/2000#, 18
'
'   Reset all parameters to default settings.
'   NB: Could (should) be called when unloading the form:
'   ConfigUltimoMonthDates Object
'
' 2021-03-01. Cactus Data ApS, CPH.
'
Public Sub ConfigUltimoMonthDates( _
    ByRef Control As Control, _
    Optional ByVal StartDate As Date, _
    Optional ByVal RowCount As Long, _
    Optional ByVal Format As String)

    Const FunctionName  As String = "CallUltimoMonthDates"
    Const NoOpValue     As Long = 0
    Const DefaultId     As Long = -1
    Const ResetId       As Long = 0
    Const ActionId      As Long = 1
    Const StartDateId   As Long = 1
    Const RowCountId    As Long = 2
    Const FormatId      As Long = 3

    Dim ControlType     As AcControlType
    Dim SetValue        As Boolean

    If Not Control Is Nothing Then
        ControlType = Control.ControlType
        If ControlType = acListBox Or ControlType = acComboBox Then
            If Control.RowSourceType = FunctionName Then
                If RowCount <> NoOpValue Then
                    If Control.ControlType = acListBox Then
                        ' Setting of row count not supported.
                        RowCount = NoOpValue
                    End If
                End If

                ' Make sure, that this control has called the callback function to be initialized.
                ' That may not be the case, if this configuration function is called during form loading.
                Application.Run FunctionName, Control, DefaultId, NoOpValue, NoOpValue, acLBOpenAsVariant

                ' Set parameter(s) and run the function by its name.
                If DateDiff("d", StartDate, #12:00:00 AM#) <> 0 Then
                    Application.Run FunctionName, Control, ActionId, StartDateId, DateValue(StartDate), acLBOpenAsVariant
                    SetValue = True
                End If
                If RowCount > 0 Then
                    Application.Run FunctionName, Control, ActionId, RowCountId, RowCount, acLBOpenAsVariant
                    SetValue = True
                End If
                If Format <> "" Then
                    Application.Run FunctionName, Control, ActionId, FormatId, Format, acLBOpenAsVariant
                    SetValue = True
                End If
                If Not SetValue = True Then
                    ' Reset to default values.
                    Application.Run FunctionName, Control, ResetId, NoOpValue, NoOpValue, acLBOpenAsVariant
                End If

                ' Apply settings.
                Application.Run FunctionName, Control, DefaultId, NoOpValue, NoOpValue, acLBOpenAsVariant
                ' Requery control.
                Control.ColumnWidths = Control.ColumnWidths
            End If
        End If
    End If

End Sub

Note, that the three arguments are optional and have been given descriptive names making it easy to reconfigure one or more parameters. As the last step, the function calls the magic command described above that will requery the control and the function.
An example of a combobox using this callback function is shown in the form CallbackDemoUltimoMonths:


At top, a groupbox controls if either the ultimo dates of the months of the current year should be listed, or it should be a count of dates from today. If the latter is chosen, the count of rows can be specified.

So, if the groupbox is changed, the start date and the count of rows listed must be set. The code to do this is quite simple - the values are set and, in the last line, the control is passed the revised parameters and requeried by the call to the configuration function:

Private Sub UltimoSelect_AfterUpdate()

    Dim StartDate   As Date
    Dim RowCount    As Long
    Dim Enabled     As Boolean

    Select Case UltimoSelect.Value
        Case 0
            ' List dates from today.
            StartDate = Date
            ' Allow to adjust the count of months to list.
            RowCount = Me!DateRows.Value
            Enabled = True
        Case 1
            ' List the current year's ultimo month dates.
            StartDate = DateSerial(Year(Date), 1, 1)
            ' Lock row count to the count of months for a year.
            RowCount = MonthsPerYear
            Enabled = False
    End Select

    Me!DateRows.Value = RowCount
    Me!DateRows.Enabled = Enabled
    Me!DateRows.Locked = Not Enabled

    ConfigUltimoMonthDates Me!UltimoMonthDates, StartDate, RowCount

End Sub

This is probably as simple as it can get, and an example like the one shown here can easily be adopted for many scenarios.

Conclusion

It has been shown what steps are needed to create a reconfigurable callback function for Microsoft Access.

If you miss having one callback function support multiple controls, or wish to recapture the basics of callback functions, please proceed with the next or the first article in this series:

Callback functions in Microsoft Access VBA. Part 1
Callback functions in Microsoft Access VBA. Part 3

Code and download

The full code and demos are attached for Microsoft Access 365.



I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.

Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.