setting up relationship (access07)

Topic

New Reply

Hi,
ran into some confusion with my table relationship so attached is a picture and heres an explanation of what is intended to happen.

currently I have 4 tables

Proposal table is what starts the data. OrderID is primary

for every OrderID there is 1 design, one well

for 1 well there can be many reservoirs

When I start to enter in data in the proposal table all tables to the rightof tblProposal get data but tblDesign to the left does not get a OrderID from tblProposal? How can I make the data flow through to all the tables

Thanks

Reply | Quote

Replies

How do you enter data? If you enter data in a table directly there is no reason why something would happen automatically in related tables.

Reply | Quote

Will in tblProposal after I enter in a OrderID a plus + appears and when I click that tblWell appears an if I enter a WellID tblResevoir appears. I may have some setting activated

Reply | Quote

Ah yes – those are subdatasheets. Each table can have only one subdatasheet, so there is no way to add the other table to the chain this way.

Subdatasheets look nice, but they have a negative impact on performance. The recommended way to treat data entry in related tables is through a form with subforms.

Reply | Quote

thanks,
off topic a bit but when making the form, I would like to have a subform for tblReservoir data in a table format so that I can add multiple entrys

Reply | Quote

You can use the Form Wizard to create a form based on tblReservoir in tabular or datasheet format. You can use this form as a subform on the main form.

Reply | Quote

ok, did that now when entering data in my form as soon as I enter data that relates to a different table I get an error at the bottom of the screen saying “Cannot add record(s); join key of table ‘tblWell’ not in recordset. Any idea what I need to do to fix this?

Reply | Quote

In fact, you need a main form based on tblProposal with a subform based on tblWell (linked on OrderID), and a sub-subform based on tblReservoir (linked to the subform on WellID).

Reply | Quote