Spltting tables, automatically update tables with related records

[oggsta]

I have split a table to limit the data in each table.

I have then created a one to one relationship (referential integrity, cascade updates, and cascade deletes) between the primary table and split table, using the field name ID which is the primary key for both fields.
Autonumber for primary field ID
Number for secondary field ID.

When i go into the primary table/form and enter a records I would like a record to be automatically entered in the secondary table/from without actually entering data in this record given the relationship.

I assumed that if you ticked cascade updates it would automatically update and include the record.

Any suggestions on how to achieve this would be appreciated.

 

[RickSpr]

Cascade Updates does not do what you expect. What it does is, when you update the relationship columns in the parent table, it propagates the update to the foreign key in any related child table rows.

There are three reasons this won't work for you:
1. Inserting the key is not the same as updating it.
2. It updates only the child table's key columns in existing rows. It doesn't create a row; that's not its purpose.
3. You can't update the key in the parent table, because it's an Autonumber.

What you should do is base your form on a query that joins the two tables. If you don't have any controls bound to the split table, or if you have controls but no data is entered into them, the record in the split table won't be created automatically. Therefore, you need to force the record to be created by updating its key. To do this, create an invisible text box bound to the split table's ID field. In the Form_AfterUpdate event, copy the main table's ID text box (which may also be invisible, if you like) to the split table's ID text box.

Note that all non-key columns in the split table must be Required=No for this to work.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein