Storing Data

[Carl Johnman]

Can someone please tell me how to store text entered into
a field in a table into another table automatically. I
need the same value updated in both fields at the same
time.

 

[Brendan Reynolds \(MVP\)]

There is no way to do this when entering data directly into tables, unless
using a server database engine such as SQL Server that supports triggers. If
you're not using a server database engine that supports triggers, you will
need to ensure that data is always entered via forms, and add code to the
AfterInsert (if you just need to record records once, when they are first
added) or AfterUpdate (if you also need to record modifications to
previously entered records) event procedure of the form to add the data to
the other table. Here's an example that copies only new records ...

Private Sub Form_AfterUpdate()

CurrentProject.Connection.Execute _
"INSERT INTO Table2 (TwoText) VALUES ('" & _
Me!OneText & "')"

End Sub

.... where 'Table2' is the name of the second table, 'TwoText' is the name
of the field in the second table, and 'OneText' is the name of the control
on the form.

 

[Brendan Reynolds \(MVP\)]

Oops! The first line in the example code should have read 'Private Sub
Form_AfterInsert()' rather than 'Private Sub Form_AfterUpdate()'

 

[Joseph Meehan]

The real question is why? Almost always when you enter the same data
into two tables it means you have a bad database design. You don't need to
answer the question for me, but do ask it of yourself. If you don't
understand ask about that or take a look at the references to normalizing
data.