Duplicating a Record from a Form that has Subforms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that contains 8 subforms (organized by tabs) and each subform
has it's own table. Each subform table is linked to the master through an ID
column. Data is entered into the form and then saved. All together on the
form there is about 200 fields. There are times when a new entry is very
similiar to one that's already been created, so I would like to have a "Make
Copy" button on the form to make a copy of all the records. The tricky part
is that it needs to copy all of the records in each of the 8 tables and the
linked ID column needs to have the same number so the new records stays
linked together. I'm wondering what the easiest way to do this would be.

Thank you for any suggestions
 
Duplicating records is always tricky but is requested often by business/the
customers.
Presume yr situation is:
form/subform shows an old record, and on the form there's a button to create
a new (main) record, which should be a duplicate of the current, old record,
correct?

Several ways, one of which
You could use (ADO or DAO) (SELECT * from WHERE...) recordsets to bring up
all the information, and populate other recordsets (INSERT INTO ...)
responsible for creating new records.
You need to do it cascading, so for all the subtables, tied to the
maintable.
After you've executed these actions, if yr datamodell is oke, and yr
form/subform design is ok, the only thing you need to do is navigate yr
mainform to the newly created record.

This is recordset-driven but you can also do it form-driven.

Krgrds,
Perry
 
The process is:

1) copy the main record to a new record via a append query
2) obtain the new "id"

3)....to number of child tables
run a append query on sub table 1 that selects all id in this child table,a
and updates the
parent_id (foreign key value) to the "new" parent id (this "switch" of the
id" to
use can be done DURING the append query).

Repeat 3) above for all 8 child tables....

This is one case where I would actually recommend the use
of a global function to return a ID value. The reason why I
suggest doing this is then you can use the query builder
to build all 8 queries, and include the new "id" as a function
expression in that query. This will save a TON of code.

So, you need to write/build 8 append querys.....

The code would look like:

Dim strSql As String

' main table copy..
strSql = GetMySql("qryAMain")
strSql = strSql & " where Contacts.contactID = " & Me!ContactID
CurrentDb.Execute strSql, dbFailOnError

' get the new id
strSql = "select @@IDENTITY from Contacts"
gbllngNewID = CurrentDb.OpenRecordset(strSql)(0)

'NOW, run each of the append quiers...

' child table 1
strSql = GetMySql("qryAChild1")
strSql = strSql & " where contact_id = " & Me!ContactID
CurrentDb.Execute strSql, dbFailOnError


Some additional notes:

I use a function called GetMySql...this is pluck out the sql text from the
query builder...as I don't want to hand write the sql..

The function GetMySql is a global function,a nd this would be placed in a
standard code module...not in the forms module.

Here is the Code:

Public Function GetMySql(strQuery As String) as string


GetMySql = CurrentDb.QueryDefs(strQuery).SQL
GetMySql = Left(GetMySql, InStr(GetMySql, ";") - 1)



End Function


Further, when you use the query builder to build the append query, you
simply fire up the query builder....and then simply double click on each
field in the field list to place that field into eh query grid. REMEMBER TO
SKIP THE FIRST PRIMARY KEY ID!!!!!

The above process can be done VERY quickly...simply rapidly double click on
all of the fields..ad then when done simply change the query to a append
query....choose the same table from the drop down list...and viola!! all of
the fields names will be copied for you!!.

the above is done for the first main table append...

for each additional "child" table query, do exactly as above...but ONE
ADDITIONAL STEP!!!

After you used query->append query from the menu, you THEN MUST modify the
FK (field column used to relate to the main table) first column to use our
NEW primary key id of the main record.:

You can use in the query builder

MyPKid:MyNewID()

So, replace the first column with the above expression

We also need to define a global variable and function called:

Public gbllngNewID As Long

And, we need a fucntion to return this value..since a query can't use
variable names..but they *can* use fucntions.

Public Function MyNewID() As Long

MyNewID = gbllngNewID

End Function

So, the above is all you need. use the query builder to build those 8 child
tables...and then you simply cut and paste the follwing code 8 times.


strSql = GetMySql("qryAChild1")
strSql = strSql & " where contact_id = " & Me!ContactID
CurrentDb.Execute strSql, dbFailOnError

strSql = GetMySql("qryAChild2")
strSql = strSql & " where contact_id = " & Me!ContactID
CurrentDb.Execute strSql, dbFailOnError

etc. all the way to qryAChild8 (assuming that this is the neames you give
each append query)

And, in fact, if your FK field name in the child tables is always the same,
then we could simply go

for i = 1 to 8

strQuery = "qryAChild" & i
strSql = GetMySql(strQuery)
strSql = strSql & " where contact_id = " & Me!ContactID
CurrentDb.Execute strSql, dbFailOnError

next i

so, it not a lot code here if you do this right..and the "field names" don't
have to be typed if you use the query builder.....

also, ignore Perry's comments about using cascade updates...you ONLY want to
do that if you are MOVING the records., but in your case you are COPYING the
records...... (cascade updates are useful if you are moving child records,
but not for copy them).

The above code example is really all the code you need here....
 
Albert, This worked perfectly. Thank You

Albert D. Kallal said:
The process is:

1) copy the main record to a new record via a append query
2) obtain the new "id"

3)....to number of child tables
run a append query on sub table 1 that selects all id in this child table,a
and updates the
parent_id (foreign key value) to the "new" parent id (this "switch" of the
id" to
use can be done DURING the append query).

Repeat 3) above for all 8 child tables....

This is one case where I would actually recommend the use
of a global function to return a ID value. The reason why I
suggest doing this is then you can use the query builder
to build all 8 queries, and include the new "id" as a function
expression in that query. This will save a TON of code.

So, you need to write/build 8 append querys.....

The code would look like:

Dim strSql As String

' main table copy..
strSql = GetMySql("qryAMain")
strSql = strSql & " where Contacts.contactID = " & Me!ContactID
CurrentDb.Execute strSql, dbFailOnError

' get the new id
strSql = "select @@IDENTITY from Contacts"
gbllngNewID = CurrentDb.OpenRecordset(strSql)(0)

'NOW, run each of the append quiers...

' child table 1
strSql = GetMySql("qryAChild1")
strSql = strSql & " where contact_id = " & Me!ContactID
CurrentDb.Execute strSql, dbFailOnError


Some additional notes:

I use a function called GetMySql...this is pluck out the sql text from the
query builder...as I don't want to hand write the sql..

The function GetMySql is a global function,a nd this would be placed in a
standard code module...not in the forms module.

Here is the Code:

Public Function GetMySql(strQuery As String) as string


GetMySql = CurrentDb.QueryDefs(strQuery).SQL
GetMySql = Left(GetMySql, InStr(GetMySql, ";") - 1)



End Function


Further, when you use the query builder to build the append query, you
simply fire up the query builder....and then simply double click on each
field in the field list to place that field into eh query grid. REMEMBER TO
SKIP THE FIRST PRIMARY KEY ID!!!!!

The above process can be done VERY quickly...simply rapidly double click on
all of the fields..ad then when done simply change the query to a append
query....choose the same table from the drop down list...and viola!! all of
the fields names will be copied for you!!.

the above is done for the first main table append...

for each additional "child" table query, do exactly as above...but ONE
ADDITIONAL STEP!!!

After you used query->append query from the menu, you THEN MUST modify the
FK (field column used to relate to the main table) first column to use our
NEW primary key id of the main record.:

You can use in the query builder

MyPKid:MyNewID()

So, replace the first column with the above expression

We also need to define a global variable and function called:

Public gbllngNewID As Long

And, we need a fucntion to return this value..since a query can't use
variable names..but they *can* use fucntions.

Public Function MyNewID() As Long

MyNewID = gbllngNewID

End Function

So, the above is all you need. use the query builder to build those 8 child
tables...and then you simply cut and paste the follwing code 8 times.


strSql = GetMySql("qryAChild1")
strSql = strSql & " where contact_id = " & Me!ContactID
CurrentDb.Execute strSql, dbFailOnError

strSql = GetMySql("qryAChild2")
strSql = strSql & " where contact_id = " & Me!ContactID
CurrentDb.Execute strSql, dbFailOnError

etc. all the way to qryAChild8 (assuming that this is the neames you give
each append query)

And, in fact, if your FK field name in the child tables is always the same,
then we could simply go

for i = 1 to 8

strQuery = "qryAChild" & i
strSql = GetMySql(strQuery)
strSql = strSql & " where contact_id = " & Me!ContactID
CurrentDb.Execute strSql, dbFailOnError

next i

so, it not a lot code here if you do this right..and the "field names" don't
have to be typed if you use the query builder.....

also, ignore Perry's comments about using cascade updates...you ONLY want to
do that if you are MOVING the records., but in your case you are COPYING the
records...... (cascade updates are useful if you are moving child records,
but not for copy them).

The above code example is really all the code you need here....
Click to expand...
 
Back
Top