Populate a text box based on a combo box

I have cascading combo boxes, first combobox dtermines what value is
displayed in the second. What I am trying to do is populate a text box based
on what is in the second combobox. Ex. 1st Combo- "Grocery Category" The
Categories are listed in one table (GroceryT) Ex. Beverage, Poultry etc.. 2nd
combo- "Grocery Type" These values are in seperate tables ex PoultryT,
BeverageT which lists all of the types of the particular type. What I would
like my text box to do is populate based on the second combo box and store it
within one of the tables. I have tried an unbound text box with the value
based on the the second column in the second combo box, it works but the
value is not stored anywhere. When you change records and go back it is not
displayed.

I hope this makes sense. Can Anyone help.

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/...g=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

Scott wrote:
I have cascading combo boxes, first combobox dtermines what value is
displayed in the second. What I am trying to do is populate a text box based
on what is in the second combobox. Ex. 1st Combo- "Grocery Category" The
Categories are listed in one table (GroceryT) Ex. Beverage, Poultry etc.. 2nd
combo- "Grocery Type" These values are in seperate tables ex PoultryT,
BeverageT which lists all of the types of the particular type. What I would
like my text box to do is populate based on the second combo box and store it
within one of the tables. I have tried an unbound text box with the value
based on the the second column in the second combo box, it works but the
value is not stored anywhere. When you change records and go back it is not
displayed.

I hope this makes sense. Can Anyone help.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201001/1

If you are storing the Primary Key of the second combo (which is what you
should be doing) you do not need to store any of the values. That said, use
the AfterUpdate event of the second combo to read the column into a bound
textbox like:

Private Sub Combo2_AfterUpdate()
Me.txtWhatever = Me.Combo2.Column(1)
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Scott" wrote in message
...
I have cascading combo boxes, first combobox dtermines what value is
displayed in the second. What I am trying to do is populate a text box
based
on what is in the second combobox. Ex. 1st Combo- "Grocery Category" The
Categories are listed in one table (GroceryT) Ex. Beverage, Poultry etc..
2nd
combo- "Grocery Type" These values are in seperate tables ex PoultryT,
BeverageT which lists all of the types of the particular type. What I
would
like my text box to do is populate based on the second combo box and store
it
within one of the tables. I have tried an unbound text box with the value
based on the the second column in the second combo box, it works but the
value is not stored anywhere. When you change records and go back it is
not
displayed.

I hope this makes sense. Can Anyone help.