By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,377 Members | 3,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,377 IT Pros & Developers. It's quick & easy.

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

P: 60
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
Oct 20 '08 #1
Share this Question
Share on Google+
3 Replies


missinglinq
Expert 2.5K+
P: 3,532
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>
Oct 20 '08 #2

P: 60
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
Oct 20 '08 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 20 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.