Cascading Combo Boxes In A Subform

[Wizcow]

I have two combo boxes on a subform set to datasheet.
Sometimes some of the data in the combo boxes vanishes.
The data is still there, if I click in the row, it appears
again.

Anyone know how get around this one?

Tom

 

[Allen Browne]

The combo has only one recordset in its RowSource - not a different
recordset for every row of your form.

That means the combo has nothing to show if:
a) the bound column is zero-width, and
b) you restrict the RowSource in such a way that it does not have the data
in the other columns.

You can avoid (a) if you leave the bound column visible. Access can always
display the bound column. For this reason I frequently use Text as the
primary key of small lookup tables, and the related foreign keys in other
tables.

You can avoid (b) if you do not reassign the RowSource of the combo to
restrict its contents, but that defeats the whole point of the
combo-on-combo scenario.

The other alternative is:
1. Create a query that combines the table your subform is based on with the
table that is the RowSource of the combo. Use an outer join if the foreign
key is not a required field. Include the text field from the look up table
in the query output.

2. Place a text box on the subform, overlapping the combo, bound to the text
field so it can display a value for each row. Bring-to-front. In its
GotFocus event, set focus to the combo.