How to save data to a second table?

[japi_s]

Hi Guys,
What i am trying to do is, i have two tables called Table1 and Table2.

I have created a form called Form1.
This Form1 has all the fields from Table1.

What i want to do is, as soon as a user fills in the details in Form1, obviuosly it saves those details in Table1, BUT i want it to save a couple of field values into Table2 as well.

How do i go about doing this??

In Table1 i can access the fields by "Me.[Fieldname]" (from the VB script), but how do i access Table2 OR how do i save data to Table2 from Form1.

Thanks

 

[The_Doc_Man]

You can try VBA recordset operations. Open the second table as a recordset.

OR - if it makes sense to do so, JOIN the two tables and update the QUERY instead of the table. If the "couple of fields" are the join keys, you can make this work. If not, oh well.

The other alternative is tricky. Add a flag field to your table 1. By default it should be FALSE. When you update table 1 through the method that you want to use, have the AfterUpdate event of the form trigger a macro that

1. Inserts all of the correct fields into table 2 based on the default state of the flag. In other words, an Insert Into (look this up in the help files) with a WHERE clause that references the default state of this flag. Just because you used the flag to select records doesn't mean that the flag has to be copied into table 2, either.

2. Resets all flags in table 1 to show that table 2 now contains this entry.

 

[Moniker]

Out of curiosity, why would you want to store the exact same data in more than one table? That pretty much breaks normalization rules. If you need to access the same information in more than one context, it should be setup as a lookup table and referenced that way. Otherwise, the moment you change the data in one table but it doesn't get changed in another, you've lost data integrity.

 

[GaryPanic]

coming in on the end of this - if you need a history table then check this in samples

G.