I'm having a problem linking a form with a subform. I have a simple database that is broken down as follows:
* 3 tables (TableA01, TableA02, TableA03) which are all related as one-to-one tables
* 1 table (TableTM) which is related to TableA01 as a one-to-many
I have created a query (QueryA01_03) which looks as follows:
SELECT TableA01.*, TableA02.*, TableA03.*
FROM TableA01 INNER JOIN (TableA02 INNER JOIN TableA03 ON TableA02.Site_ID = TableA03.Site_ID) ON (TableA01.Site_ID = TableA03.Site_ID) AND (TableA01.Site_ID = TableA02.Site_ID);
to bring back all fields as one record in the one-to-one tables.
I then created a form (Form1) whose record source is QueryA01_03. Everything works great and I can easily navigate all the one-to-one tables as a single record.
The problem comes when I add a subform for the many table (TableTM). I have built a subform whose Source Object is set to Table.TableA02_TM. The Link Child field is set to SiteID and the Link Master field is set to TableA01.SiteID. When I run the form, everything appears fine. The main form displays the main record, the subform displays all the fields where the SiteIDs are equal. If increment to the next record on the main form, the subform is requeried and displays the correct information. The problem comes when I try to enter a new record on the subform. Whenever I start typing in the first form field, I get the error:
The LinkMasterFields property setting has produced this error: The object doesn't contain the Automation object 'TableA01."
When I go to look at Table TM, a new row has been created but the link key (SiteID) is blank. How can I make the subform use the main forms SiteID as it's SiteID when entering a new record?