Form (Table 1) with Subform (Table 2) not updating records in either table

[jreanier]

I am a NEW, NEW access user. I have created two databases that have some common fields. I've created a form using the Form Wizard to input data into Table 1. I then created a Subform so that the same information would update Table 2. This is not happening, and I'm desperate to know why. I really need a basic explanation. If it's too complex my brain will fizzle. I've been working on this for 2 weeks. Thanks!

[aytee111]

It is not going to happen automatically, you have to tell it to update the other table. In the AfterUpdate event of your main form, run an update query that updates table 2. The subform is just a place to show data, it seems unneccessary in this instance.

[NTC]

Table 1 must have a unique key field - autonumber
Table 2 must have its own unique key field - autonumber - plus - a field to hold Table 1's value as a number field (let's call this 'T1Key')

When you insert a sub form into the main form - the wizard will pre select the linking field and/or allow you to determine it

Table 2 should not hold duplicate data that exists in table 1, and as you've discovered that data also does not automatically get written - with the exception of T1Key which does get automatically written into table 2.

Relational database conceptually does not repeat the same data in multiple places. You use the link between the key fields of the 2 tables to display the data of Table 1 along with Table 2 information whenever it is needed.

There are valid cases where you want to write the data of Table 1 into Table 2 - for instance Tax Rate - - as the tax rate changes across time you don't want to rely on a link as it would change past sales info....so in this case one must literally write code that will write the value of one table into another table and that syntax and trigger all depends on the how the user interface is designed.