Microsoft Access 2016: Resetting Default Value of Combo Box (that uses equation) with Button?

Question

I have the following code on the Reset button:

Private Sub cmdReset_Click()
    Dim ctl As Control

    For Each ctl In Me.Controls

        Select Case ctl.ControlType
            Case acComboBox
                ctl.Value = ctl.DefaultValue
        End Select
    Next
    Me.Requery
End Sub

This is what my Default Value for the combo boxes look like:

=DLookUp("FieldName","TableName","strCriteria")

When I click the Reset button the combo box just displays the equation as a string =DLookUp("FieldName","TableName","strCriteria") instead of actually running the function. I tried using a different Default Value =[cboName].[ItemData](#) and it's the same issue. The Form works perfectly otherwise.

I appreciate your help.

Answer 1

The behavior is expected, since ctl.DefaultValue returns the string default value property, not the actual calculated default value.

A possible approach is to use Eval to calculate the actual default value. Note, however, that numerous problems can arise when doing this, since the default value is not VBA, but an Access expression, so it has a different scope, and is sensitive to locale settings such as the list separator setting.

You can use the following line:

ctl.Value = Eval(Right(ctl.DefaultValue, Len(ctl.DefaultValue) -1))