Copy record and child records, but not all fields

[Bob 4]

I have a charter tour bus program in Access 2000 used by 10 people daily
since 2000. To reduce data entery and make life easier for the tour planners
we want to copy prior successful tours to new tours. This includes records
in child tables like pick up points, itineraries, etc. (many for each tour).

However, we don't want to copy all fields in the tours or in the child
tables. For example we do want to copy the name, number of days, etc. but we
don't want to copy the leave date.

After making the copy I want to move to the new record so the tour planner
can enter new data such as the leave date.

I'm comfortable with VBA and recordset operations. I've searched the
discussion group but haven't found a solution.

Can someone point me to an example or recommend a sequence of steps?

Thank you,

bob 4

 

[Allen Browne]

Here's an example:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

 

[Bob 4]

Thank you, Allen. I've read many of your posts over the years. I read and
understand the code on your site for duplicating records and related records
in subforms. However, I do not want to duplicate all the fields in the
original record or the child records, and I have child tables that are not
shown in active subforms when I make the copy. Can you point me to some code
or give me some general guidance that satifies these requirements?

Thank you for your time.

Bob 4

 

[Allen Browne]

The code in the example adds the primary record to the main form's
RecordsetClone, so you can get the primary key value for the next record. It
then creates the records in the related tables by executing a SQL statement.
You will need to execute a series of SQL statements (append queries) to add
the desired fields to the related tables.

Note that in the example:
a) the fields you do want to assign are listed in the INSERT clause
b) the values to put in those fields are listed in the SELECT clause
c) the Execute actually puts them in the table, i.e. it does not actually
use the subform.

In the same way, you can specify which fields you want to put values into,
and what values to assign to those fields, and the fact that there's no
subform for some of your related tables is no barrier.

In the end, you will create a series of SQL strings to execute -- one for
each of your related tables that you wish to add records to.

 

[Bob 4]

Thank you. I'll implement your approach.

Bob 4

The code in the example adds the primary record to the main form's
RecordsetClone, so you can get the primary key value for the next record. It
then creates the records in the related tables by executing a SQL statement.
You will need to execute a series of SQL statements (append queries) to add
the desired fields to the related tables.

Note that in the example:
a) the fields you do want to assign are listed in the INSERT clause
b) the values to put in those fields are listed in the SELECT clause
c) the Execute actually puts them in the table, i.e. it does not actually
use the subform.

In the same way, you can specify which fields you want to put values into,
and what values to assign to those fields, and the fact that there's no
subform for some of your related tables is no barrier.

In the end, you will create a series of SQL strings to execute -- one for
each of your related tables that you wish to add records to.

 

[Bob 4]

Allen, I'm revisiting this code and am implementing it now. I've followed
the example you provided and understand how it works. However the AddNew
method in the code below is moving me to a new record immediately. Any
pointers?

Thanks again. Bob 4

If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Exit Sub
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew '__problem is here__
!TourName = Me.TourName
!NumDays = Me.NumDays
!LeaveDate = Me.LeaveDate
!ReturnDate = Me.ReturnDate
!TourType = Me.TourType
!CostPerMile = Me.CostPerMile
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngNewTourID = Me!TourID
End With
End If