Adding records to a separate table based on inputs to a form

[ramprat]

Hi All,

I am using Access 2003 and have a continuous form based on a table (table1) as a subform on another form. I simply want to be able to take any records that get added to table1 through the subform and populate a 2nd table (table2) with these added records from the subform the instant they are added. The two tables both contain a common ID field. Also if any values in existing records on the subform change I would like to update a calculated field in table2 using these new values. How can I do this using VBA? I'm stumped.

Any help would be appreciated. Thanks

[missinglinq]

The big question here is WHY? Storing most data in more than one place is in direct violation of all the principals of relational database design; storing entire records certainly is. You probably need to take a look at Database Normalization and Table Structure.

Welcome to Bytes!

Linq ;0>

[ramprat]

Thanks Linq. My first table stores certain parameters about a road. The second stores calculated values that are calculated from the parameters in table 1. This makes the fields in table 2 dependent on fields in Table 1 as thus should be stored separately (I believe). As I add new roads to my first table through my continous form I need to add a record to the second table which will have the common road_id value that links the two tables together. I was hoping to be able to have this work automatically every time a new road is added to the first table through the form.

I hope this makes it clearer. Thanks

[nico5038]

As stated, storing redundant data is against normalization rules. Storing calculated values isn't "normalized", but sometimes, when the calculation done is lengthy, necessary.
As you found out, changing values cause trouble :-)

The solution is however "simple", just create a query with the calculations to get a proper endresult. When this query is too lengthy to use for "direct reference" just change it into an INSERT INTO query and fill a table (e.g. overnight) and use that.
When the original data changes, just rerun the query to build a new table.

Nic;o)