Can I copy a recordset form multiple tables?

  • Thread starter Thread starter Tlm
  • Start date Start date
T

Tlm

Hello All,

I have a form (FrmA) with a subform (SubFrmB) embedded in it. SubFrmB also
has a subform embedded in it (SubFrmC) The form's recordsource is based on a
table (TblA). SubFrmB's recordsource is also based on a table (TblB).
SubFrmC's recordsource is also based on a table (TblC).



There is a one-to-many relationship between TblA (one) and TblB (many).
There is a also a one-to-many relationship between TblB (one) and
TblC(many). The forms are similarly linked.



I want to be able to copy the recordset in SubFrmB - along with its many
records in SubFrmC - so I can then insert that recordset as a new record in
FrmA.



I considered using the RecordsetClone function for this. But since the
RecordsetClone property setting makes a copy of the underlying table
specified by the form's RecordSource property, that won't work for me since
there are three tables in play here.



Any and all suggestions on how I could possibly accomplish this would be
GREATLY appreciated!



Thanks!!!



Tom
 
If you already stored data in related Records in TblB and
TblC, why would you want to store the same data in TblA???

Besides, you want to store multiple Records from TblB and
TblC into a single Record in TblA, how do you propose to
fit all of these bits of data (from TblB & TblC) into a
single Record in TblA???

Suggest you read up on Relational Database Design Theory
and see if what you want to do is the right thing to do
according to the RDDT.

HTH
Van T. Dinh
MVP (Access)
 
I want to be able to copy the recordset in SubFrmB - along with its many
records in SubFrmC - so I can then insert that recordset as a new record in
FrmA.
Click to expand...

Well... that's THREE recordsets, not one. And I really must wonder why
you would apparently want to store multiple copies of (apparently)
identical records redundantly!

You'll need to run three Append queries, which can if you wish all be
wrapped in a single transaction; but I'd be curious to know why this
operation is necessary!
 
John,



Thank you for your posting. The situation I'm facing with this issue is
this:



This database tracts publications. The main form (FrmA - TablA) maintains
data regarding an overall ORDER to be sent to a printer; order #, name,
date, etc. The subform in the order form (SubFrmB - TblB) maintains data
regarding particular JOBS in the order; colors to be used, paper weight,
etc. (there are often 20 or more jobs to an order). The subform in the jobs
subform maintains data regarding the DISTRIBUTION of the jobs; how many go
here, how many go there, etc. (there are often 20 or more separate
distribution destinations for each job).



Many jobs are often very similar, but have one or two different values in
some fields. The user wants to be able to 'copy' a job (along with its
distribution data) and 'paste' it in a new order - then modify as required.
The intent is to save the user all that redundant data entry, and potential
type-Os, (there are over 50 fields on the job - SubFrmB - form alone). So,
it's not exactly redundant data. But nearly.



I take your point about there being actually THREE recordsets here. And
your recommendation that I use three separate append queries sounds logical.
But how?



Thank you again!!!



Tom
 
I take your point about there being actually THREE recordsets here. And
your recommendation that I use three separate append queries sounds logical.
But how?
Click to expand...

You'll probably want to have three stored Append queries, for the
three different tables; they could all have a criterion such as

=Forms![YourMainForm]![YourUniqueID]

referencing the control on the main form which identifies the job.

These queries can be executed from VBA code or from a Macro in the
Click event of a "Clone This Record" button. This code might be
something like

Private Sub cmdClone_Click()
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Err
Set db = CurrentDb
Set qd = db.Querydefs("qryAppMain") ' append to main table
qd.Execute dbFailOnError
Set qd = db.Querydefs("qryAppFirstSub") ' first related table
qd.Execute dbFailOnError
Set qd = db.Querydefs("qryAppSecondSub") ' next
qd.Execute dbFailOnError
Proc_Exit: Exit Sub
Proc_Err:
<enter error handling code here>
End Sub
 
John,

Excellent! I'll give it a try. I very much appreciate your help!!!

Tom


John Vinson said:
I take your point about there being actually THREE recordsets here. And
your recommendation that I use three separate append queries sounds logical.
But how?
Click to expand...

You'll probably want to have three stored Append queries, for the
three different tables; they could all have a criterion such as

=Forms![YourMainForm]![YourUniqueID]

referencing the control on the main form which identifies the job.

These queries can be executed from VBA code or from a Macro in the
Click event of a "Clone This Record" button. This code might be
something like

Private Sub cmdClone_Click()
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Err
Set db = CurrentDb
Set qd = db.Querydefs("qryAppMain") ' append to main table
qd.Execute dbFailOnError
Set qd = db.Querydefs("qryAppFirstSub") ' first related table
qd.Execute dbFailOnError
Set qd = db.Querydefs("qryAppSecondSub") ' next
qd.Execute dbFailOnError
Proc_Exit: Exit Sub
Proc_Err:
<enter error handling code here>
End Sub
Click to expand...
 
Back
Top