Cascade comboxes in a continuous form won't work

[yltang]

I am working on an order system. The following are the tables and forms.

(A) Tables:
tblOrder: OrderID (PK), CustomerID
tblOrderDetail: OrderID (PK), ProductID
tblProduct: ProductID (PK), ProductName, CategoryID
tblCategory: CategoryID (PK), CategoryName

(B) Forms:
frmOrder (single column type with sub-form frmOrderDetail): OrderDate, CustomerID, CustomerID
frmOrderDetail (of continuous type): CategoryID, ProductID  (both are combo boxes of control names cboCategoryID and cboProductID, respectively; cboCategoryID is unbound)

What I need: In the frmOrderDetail sub-form (may already have more than one row), select CategoryID in a row and the corresponding matched ProductID will show up in the combo list at the same row. Nothing will be affected in other rows. I have written a program for CategoryID in frmOrderDetail as follows. However, when I select a CategoryID all CategoryID in other rows are altered. Same thing happens to ProductID. The cascade combo boxes don't work: they interfere with each other. Any solutions?

------------------------
Private Sub cboCategoryID_AfterUpdate()
   Me!cboProductID.RowSource = "SELECT ProductID, ProductName FROM tblProduct WHERE (CategoryID = Forms!frmOrderDetail!cboCategoryID);"
 Me!cboProductID.Requery
End Sub
-------------------------

[Stewart Ross]

Hi. If you see the selections repeating for all occurrences of your combo boxes on the continuous form then your combos are unbound; that is, their control source property is blank instead of being set to a specific field in the underlying query or table on which your form is based.

Access implements a single instance of controls on a continuous form, and is only able to vary the values shown when the control is bound to an underlying field. Controls that are not bound show the same value down all rows of the continuous form, rendering them useless.

You will need to resolve this problem by binding the combos to the correct fields before you can do any more with the cascading selections.

-Stewart

[yltang]

Hi. If you see the selections repeating for all occurrences of your combo boxes on the continuous form then your combos are unbound; that is, their control source property is blank instead of being set to a specific field in the underlying query or table on which your form is based.

Access implements a single instance of controls on a continuous form, and is only able to vary the values shown when the control is bound to an underlying field. Controls that are not bound show the same value down all rows of the continuous form, rendering them useless.

You will need to resolve this problem by binding the combos to the correct fields before you can do any more with the cascading selections.

-Stewart

Hi Stewart,
Thanks a lot. I have added a Dummy field in tblOrderDetail table and bind it to the cboCategoryID combo box. Now, selecting an item in cboCategory will not change the values of cboCategoryID in other rows. However, when I make another selection of cboCategoryID in another rows, it will change the values of cboProductID in every row. How do I solve the problem? Thanks in advance.

[missinglinq]

cboProductID also has to be bound to a field, or the same thing happens!

Linq ;0)>

[Stewart Ross]

Hi. Reviewing your first post in light of what you say about dummy fields I can't understand what you are trying to achieve here. If you have had to add dummy fields to bind your combos (by which I take it you mean that they are fields otherwise unused except to allow the individual combo selections to work) what was it you expected the combos to do for you in the first place?

Cascading combo selections are normally used to filter groups of records shown on forms and subforms, and in most circumstances their placement is at the level 'above' that of the records concerned.

-Stewart

[yltang]

Hi. Reviewing your first post in light of what you say about dummy fields I can't understand what you are trying to achieve here. If you have had to add dummy fields to bind your combos (by which I take it you mean that they are fields otherwise unused except to allow the individual combo selections to work) what was it you expected the combos to do for you in the first place?

Cascading combo selections are normally used to filter groups of records shown on forms and subforms, and in most circumstances their placement is at the level 'above' that of the records concerned.

-Stewart

Hi missinglinq and Steward,
Thanks first for your comments. To missinglinq: the cboProductID is also bound. To Steward: the main reason for using cascading combo boxes is indeed to filter the number of selection items. I think it is very common for an order system to limit the number of products for selection (imagine there are hundreds or even thousands of products!). One of the more efficient ways I can think of is to group products into categories. That's why I used cboCategoryID as the first combo box. After selecting the cboCategoryID combo box, the number of corresponding products will be much fewer. However, as I posted in the first question, using an unbound combo box for selecting categories won't work. Therefore, as suggested by Steward, I have to bind the combo box by using a Dummy field as a workaround. Sadly, the problem is solved only partially: the values in cboProductID in every row are changed due to the selection of the product categories. I hope that I describe the problem clear enough. Again, I list the main structures of the related tables:

tblCategory: CategoryID*, CategoryName
tblProduct: ProductID*, ProductName, CategoryID
tblOrder: OrderID*, CustomerID
tblOrderDetail: OrderID*, ProductID*, Dummy

The form for adding an order is a main single form (frmOrder) accompanied by a continuous sub-form (sfmOrderDetail). So, how do I add/select a product without having to scan tens, hundreds, or even thousands of products? I tried to use a combo box cboCategoryID (bound to a Dummy field in tblOrderDetail) to select the category of the product. And, supposedly, the proper products belonging to that category will show up in the combo box cboProductID, which is bound to ProductID in tblOrderDetail. The problem is that the values in cboProductID in every row get refreshed very time I make a category selection. So, how do I solve the problem? Thanks in advance.

[Stewart Ross]

Hi. Well, you appear to be binding the wrong field here; if you are selecting products with your filtered combos it would normally be the product ID field in your subform that should be bound to the main selection combo. Using a dummy field is simply incorrect, no matter why you are doing so, as there is no relation between the actual product ID and your dummy field.

If you want to use two cascaded combos to select categories and products to make it easier for users to select the correct item on your order details form it would be better to have a single pop-up form for that purpose, opened from a selection button on your subform, instead of trying to place two combos on your subform rows and running into the difficulties you are currently experiencing.

I would urge you to consider again what it is you are trying to achieve, and how you are setting about it. I would also suggest you review the Northwind sample database supplied with Access, which shows examples of the use of subforms that might help you with simpler designs.

-Stewart