Autofill From 2 tables

[C.R. Houk]

Having a problem working this out, and I am a bit weak in
the VB side of things. Using Access 2002.

Essentially I want to select a name from a combo box which
the source is in the <Name> table. On the name table,
employee names are listed along with Employee age and
section working in. When the name is selected I want the
form to autofill 2 blocks the <Age> block and the
<Section> block with the data from the Name table. I do
not want to link the tables as I want an individual record
in the new table titled <Test>for each person every time I
fill out the form. The purpose is to have a separate
entry for every time the individuals do the test. Any
help is greatly appreciated.

C.R. houk
charles.houk<remove>@charleston.af.mil

 

[Roger Carlson]

Disclaimer: You should not copy the additional information into another
table. Simply putting the Name (or Employee number) is sufficient. You can
link back to get the other information anytime. For that matter you
shouldn't be storing the Age. Store the birthdate and you can calculate the
Age anytime you want.

That being said, one way to do what you want is to create your combo box
with three columns in the Row Source. Include Name, Age, and Section in the
query that builds the Row Source. Set the Column property to 3 and size the
columns properly. Have the combo box Control Source be the Name field.
Call the combobox cboName

Now create two new text boxes (txtAge and txtSection) bound to their
respective fields. In the AfterUpdate property of the Combobox, have code
something like this:

Me.txtAge = Me.cboName.Column(1)
Me.txtSection = Me.cboName.Column(2)

Columns are numbered starting from zero, so these are the second and third
columns respectively.