How to copy a recordset!?!?!

[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

[Bob Quintal]

"Tlm" <t.*******@comcast.net> wrote in
news:hvdsb.174780$Fm2.153056@attbi_s04:

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.
What you want to do is only possible if the three tables have
identical structures. You are seesntially talking that you want to
copy a row from table B into Table A and the row(s) from Table C
into table B. If your tables are identical, they should be merged
into 1. then you don't need to copy anything, just find the row in
form B and give instruction to form A to move to that row.

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!
I think we need to understand the purpose of your forms so that we
can find an alternative solution.

Bob Q.
Thanks!!!

Tom

[Tlm]

Bob,

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



"Bob Quintal" <bq******@generation.net> wrote in message
news:b1******************************@news.teranew s.com...

"Tlm" <t.*******@comcast.net> wrote in
news:hvdsb.174780$Fm2.153056@attbi_s04:

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.

What you want to do is only possible if the three tables have
identical structures. You are seesntially talking that you want to
copy a row from table B into Table A and the row(s) from Table C
into table B. If your tables are identical, they should be merged
into 1. then you don't need to copy anything, just find the row in
form B and give instruction to form A to move to that row.

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!

I think we need to understand the purpose of your forms so that we
can find an alternative solution.

Bob Q.

Thanks!!!

Tom

[Bob Quintal]

"Tlm" <t.*******@comcast.net> wrote in
news:Xursb.181803$Fm2.164376@attbi_s04:

Bob, 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?
So you are not moving the recordset of table B into table A, but
making a copy in table B.

How do you compose order#, job# and Distribution#? and what is the
nature of the relations between tables. Does the distribution table
have the job # as a foreign key? To tell you specifically how to
clone the records, we need to know this,.

You will definitely need a good bit of code here to do what you
want.

I'd plan it out this way.

Create a new Order in table A. this gives you a new order number.
Have a "clone order" button on the form that asks for a clonefrom#,
copies all the records for table B having order# = clonefrom#, but
replacing order# with cloneto#.

Then the code selects all the records in table C that have a Job#
record that has the cloneto# as its order#. Now you have to insert
the records into Table C again, but update the job#s in tables B
and C to reflect the new job#s.

Thank you again!!!

Tom



"Bob Quintal" <bq******@generation.net> wrote in message
news:b1******************************@news.teranew s.com...

"Tlm" <t.*******@comcast.net> wrote in
news:hvdsb.174780$Fm2.153056@attbi_s04:

> 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.

What you want to do is only possible if the three tables have
identical structures. You are seesntially talking that you
want to copy a row from table B into Table A and the row(s)
from Table C into table B. If your tables are identical, they
should be merged into 1. then you don't need to copy
anything, just find the row in form B and give instruction to
form A to move to that row.

>
>
>
> 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!

I think we need to understand the purpose of your forms so
that we can find an alternative solution.

Bob Q.

>
>
>
> Thanks!!!
>
>
>
> Tom
>
>
>