Search code examples
vbams-accesscomboboxcomparison

Erratic combobox comparison in Access VBA


I have set the row source to the same table for two different unbound comboboxes, cbo_MaxCost and cbo_MinCost, on a form. These comboboxes are meant to specify the minimum and maximum of a range of values for each record in another table. To ensure they do, I've set up code in each combobox's AfterUpdate event to check that

cbo_MaxCost >= cbo_MinCost

or warn the user with an error.

The problem is, this check performs erratically. For example, sometimes Access VBA will evaluate

21 > 11

as False and other times as True. Usage of the dot and exclamation point notations makes no difference, nor does using "Me." or not, or ".Value" or not. Stepping through it shows that Access registers the proper values from the comboboxes during the comparison. This behavior seems to happen more often when I select a much higher or lower value than was previously in one of the boxes. I can't figure out what causes it.

This is the code:

Private Sub cbo_MaxCost_AfterUpdate()
   If cbo_MaxCost >= cbo_MinCost Then
      MsgBox ("Access is calculating correctly.")
   Else
      MsgBox ("The maximum cost should be higher than the minimum.")
   End If
End Sub

Private Sub cbo_MinCost_AfterUpdate()
   If cbo_MaxCost >= cbo_MinCost Then
      MsgBox ("Access is calculating correctly.")
   Else
      MsgBox ("The maximum cost should be higher than the minimum.")
   End If
End Sub

Solution

  • Have in mind, that a combobox/listbox always returns text, so convert to numeric before the comparison:

    CCur(Me!cbo_MaxCost.Value) >= CCur(Me!cbo_MinCost.Value)