Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-14-2007, 03:01 PM   #1
japi_s
Registered User
 
Join Date: Feb 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
japi_s is on a distinguished road
Question How to save data to a second table?

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

japi_s is offline   Reply With Quote
Old 02-14-2007, 09:09 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,354
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
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.
The_Doc_Man is offline   Reply With Quote
Old 02-16-2007, 07:05 AM   #3
Moniker
VBA Pro
 
Join Date: Dec 2006
Location: Dallas
Posts: 1,567
Thanks: 0
Thanked 10 Times in 9 Posts
Moniker will become famous soon enough Moniker will become famous soon enough
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.

__________________
~Moniker

(If you've been helped by me or anyone else, please add to their reputation by clicking the "scales" icon in the upper-right.)
Moniker is offline   Reply With Quote
Old 02-16-2007, 07:13 AM   #4
GaryPanic
Smoke me a Kipper,Skipper
 
Join Date: Nov 2005
Location: England
Posts: 3,180
Thanks: 5
Thanked 7 Times in 6 Posts
GaryPanic will become famous soon enough
coming in on the end of this - if you need a history table then check this in samples

G.

GaryPanic is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting Data From A Table Using SQL inside VBA Module Dexsquab Modules & VBA 2 05-01-2007 07:00 AM
[SOLVED] How to pass data from one table to the next? suzannemonette General 5 08-17-2006 07:31 AM
Problems with recording data in table through forms Mumus Tables 1 07-06-2006 12:06 AM
getting form data to copy from one table to another michaeluk Forms 0 12-13-2005 01:14 PM
Extracting data for pivot table Belinda Li Queries 0 10-19-2004 10:35 AM




All times are GMT -8. The time now is 06:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World