Combo box column says it is null when it is not

[BallunarCrew]

I have a combo box on a form that has its row source set to a query on a table. The combo box shows 3 fields from the table. In the Change event, I reference the data in cmbExam.column(2) to get a number that is displayed in the third field. On some choices from the combo box, it returns a null for cmbExam.column(2) when I can clearly see the data in the combo box and the underlying table. I have compacted/repaired the database; deleted the source data and recreated it; recreated the combo box all to no avail. During one session working in the database, this will happen on 1-2 of the combo box choices and the next session it may be on a couple of others, next session it all works fine - no pattern to the behavior. Anyone ever experience this?

 

[seaport]

There are two situations when the column property returns null.
1. The combox's LimitToList property is set to be true and the user types in something that is not in the dropdown list.

2. The third column/field does not exist (the source table/query has only 2 fields).

 

[MajP]

Need to use the after update event instead. If you type in the box the change event takes place as soon as you type a key. Therefore assume the record is "123", and you type 1. The change event fires as soon as you enter 1 and there is no related record yet.

 

[BallunarCrew]

Seaport - I have seen this happen when using the mouse to click the drop down arrow then pick a value but I will try turning off the LinitToList. The third col definitely exists, I can see the data in the pick list and other choices work fine.

MajP - I'll try the afterUpdate event but the users to do not type in this combo - it is all mouse driven. Maybe the afterupdate will be more consistent.
I've programmed many combo boxes like this in my 15+ years with Access and have never had this problem. It is with two combo boxes on one form - am about to decide the form is corrupt and recreate it.

 

[MajP]

You tell me that a user never types in the box to trigger the autoexpand? I find that hard to believe.

 

[BallunarCrew]

The box is very small, basically just displays the drop down arrow. I can't say what a user never does or doesn't do but I have experienced this myself and I do not type in the box. I click the arrow and then click on a choice.

 

[TheAceMan1]

How are ya BallunarCrew . . .

Remove & reconstruct the combo and use the [blue]AfterUpdate[/blue] event suggested by [blue]MajP[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[BallunarCrew]

It appears that changing to the AfterUpdate event has corrected this but since it has happened seemingly randomly, I think the real proof will be when the problem does not occur again. Thanks for the help.