Cascading Combo Boxes in a continuous subform

[Skeletor]

I am using a modified 2007 Northwind database. I have added 3 Combo boxes to
the "Order Subform for Order Details" form; "Sales Channel", "Supplier",
"Category", which filter the products displayed in the "Product ID" Combo.

When I make selections in the first row of combo boxes, everthing workes
perfectly.
However, when I select a different category in the "Category" combo box in
the second row, the "Product ID" in the first row disappeared.

Could someone please advise how to rectify this so that each row retains the
information selected.
Thankyou
Mike

 

[Allen Browne]

Access does not load a different RowSource query for every row in a
continuous form. So, if you filter the combo so that the display value
needed on other rows is not available, you make it go blank.

One solution is to not hide the display value. Say the combo is bound to a
Category table, with fields:
- CategoryID Autonumber
- CategoryName Text
You set up the combo so its Column Count is 2, the Bound column is 1 (the
autonumber), and the Column Widths makes this column zero-width. This will
give you the problem.

An alternative approach is drop the AutoNumber field, and make the text
field the primary key. The category name will be required and unique anyway,
so it's a natural primary key. Now you don't need to hide the bound column
of the combo, so Access knows the value to display on other rows, so the
problem evaporates.

If you can't do that for some reason, another alternative is to create a
query as the source for your form. The query has your both main table and
the Category table, using an outer join so records with no category don't
disappear, as explained here:
http://allenbrowne.com/casu-02.html

This query can output the CategoryName as well as the CategoryID that the
combo is bound to. In your form, you can place a text box on top of the
combo, so it shows the value for each row. Give the text box these
properties:
Control Source CategoryName
Width Same as the combo without the drop-down arrow
Tab Order Just after the combo
Tab Stop Yes (set the combo's to No.)
Locked Yes (so the user can't change it.)
Enabled Yes (so the user can click on it.)
On Enter [Event Procedure]

Click the Build button beside the On Enter property.
Access opens the code window.
Set up the code for the event to set focus to the combo, e.g.:
Me.CategoryID.SetFocus

The trick here is that the combo jumps in front on the current row only, so
the other rows still show the value using the text box.