Search code examples
vbastringms-accesstypesvariant

VarType() & TypeName() Fail for Unbound ComboBox.Value

In Access VBA, I have a function that is intended to return a criteria expression from field name and value parameters.

The idea is that for strings, the criterion will use the Like operator to permit pattern matching, e.g., Party Like '*f*' and for long integers, i.e., PK values, the criterion will use the equality operator (=), e.g., PartyID = 5822. Currently, this is conditioned on the variant subtype of the passed value parameter.

The function is below but the problem is that VarType() evaluates the Variant variable containing a long integer as being of the string subtype. I.e., VarType() returns 8 (i.e., vbString) when evaluating a Variant variable containing a long integer, whereas one would expect it to return 3 (i.e., vbLong). Tests indicate that this behavior also exists in the calling code, i.e., VarType() returns 8 (i.e., vbString) even for ctl.Value. TypeName() fails similarly. Recall that the Value property data type of all Access controls is Variant.

The question is why this is occurring, and specifically how to get VarType() and TypeName() to behave as documented.

Private Function CriterionCreate(ByVal strFieldName As String, varValue As Variant) As String
100     Dim strCriterion As String
    
200     Select Case VarType(varValue)
            Case vbLong     '=3.  For long integer PKs.
202             strCriterion = strFieldName & " = " & varValue
210         Case vbString   '=8.  For strings & text PKs.
211             strCriterion = strFieldName & mLikeThis(varValue)
220         Case vbNull     'Blank / empty record.
221             strCriterion = ""
229     End Select
    
300     CriterionCreate = strCriterion
End Function

Edit: The calling code is:

strCriterion = CriterionCreate(strFieldName, ctl.Value)

Solution

  • Thanks to @Andre, who dropped the breadcrumb that led to a solution, which emerged after a grand tour of the ComboBox object and its properties, as follows.

    [EDIT: The following works perfectly well but it turns out the IsNumeric() function will accurately evaluate Long data returned by an unbound ComboBox that VarType() incorrectly characterizes as a String.]

    [TL;DR: An unbound ComboBox fails to return accurately typed data. So, instead reference ComboBox.Recordset to wrangle accurately typed data from an unbound ComboBox.]

    The breadcrumb is that an unbound Access TextBox returns text (i.e., a string) by default. The problem I was having was with an unbound ComboBox, but still.

    This is entirely unintuitive because the data is a Long Integer and Value properties all are Variant, which suggests that the data type would be preserved.

    Instead, it seems that ComboBox.Value evaluates ControlSource to determine its data subtype rather than the data itself. If a combo box ControlSource is a FK field in a table, that field likely is Long, so ComboBox.Value returns that type. An unbound combo box has no reference, however, so ComboBox.Value defaults to vbString, perhaps because that is the type of the ControlSource property itself.

    • Note that this issue is most likely to arise with unbound combo boxes. The Text and Value properties of an unbound TextBox will be blank. An unbound ComboBox, however, will have these properties populated by its RowSource property.

    • Thus, while ComboBox.Value appears to evaluate ControlSource to establish its subtype, it does not appear to evaluate RowSource in a similar manner.

    Nowhere, besides here, is this documented.

    So, an unbound ComboBox' Value property fails to return accurately typed data. Other data references are available, however, albeit with subtle distinctions:

    • ComboBox.Value, again, is typed vbString.
    • ComboBox.Column(0) also is typed vbString. This seems odd because ComboBox.Column presumably would reference the data directly and if so, ought to be able to correctly type it. Other behavior of the Form object suggests that objects interact internally with JET SQL dynasets, not their RecordSource, ControlSource, or Recordset objects directly. If so, then perhaps Access types all such data as vbString. Stay tuned on this topic.
    • ComboBox.Recordset.Fields(0) is typed vbLong, which is a bit of a breakthrough. Sadly, references to ComboBox.Recordset return all records. The solution, then, is to filter the recordset.

    It may seem like a bit of work but the code I came up with to wrangle accurately typed data from an unbound ComboBox object follows. Its advantage is that it does not require further inspection or any use of a type conversion function to coerce type.

    230                 If ctl.ControlType = acComboBox Then
231                     Dim rst As DAO.Recordset
232                     Dim rstFiltered As DAO.Recordset

240                     Set rst = ctl.Recordset

250                     With rst
251                         .Filter = .Fields(0).Name & " = " & ctl.Value
252                         Set rstFiltered = .OpenRecordset
259                     End With

260                     varValue = rstFiltered.Fields(0)

267                     Set rstFiltered = Nothing
268                     Set rst = Nothing
269                 End If

270                 strCriterion = CriterionCreate(strFieldName, varValue)