writing data to 2 tables

[Guest]

I am presently using forms to write to one table. I would like to duplicate
that table, give it a different name and have a form write to both of them at
the same time. This would mean I can have both tables saved in different
locations giving me a backup of the data. Obviously one would be chosen for
viewing.

How is this done?

thanks

 

[Guest]

Why don't you back up your data every day, instead of creating a form that
save into two tables?

For your question, I assume that you'll need to open the second table and
saving the record, so on the form After Update event write the code

Dim DB as Dao.DataBase, MyRec As Dao.RecordSet
Set DB = CurrentDb
Set MyRec = DB.OpenRecordSet("Select * From tableName Where
KeyFieldNameInTable =" & Me.KeyFieldNameInForm)
If MyRec.Eof Then
MyRec.AddNew
Else
MyRec.Edit
End If
MyRec!Field1InTable = Me.Field1InForm
MyRec!Field2InTable = Me.Field2InForm
MyRec!Field3InTable = Me.Field3InForm
MyRec!Field4InTable = Me.Field4InForm
MyRec.Update

=================
Note, I didnt try the code, but I hope it will give you the right directon

If the KeyField is a tex field, change the select to

Set MyRec = DB.OpenRecordSet("Select * From tableName Where
KeyFieldNameInTable ='" & Me.KeyFieldNameInForm & "'")

 

[John Vinson]

I am presently using forms to write to one table. I would like to duplicate
that table, give it a different name and have a form write to both of them at
the same time. This would mean I can have both tables saved in different
locations giving me a backup of the data. Obviously one would be chosen for
viewing.

How is this done?

Not very easily. You'll need VBA code to open the second table (linked
to a second database) as a Recordset, use the AddNew method, and add
each field:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("BackupTable", dbOpenDynaset)
rs.AddNew
rs!ThisField = Me!ThisControl
rs!ThatField = Me!ThatControl
<etc>
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

As a rule, you're probably better off backing up the entire database
frequently, *outside* of Access.

John W. Vinson[MVP]