Combo Box Does Not Display Value

[Amy E. Baggott]

I have a combo box (booth number) whose row source is filtered based on
another combo box in the same form (hall). However, when I move to the next
record in a continuous form and change the hall on the new record, the booth
number disappears from the original record. How can I limit the booth
numbers to only the ones in the current hall for new records and still have
the form display the booth number that was already selected and saved in the
previous record? The value is still in the underlying table, but does not
display.

 

[Ken Snell MVP]

You need to requery that combobox in the form's OnCurrent event procedure.

 

[Rick Raubenheimer]

Dear Amy

Ken's answer tells you how to get the list current, not why the booth number
disappears from the original record. The problem is that (as you can see in
Design View) there is really only ONE combo box, not one for each record. So
when you re-query the combo box, they ALL display the booth numbers in the
current hall.

As far as I know there is NO solution using the combo box only.

I have a very ugly and messy work-around that I use, as follows:
- Over your combo box, place a text box that covers all but the combo box's
"down arrow" button.
- Set "Order" so that the Text box is "in front" of the Combo box.
- Link the text box to the field that displays in the Combo box. If the
Combo box draws its data from another table, you either have to include that
table in the Form's record source (which may complicate your join types) or
use a DLookup (which is slow).
- set the Text box just before the Combo box in the Tab Order, but make its
Tab Stop property "false" and set "Locked" to True.

The user will then (with luck) click on (or tab to) the combo box (which
temporarily brings it to the front) and use the filtered list. Meanwhile the
text box will display the correct data on the other records.

If the user happens to click on the text box instead of the combo box's
"down arrow" button, tough!

RICKgards.

 

[Amy E. Baggott]

Actually, I did something similar. Since I don't want them changing the
value except when they create a new record, I created a text box to show the
current value and placed it behind the combo box. Then I set the On Current
property to include an If statement that shows the combo box and hides the
text box on new records and does the reverse for everyone else. Hopefully,
it should work when they start using it.