create new row/record using existing row as source

[Guest]

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

 

[Marshall Barton]

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

 

[Guest]

Thanks Marshall, I'll give it a shot.

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

 

[Guest]

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

 

[Marshall Barton]

Add a line before the .Update
lngPK = !pkfieldname

Can't users edit the new record in the same form where we
copied the record?