Append records from 2 tables with a 1:1 relationship

[driver7408]

I would consider myself at a novice level when it comes to VBA. Mostly basic functions, but not declaring variables much yet.

I have a db using 3 large tables with 1:1 relationships. Before you question my normalization, I stress that these tables are strictly used to temporarily store user input information for a highly automated form with several hundred fields. All of the bound fields enable the form to be reopened and retain the information originally entered. The information is then transferred to a report and converted to a .pdf where it is filed; these 3 table's records are deleted weekly with a delete query. Other than a few combobox row source queries, these 3 tables are not connected to the rest of my db. At this point the form is functioning perfectly.

What I am trying to do is save about 3-5 of the partial (records) on 2 of these tables for indefinitely, no longer needed, or overwritten. Reason being is that partial information (records) on this form can be recalled and duplicated into a new form, saving the user time from having to completely re-enter some fields that will not change week to week.

So far, my best idea is to have 2 duplicate tables that store these records (based on a checkbox on a scheduled query event) that can be recalled and written back into the 2 main tables with a new primary key when needed. Append queries only work with one table at a time, and I need to maintain the integrity of these records 1:1 across the 2 tables with their primary keys intact.

If any of this is not clear, I can clarify.

I know there are many ways do do this; Does anyone have some suggestions on the most efficient way I can do this programatically?

Thanks

 

[Mihail]

Before you question my normalization

Ok, no question even if was my first guess, and the second, and the third ....
Append records, one by one, by using a Do-Loop cycle.

 

[namliam]

How do the primary keys work? are they autonumber(s)?

Ultimately your intrest will lie in Mihail's suggestion of running append query's from a little bit of VBA maybe using a Max() for getting the (new) key from the first table.
You can simply design the append query in the designer and run them from code 1 by 1.

Currentdb.execute "Query1"
Currentdb.execute "Query2"
Currentdb.execute "Query3"

I hope this helps you

 

[driver7408]

It has primary keys on all 3 with 1st being an autonumber. I was thinking of incorporating a checkbox in the form. When the checkbox is true, the record will query up later via combobox on the switchboard. If selected, the common fields from the old record will be appended into the new record with a new autonumber primary key.

 

[namliam]

Yeah that you can do with append queries, though you will have to requery the form to get the results visible...

You can also "simply" query the "storage" table and write the data into the form, let the form take care of creating the autonumber key and the "foreign keys"
doing something along the lines of the following aircode

dim rs as dao.recordset
set rs = currentdb.openrecordset("Select * from yourTable where .... = " & yourcombobox)
me.SomeField = rs!SomeField
Me.AnotherField = rs!Anotherfield
rs.close
set rs = nothing

 

[driver7408]

I found something that works great, in case someone else ever stumbles across this.

After fighting with several queries and VBA, I ended up going with Allen Brown's "Duplicate the record in form and subform" off his tips and tricks website.

I used the exact same code on that website, changing the fields and queries as appropriate. It ended up working perfectly. No need for any additional queries or tables. I have it where the user can check a checkbox in the original form if it is a commonly used configuration. The commonly used forms can be selected specifically from the main menu combobox. Once that form opens, they can click a button on the form that opens a new record and transfers all of the common info into the new record, using the code I got from that website.