How do i enter a value into multiple tables by entering it once?

[chappy22]

I have one main table which has a AccountNumber field, the primary key. This field is linked to many other tables. I want to be able to enter the value into the main table and have it dispersed to all the other tables, in which it is linked with. How do i do that?

P.S. I have linked the fields, enfoced referential integrity, and checked of cascade update related fields. This did not automaticall update the fields in the rest of the tables for me.

Please help.

Chappy

 

[BNPMike]

Normally you wouldn't expect a key to be created in any other tables until events/records occurred connected to those tables. Eg If you add an account, you wouldn't expect to add that to the order table until an order was raised.

Either you trying to create empty records or you've got a funny data model. Tell us more.

 

[willir]

Well, knowing the table design would be useful.

Is the AccountNumber the primary key in the one parent table and the foreign key in the other tables? This would be one possible scenario.

Another scenario would be where the account number was used as a decriptive field on the parent table which used an internal number for the primary key. The internal primary key would be used as the foreign key for the related tables.

Example
tblAccount
AccountID - primary key
AccountNumber - text
etc...

This way, changing the AcountNumber on the one table would be reflected on all the other tables linked to the parent table.

...Moving on
Typically, when you create the parent record, the child records are not automatically created. For example, a parent can have one, two or more ... or associated no child records.

There are three typical ways to create the associated records. Before proceeding, it is extremely useful to formally create your relationships with the Relationships GUI tool (from the menu, "Tool" -> "Relationships").

A) Create the parent record on the main form, and then created the child records in a subform(s). If you formally create the relationships before hand, when you add the subform to the main form, Access will know how to link the records using the Primary <-> foreign keys.

B) Create the parent record in on form. Afterwards, create the child records in other forms. Link the child to the parent using a combo or list box.

C) Programatically. Create the parent record. Click on a button that automatically populates the child records. You have to have a good idea on what you need, and a bit of programming experience to accomplish this approach. It is not too difficult to achieve a simple procedure. But if it gets complicated, then the coding becomes tougher too.

Richard