Field Filling In Based on a Common Field in Another Table

[Guest]

I have two tables with two identical fields. On table 2, by selecting a
choice in the combo box in field 1, I want it to automatically fill in the
data for the corresponding choice from Table 1, field 2. For example if in
Table 1 I have:

Number Description
1 XYZ

On Table 2, when I select 1 from the Number field combo box, I want XYZ to
automatically fill in under the Description field.

 

[Allen Browne]

In general, you would not want to do that.

Unless you want to have "XYZ" as the default Description, but allow the user
to change it to something else, that's not the way to go. For example, if
the description for item 1 was "Claw Hammer", and you were quite happy for
your users to sell item 1 but change the description so it reads "DVD
player", then you could use the AfterUpdate event of the combo box to
DLookup() the table to get its Description and assign that string to the
Description field on your form. For an example of how to do that, open the
Northwind sample database, the Orders Subform, and look at the AfterUpdate
event of the ProductID combo.

In most cases, though, this would lead to very bad data, and lots of
maintenance problems trying to keep all the redundant copies of the
descriptions all up to date whenever any change is made in one place. To
avoid these issues, you would create a query that combines both the main
table and the lookup table (the combo's RowSource). This query can get the
Description from the combo's table, and display it in your form or report.
The main table never stores this value, and so you avoid all the problems.

For more info, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html