Autofill fields based on other fields' responses

[Danny]

In a table, is it possible for one field to auto-fill itself based on the
previous field's entry? For example, I have two look-up fields, and both are
filled with data. I would like to have the second field choose the entry that
would be linked with the first field automatically, based on the selection I
make in the first field.

So for example, the data is linked like: Field one: Cookie, Field Two:
requires flour and sugar. Is it possible to have the second field choose that
associated answer automatically, after I have chosen cookie for the first
field?

Thanks!

 

[Steve Schapel]

Danny,

The answer to this depends on your answer to this... Is there always
only one possible entry in Field Two for any given entry in Field One?

 

[Danny]

For the most part yes. Would it be possible to add that additional
functionality, of having a few different responses available to fill in field
two, based on field one?

 

[Steve Schapel]

Danny,

You would need a "master" table that references all possible
combinations of Field One and Field Two. And probably also a single
field table that lists all the possible Field One values.

So, for example...

Table: Products
one field - Product
data something like this...
Cookie
Pancake
Tart

Table: Ingredients
two fields:
Product
Ingredient

data something like this...
Cookie flour
Cookie sugar
Cookie milk
Pancake flour
Pancake butter
Tart flour
Tart sugar
Tart fruit

Ok, then the standard way to do this on your form is via "cascading
comboboxes". Make a combobox which uses the Products table as its Row
Source. Then make another combobox, using a query based on the
Ingredients table, but uses the first combobox in its criteria. The SQL
view of such a query might be like this...
SELECT Ingredient FROM Ingredients
WHERE Product = Forms![YourForm]![Product]

You need a macro or VBA code on the After Update event of the first
combobox to Requery the second combobox.

There are other considerations if this is on a continuous view form, but
the obove general approach is fine on a single view form.