Form Field Autofill from Table of Information

  • Thread starter Thread starter Jason Lopez
  • Start date Start date
J

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
 
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
 
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
 
Back
Top