create new row/record using existing row as source dfeigen115 14.09.2006 21:16:01 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.....

Re: create new row/record using existing row as source Marshall Barton <marshbarton[ at ]wowway.com> 15.09.2006 05:14:25 dfeigen115 wrote: [Quoted Text] >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]

Re: create new row/record using existing row as source dfeigen115 15.09.2006 12:53:02 Thanks Marshall, I'll give it a shot. "Marshall Barton" wrote: [Quoted Text] > 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] >

Re: create new row/record using existing row as source dfeigen115 15.09.2006 17:31:02 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: [Quoted Text] > 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] > >

Re: create new row/record using existing row as source Marshall Barton <marshbarton[ at ]wowway.com> 15.09.2006 19:51:23 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: [Quoted Text] >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).