The only reason I can see for needing a text box is if the second combo box
is if the Grocery Type field in the form's underlying table is a numeric
'surrogate' key, and the second combo box to which this is bound is showing
the text value from the other table by hiding the bound first column in its
RowSource. If the form is in continuous forms view this causes a problem as
in rows where the Grocery category differs from that in the current row the
second combo box will be blank as the relevant row is no longer available in
its list once its been requeried to show only those types for the current
category. In this situation an unbound text box can be used to show the text
value, and this is normally superimposed on the combo box to create a
'hybrid' control which looks to the user like a single combo box control.
The text box should be unbound, however, and there should be no extra column
in the form's underlying table, only the column to which the combo box is
bound.
In single form view form view this problem does not arise as all that's
necessary is to requery the second combo box in the form's Current event
procedure for it to show the relevant items for the current category in the
first combo box.
Nor does the problem arise in continuous form view if 'natural' keys are used,
i.e. the primary key of the product type table is the text field, rather than
a surrogate numeric key such as an autonumber, and the corresponding foreign
key field in the form's underlying table, i.e. the field to which the second
combo box is bound, is also a text field. This is not always possible,
however, as the text field might legitimately have duplicated vales, e.g city
names.
In neither of the latter two cases is an unbound text box needed.
You'll find a demo of how to use correlated combo boxes in both continuous
and single form view, where surrogate numeric keys are used, at:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
It uses the local administrative units of county, district and parish in my
area, but the principles are the same for any set of hierarchical entity
types. Where the demo differs from other examples you may have seen is that
the tables are fully normalized. In your case if you are storing both the
grocery category and grocery type in the form's underlying table, it is not
correctly normalized as the grocery type determines the grocery category, so
the table is open to inconsistent data. In my experience this lack of full
normalization is very common in working databases, however, so whether you
wish to normalize your table in the way that the demo illustrates is for you
to decide.
A more fundamental issue, however, is that you have separate tables for the
different grocery types. This is not good design as it encodes data as table
names. A fundamental principle of the database relational model (the
Information Principle) is that data is stored as values at column positions
in rows in tables and in no other way. Normally you'd have a single
Groceries table with a column GroceryType to distinguish between the types.
This in turn would reference a GroceryTypes table with a primary key
GroceryType, with one row for each type, Beverage, Poultry etc. The second
combo box in your form would then have a RowSource such as:
SELECT Grocery FROM Groceries WHERE GroceryType = Form![cboCroceryCategory]
ORDER BY Grocery;
Note that in the above, as both combo boxes are in the same form, the Form
property can be used rather than fully referencing the form by name.
Ken Sheridan
Stafford, England