Child table/subform

[FBrnstrmr]

Hi all,
I am trying to clean up a subform/form/parent table/child table issue. I
have a form which relates information regarding a property (20000 or so
actually). When I do an inspection of one of these properties there are
numerous records created which I need to associate with that property. So I
have an Inspection form which lists the property in question and a subform
which lists the various items I need to inspect and record any deficiencies.
I need to relate the subform with the form and am not sure how they get
linked. Am I to have a UniqueID# on each record of the subform and if so how
do I link it with the unique Id on the form. Although the address would be
the unique info on the form it is not unique in the source table, it is
unique in the control source table. Also do I only have to relate the tables
through the address?or do I need an address field in the child table as well
as the parent table? Hope that is clear enough. Thanks for any help in
advance,
-fred

 

[Barry A&P]

Fred
On your address table you could use something like addressID Set to Auto
Number as the primary Key and use addressID as a Foreign Key on your
Inspection items Table be sure to use "Number Long Integer" as data type.
Relate these and enforce integrity. then in your subform properties under
the Data tab
Set linkMaster fields to AddressID
and Link Child fields to addressID
If you want to link the tables by address field and have address as a
foreign key on your inspection items table you could but your address field
would HAVE to include city and state as there are a lot of 232 1st Streets
out there and i dont believe this is a common method..
You could also use the subform wizard in the tool box and it will ask you
what fields to link the tables on..

Good luck

 

[Fred]

Couple extra notes from the peanut gallery.

I would think this through in terms of the structure and organization of
your information, then of your tables.

One more thought....here's a way to look at it that might unconfuse it a
little. . We say linking tables, but fundamentally the links are between
records. The "preface" to creating the link is a table with the right
fields in it to do the following. The main job of linking is to place a
value into the FK of a record which corresponds to the value in the PK of the
other record. The the epilog of creating the link is "drawing the line"
(literally or figuratively) between the tables in the design view, a query, a
form etc.

 

[FBrnstrmr]

That might have helped had I known what a PK and an FK are.
Thanks though!

 

[FBrnstrmr]

Whoops! Parent key and foreign key. Sorry about that and Thanks!!

 

[FBrnstrmr]

Thanks Barry, I chucked what I had tried and followed your formula sort of,
and it works as required. Now I just have to expand my duo-cascading combo
boxes into quad cascading combo boxes and all will be tickity boo!
Thanks very much,
-Fred