Adding related records (2000)

[kevrizz]

I have 4 tables, 3 of which are linked via a one-to-many relationship to the main table. The link is a field caled Company name. I am trying to work out how I can add a new company to the main table and have that company automatically added to the other tables. I am currently adding the new company via a form but cannot get the other fields to update.

[WendellB]

One solution is to use a query to join the tables together, but with 3 linked tables I suspect your query may not be updateable. It's worth a try though, as the other alternative involves writing a bunch of code to do appends to each of the tables after the main table is populated. If you try the join approach, make sure all the joins are equi-joins.

You may want to look at your table design too. If all tables require data all the time, would it make sense to just have one table and eliminate the three linking fields? Normalization can be a tricky issue. Hope this helps.

[MarkLiquorman]

>>I have 4 tables, 3 of which are linked via a one-to-many relationship to the main table. The link is a field caled Company name. I am trying to work out how I can add a new company to the main table and have that company automatically added to the other tables. I am currently adding the new company via a form but cannot get the other fields to update. <<

I'm having some problem understanding what you are trying to do, maybe it is your terminology. Something doesn't sound right from what you are describing. What do you mean by " have that company automatically added to the other tables" and "cannot get the other fields to update"?

[kevrizz]

Mark
One table is Company Info with Company Name as Primary Key. This is the foreign key in 3 other tables for Training History, Contact Details and Business Units. Links are one-to-many.
I am trying to set up a form so that users can add a new company to Company Information. At the same time I'd like that company to appear in the other tables. This is so that the details from all tables can be seen in a form with subforms for the other tables.
At present if I add a new company the subforms for Training History, Contacts and Business Units are blank i.e. no fields at all, not no data.
Hope this explains my problem.

Kevin

[MarkLiquorman]

You will have to manually insert a record into each of the child tables, if you want an essentially "blank" record to appear for in each of them for the new company. You could use append queries, firing them during your form's AfterInsert event. However, if any of the fields are required and don't have default values, the append queries will fail.

[kevrizz]

Thanks Mark
It's amazing how clear things become after discussing it with others. My answer is to do exactly as you said, to manually enter the values via some code. The form prompts the user for a new company name and upon entering it it is added to the other tables. Thanks again.

[kevrizz]

Wendell, I've adopted your "bunch of code" suggestion. I use a form to collect the new name and then have it added to the other tables. Thanks.