One thing to be aware of when using correlated combo boxes is that the
Categories combo box should normally be unbound, as to include both the
CategoryID and ProductID in an OrderDetails table for instance would usually
mean that the table is not properly normalized. The CategoryID is implied
('functionally determined' in the jargon of the relational model) by the
ProductID so to include both introduces redundancy and the possible risk of
update anomalies, it being possible, regardless of what you do at form level
to prevent this, to have two rows in the table with the same ProductID and
different CategoryID values. You'll note the Northwind includes only the
ProductID in the OrderDetails table.
You can still have your normalization cake and eat it though, as it possible
to set up a form so that the category is selected first in an unbound control
and then the product from a restricted list in a bound control. You'll find
a demo of how to do this at the following link, although in the demo I use
geographical data with three levels to the hierarchy rather than two. The
principle is exactly the same, however:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
Setting up correlated combo boxes like this is simple to do in single form
view, but does not work in a continuous form as when the control is requeried
it will go blank in other rows where the Category differs from that in the
current row. In a continuous form you need to use either a hybrid control
made up of a text box superimposed on a combo box so they appear to the user
to be a single combo box control, or a combo box with a multi-column list.
In the demo I include both methods, but recommend the latter. With just two
levels rather than three the former works quite well, though, so would be
fine in your case.
You'll have noticed that I qualified my statements in the first paragraph
with 'normally' and 'usually'. This is because there might be situations
where you'd have both CategoryID and ProductID columns in an OrderDetails
table or similar. This would be where a product's category might be changed
at some time, but you would want the original category at the time of the
order to be retained with each order detail record. While a theoretical
possibility I find it hard to think of a real world situation where that
would be the case, however.
Ken Sheridan
Stafford, England
