Occasionally, my users need to create a new record in the database using the
values of an existing record (in the same databvaase) as the source. The
primary key to the table is an "autonumber" field. Ultimately, I'd like to
be able to have the user specif y the source record, have the system create a
new record using some (not all) field values of the source record; open the
"edit" form with the new record as the source.

Thanks in advance for any suggestions.....

dfeigen115 wrote:

>Occasionally, my users need to create a new record in the database using the
>values of an existing record (in the same databvaase) as the source. The
>primary key to the table is an "autonumber" field. Ultimately, I'd like to
>be able to have the user specif y the source record, have the system create a
>new record using some (not all) field values of the source record; open the
>"edit" form with the new record as the source.


Here's one way to copy (some of) the current record's values
to a new record and then navigate to the new record:

Sub CopyButton_Click()
With Me.RecordsetClone
.AddNew
!flda = Me.flda
!fldc = Me.fldc
. . .
.Update
Me.Bookmark = .LastModified
End With

Note that this saves the copy back to the source table, so
if the user decides they didn't want to do that, you'll have
to provide a delete button (i.e Esc or Undo will not cacel
the new record).

--
Marsh
MVP [MS Access]

Thanks Marshall, I'll give it a shot.

"Marshall Barton" wrote:

> dfeigen115 wrote:
>
> >Occasionally, my users need to create a new record in the database using the
> >values of an existing record (in the same databvaase) as the source. The
> >primary key to the table is an "autonumber" field. Ultimately, I'd like to
> >be able to have the user specif y the source record, have the system create a
> >new record using some (not all) field values of the source record; open the
> >"edit" form with the new record as the source.
>
>
> Here's one way to copy (some of) the current record's values
> to a new record and then navigate to the new record:
>
> Sub CopyButton_Click()
> With Me.RecordsetClone
> .AddNew
> !flda = Me.flda
> !fldc = Me.fldc
> . . .
> .Update
> Me.Bookmark = .LastModified
> End With
>
> Note that this saves the copy back to the source table, so
> if the user decides they didn't want to do that, you'll have
> to provide a delete button (i.e Esc or Undo will not cacel
> the new record).
>
> --
> Marsh
> MVP [MS Access]
>

Marshall,

The "copy" is working fine, thank you.

I do have a followup question... How can I access/capture the value of the
primary key (autonumber) that was created when the clone was saved? The idea
being once the new record is saved, open an edit form populated with data
from the new record so that the users can tweak anything as needed.

Dan

"dfeigen115" wrote:

> Thanks Marshall, I'll give it a shot.
>
> "Marshall Barton" wrote:
>
> > dfeigen115 wrote:
> >
> > >Occasionally, my users need to create a new record in the database using the
> > >values of an existing record (in the same databvaase) as the source. The
> > >primary key to the table is an "autonumber" field. Ultimately, I'd like to
> > >be able to have the user specif y the source record, have the system create a
> > >new record using some (not all) field values of the source record; open the
> > >"edit" form with the new record as the source.
> >
> >
> > Here's one way to copy (some of) the current record's values
> > to a new record and then navigate to the new record:
> >
> > Sub CopyButton_Click()
> > With Me.RecordsetClone
> > .AddNew
> > !flda = Me.flda
> > !fldc = Me.fldc
> > . . .
> > .Update
> > Me.Bookmark = .LastModified
> > End With
> >
> > Note that this saves the copy back to the source table, so
> > if the user decides they didn't want to do that, you'll have
> > to provide a delete button (i.e Esc or Undo will not cacel
> > the new record).
> >
> > --
> > Marsh
> > MVP [MS Access]
> >

Add a line before the .Update
lngPK = !pkfieldname

Can't users edit the new record in the same form where we
copied the record?
--
Marsh
MVP [MS Access]


dfeigen115 wrote:
>The "copy" is working fine, thank you.
>
>I do have a followup question... How can I access/capture the value of the
>primary key (autonumber) that was created when the clone was saved? The idea
>being once the new record is saved, open an edit form populated with data
>from the new record so that the users can tweak anything as needed.
>
>>
>> "Marshall Barton" wrote:
>>
>> > dfeigen115 wrote:
>> >
>> > >Occasionally, my users need to create a new record in the database using the
>> > >values of an existing record (in the same databvaase) as the source. The
>> > >primary key to the table is an "autonumber" field. Ultimately, I'd like to
>> > >be able to have the user specif y the source record, have the system create a
>> > >new record using some (not all) field values of the source record; open the
>> > >"edit" form with the new record as the source.
>> >
>> >
>> > Here's one way to copy (some of) the current record's values
>> > to a new record and then navigate to the new record:
>> >
>> > Sub CopyButton_Click()
>> > With Me.RecordsetClone
>> > .AddNew
>> > !flda = Me.flda
>> > !fldc = Me.fldc
>> > . . .
>> > .Update
>> > Me.Bookmark = .LastModified
>> > End With
>> >
>> > Note that this saves the copy back to the source table, so
>> > if the user decides they didn't want to do that, you'll have
>> > to provide a delete button (i.e Esc or Undo will not cacel
>> > the new record).