Form with fields from multiple tables

[Guest]

Greeting all. I searched past questions before posting again, but what I
found that might help me was over my head. I have separated my one large
table into many small tables, all pertaining to telecom fiber jobs. I have
made a form on which there are fields from multiple tables. The control
source for the form is set to my table 'tblJobInfo', on which I have general
information about each job. On the form, however, there are fields from
other tables with more specific information about each job, such as fiber
types and footages. If I have field '144FiberFootage' on my form, from
'tbl144Fiber', how do I get the form to show the information from the other
table. On the form I have set the control for that field to be
'tbl144Fiber', but it still shows only '#Name?'. How do I make this work?
Is this something that a newbie can accomplish? Everyone here has been so
helpful during my struggle with this. I think you all have put me on the
right track, and I thank all of you so much.

Greg Snidow

 

[Guest]

Use a subform for any different tables you need on the form and link the
together.

 

[Ed Robichaud]

Greg - I won't repeat my response to your other related question, but the
answers overlap. Assuming that you have your data tables structured and
related correctly, related info is commonly displayed on a form by use of
sub-forms.

An example: 1- Many (1 Person - Many Payments)

Main form - Personal info (name, address, etc)

related sub-form - Dues payments (date, amt, etc.)

There is a subform/report wizard on the toolbar that will walk you through
this. Commonly, the main form is a single type showing 1 records, while the
related/linked subform is continuous/datasheet show many related records.

As you discovered, one query of many related tables can result in a
view-only dynaset. You won't be able to add/edit records because Access
can't allow you to have child (many side) records when their is no parent
(one side) record.

As a design issue, no one form or report can usefully show everything.
Think about what are the main items to show, then you can use tabs and/or
linked separate forms to let users "drill down" to all the details.
-Ed

 

[Guest]

Thanks Ed, I appreciate the simple verbal explanation.