Can anyone explain this conundrum please. Just niggling as to why. Using Access 2010 VBA.
To simplify. I have two (of many indexed) combo boxes both based on the same 2 column table 1st column field is a primary key ID as Long and 2nd column a text field. I alter the row source in one dependent on the chosen value in the other using the AfterUpdate event. This is to filter out tbat value in the other combo. Works fine.
However if I check whether the two values are equal before the change in the BeforeUpdate event so as to warn user that the 2nd combo is about to be filtered and give chance to step back, I meet the following minor problem.
If I test with
IF Me.Controls("cbo" & CStr(k)) = Me.Controls("cbo" & CStr(i)) Then ....
when the 2 combos have equal values the test fails to return True
If I do any type conversion at all for e.g.
IF Cstr(Me.Controls("cbo" & CStr(k))) = Cstr(Me.Controls("cbo" & CStr(i))) Then ....
or indeed use CInt or CLng the test returns True as I would expect.
Anyone care to explain please why the 2 equal values are not equal! I assume the combo values from the 2 combos are the same type? as they come from the same table ID column - so why the difference.
Incidentally what type is returned by a combo value referenced to ID field with data type Long?
Probably because the 2 combo boxes have different data types. One is Long and other one is Text.
Probably because in the BeforeUpdate event, the old value of the combo box is being referred so the comparison of values are not accurate.
As in the original post. The combos are all part of a set all of which have the same base table and their values are taken from the primary key field which therefore has data type LONG so not of different data types.
The values compared are the "proposed" value of the combos (before change) not the old values. In any case as in original post if any type conversion is used, then the two values are detected as equal when they should be.
After further thought I guess that combo values must be returned as a variant as they (as opposed to listboxes) can return NULL which only variant types can handle.
The question now I suppose is why are 2 "equal" variant values not detected as equal without explicit type conversion?
Not a big deal but it would help understanding if I knew the reason.