Form Field Autofill from Table of Information

[Jason Lopez]

I have another one that is quite a doozey for me. I have two tables that
are connected via a 1-to-Many relationship. In order to simply the
information contained, I am trying to keep from duplicating information from
one table to another. So, I have the first table (tbl_ED) that has a
listing of people in one particular category. The second table is similar
to the first (tbl_RD). Because the people listed in both tables interact
with the other table, a third table has been created to document the
affiliations (tbl_ED-Assign). tbl_ED has the EDID# as the primary key and
tbl_ED-Assign has the recordnumber (autonumber) as the primary key and the
RDID# assigned with its corresponding EDID#.

The form I am creating has tbl_ED in the main form linked via EDID# in the
subform. What I would like to do is have a combo box that holds the RDID#
in the subform. But, also fills in some control boxes from tbl_RD so that
the specifics are seen. The only item being recorded in the subform is the
RDID#. The rest of the information is convenience and does not need to be
copied over to the tbl_ED-Assign.

I hope this helps in understanding. If not let me know. I'll try and add
some more info to see what I am trying to do a little better.

tbl_ED:
EDrecordnumber# (autonumber)
EDID#
LName
FName
Area

tbl_RD:
RDrecordnumber# (autonumber)
RDID#
LName
FName
Area

tbl_ED-Assign:
ED-Assignrecordnumber# (autonumber)
EDID#
RDID#

Hope there is some help that someone can offer so that I don't have to copy
data multiple times for the same thing.

Jason Lopez

 

[Jeff Boyce]

Jason

Like Bonnie points out, using tables like that is kind of how you'd handle
this ... if you were limited to using a spreadsheet.

Access is a relational database, though, so why not use a single table for
persons.

Then, if you need to associate one person with another, use a second table
that holds the two personIDs. If their association needs to be categorized,
add a field to that second table to hold the category.

(and just in case there's ever a new category, create a category table and
use the CategoryID in that record.)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jason Lopez]

Awesome. I didn't think about the the idea of using a category table.
Thanks for the help. I'll see shortly how well it works.

Jason