Form from three diff tables?

[Guest]

I can successfully create a form that allows me to both view and edit data in
fields that refer back to TWO different linked tables in Access 2000 – but
when I try to do this with fields from THREE tables, I can view but NOT edit
the data.

Background: I have a table of “demographics†in which the primary field is
social security number. It is a “master†table is linked directly to three
other tables by social security number, which is a foreign field in all three
other tables. Let’s call the other tables A, B, and C. A, B, and C are
bnot linked to each other. The links between “demographics†and A, B, and C
all one-to-many, with referential integrity enforced and cascade updates and
deletions enabled (though my problem still happens when the cascades are not
selected).

When I create a form in which fields are derived form “demographics†and A,
or “demographics†and C, all is fine. The Wizard gives me the option of
having the second table as a linked form or a subform – I select the latter –
and when the form pops up, I can click through the records and see the values
change appropriately. I can put a cursor in a field and change the data, and
the change is made in the actual table, I’ve checked. The “asterisk arrowâ€
button for adding a new record is functional.

When I use the Wizard to create a form with fields from, say, “demographicsâ€
plus A AND C, the Wizard does not give me the option of “subfield or linked
formâ€. It just creates a single “dumb†form. All selected fields are
visible and existing records can be clicked through – but when I put a cursor
into a field and try to enter data I get no action and a “ding†sound (no
error message). The "new record" button is grey and nonfunctional. I’ve
checked to ensure that all the data entry and addition permissions for the
form are set to “yesâ€.

Any suggestions?

 

[Roger Carlson]

This is a little to complex for the wizard, but you can do it yourself.
What you want (as I understand it) is a main form with 3 subforms, one for
each of your other tables (A, B, and C). If you like, create the form for
Demographics and subformA. Now, create two other subforms and save them.
At this point they'll just be forms in continuous or datasheet view. Make
sure the Record Source for these forms contain the foreign key to
Demographics.

Then, just click and drag the subform onto your main form. Access will
automatically drop it into a subform control and set the Link Parent/Child
properties as long as it can recognize what the field are. (Which it should
if you create the Relationships properly.)

That should be all you need to do. If there isn't enough screen real estate
for all 3 subforms, you can add a tab control to your form and put each
subform on a separate tab.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L