Add one record to two different tables with one form 1

[MICKI0220]

I need to make a record add to two different tables by only entering it into one form. How can I do this?

 

[RivetHed]

I'd say the easiest way would be to unbind the fowm from the table and then set the save record button code to something like:

dim db as database
dim rs as recordset

set db = currentdb
set rs = db.openrecordset("MyTable1")

with rs
.addnew
!MyField1 = txtbox1
!MyField2 = txtbox2
'Repeat for however many fields you need to fill
.update
end with

set rs = db.openrecordset("MyTable2")
'repeat steps above to add record

set rs = nothing
set db = nothing

 

[MajP]

I will have to strongly disagree that is the easiest. Working with unbound forms requires a lot of programming that a bound form does natively. By far the easiest solution is to bind the form and run an update/append query into the other table.

However, the real question is why? This hints at a non-normalized data structure. Unless you have a strong reason, databases store unique information 1 time. If possible, a redesign of your tables is probably the correct solution.

 

[MICKI0220]

Thank you, that is exactly what I needed. Works perfectly now.

 

[missinglinq]

MajP's question remains; Why are you storing redundant data in a second table?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[MICKI0220]

I am actually only storing the same primary key so that they can be joined. One field inputed in a record. Something happened to the query that allows to update one table when I moved both those tables to an SQL database. It does not allow me to edit the table if the joined tables don't have a matching primary key, even though I am not storing any info in the second table.