Populating a field from a separate table

[EJH]

I have a Database that has three tables. One of the three is just a table
that contains three fields and is filled with reference information. One
field is 3-Digit(primary key), the next is Office, and the last is Area.
The next table has the same type of headings plus several other fields but
is not filled with data. I want to be able to create a form and enter a 3
digit number in the 3-Digit field of the second table and have it extract
information from the first table to fill in the Office and Area fields of
the second table. There will be more information in the second table, but I
just need to know how to populate these two fields. I have tried different
functions, including a Dlookup, but I must be putting it in the wrong place
or creating the syntax wrong. I am a newbie and appreciate any help.
Thanks.

 

[Guest]

You can do this but you should first ask yourself whether you should do it.
If for a value of the 3-digit number in a row in the second table the values
of Office and Area will always be the same as those for the row with that
value of the 3-digit number in the first table then you should not have
Office or Area columns in the second table. To do so constitutes redundancy
and leaves the database vulnerable to update anomalies.

The correct model would be to have just the 3-digit number column in the
second table as a foreign key column and, when you need to know the values of
Office and Area for a row in the second table pull them in via a query which
joins the two tables or by looking them up from the first table; hence no
redundancy, no possible update anomalies.

As far as your form is concerned I'd suggest that you use a combo box bound
to the foreign key 3-digit column with a RowSource property along these lines:

SELECT MyID, Office, Area
FROM Table1
ORDER BY MyID;

Where MyID is the primary key 3-digit column. You can order the list
however you wish, not necessarily by MyID. Set its other properties as
follows:

BoundColumn 1
ColumnCount 3
ColumnWidths 1cm;3cm;3cm
ListWidth 7cm

Experiment with the dimensions for the last two properties to get the best
fit; the ListWidth should equal the sum of the ColumnWidths dimensions
however.

Add two unbound text boxes to the form with ControlSource properties which
refernce the columns of the combo box's list:

=YourComboBox.Column(1)
=YourComboBox.Column(2)

The Column property is zero-based so 1 and 2 are the second and third
columns, i.e. Office and Area. Consequently, when you select an item from
the combo box's list the 3-digit number will show in the combo box (and
stored in the foreign key column to which its bound) while the two text boxes
will show the corresponding Office and Area values.

If by any chance (though I'd strongly doubt this isn't the case) for a value
of the 3-digit number in a row in the second table the values of Office and
Area will NOT always be the same as those for the row with that value of the
3-digit number in the first table, but could be legitimately edited once
you'd pulled them in from the first table, then you would need to store them
in columns in Table2. In this case the setup would be much the same as above
but the two text boxes would now be bound to the Office and Area column in
Table 2 rather than referencing the values in their ControlSource properties
(which would now be the names of the two columns). To push the values into
them you'd use code in the AfterUpdate event procedure of the combo box:

Me.Office = Me.Me. YourComboBox.Column(1)
Me.Area = YourComboBox.Column(2)