I'm pretty sure I can do this with a recordset, but I'm not sure how.

I've got a form that's tied to a table (with a subform that's tied to a
child table). I've set up a button that pops up a modal window that asks for
some text, and when the control returns to the form, I want to

1. Save the current record;
2. Append the text entered onto the current key field;
3. Go to a new record with the new key, and all other field values the same.

So, for example, the current key is "abcd". The dialog opens and the user
enters "x1". I want to save all the current field values for record "abcd,"
then start a new record with key "abcd.x1" and all field values the same as
the field values that were just saved.

I tried just setting the key field to the new value, and that appears to
work for the fields on the main form, but all of the field values on the
subform are wiped out.

Any suggestions would be greatly appreciated.

Thanks,
Rich

To duplicate selected fields from the current record in the form, AddNew to
the form's RecordsetClone.

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

The article uses that approach to duplicate the main form's record. You can
ignore the bit about duplicating the subform record as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rmiller1985" <rmiller1985atearthlinkdotnet> wrote in message
news:4B767B6C-4423-43B0-9D6F-(E-Mail Removed)...
> I'm pretty sure I can do this with a recordset, but I'm not sure how.
>
> I've got a form that's tied to a table (with a subform that's tied to a
> child table). I've set up a button that pops up a modal window that asks
> for
> some text, and when the control returns to the form, I want to
>
> 1. Save the current record;
> 2. Append the text entered onto the current key field;
> 3. Go to a new record with the new key, and all other field values the
> same.
>
> So, for example, the current key is "abcd". The dialog opens and the user
> enters "x1". I want to save all the current field values for record
> "abcd,"
> then start a new record with key "abcd.x1" and all field values the same
> as
> the field values that were just saved.
>
> I tried just setting the key field to the new value, and that appears to
> work for the fields on the main form, but all of the field values on the
> subform are wiped out.
>
> Any suggestions would be greatly appreciated.
>
> Thanks,
> Rich

Thanks, Allen!

A couple of questions:

1. The form that I'm using doesn't have fields corresponding to all of the
fields in the underlying table (there are additional forms that deal with the
other fields). So if I use the technique you've outlined, how can I
duplicate all of the table's fields? I might be misunderstanding the
"!CustomerID = Me.CustomerID", but doesn't that "Me" refer to a value from
the form?
2. I should have mentioned in my original post that this is an Access 2000
application (I'm trying to get my client to upgrade!). I know the
recordsetclone object exists, but are all of the methods in this technique
available?

Thanks again,
Rich


"Allen Browne" wrote:

> To duplicate selected fields from the current record in the form, AddNew to
> the form's RecordsetClone.

It all works in A2000.

If the RecordSource of the form is the table, then all fields are available,
even if they are not represented by a control on the form. If the form's
RecordSource is a query, can you add the other fields to the query so they
are available?

If you cannot do that, it would be possible to OpenRecordset directly on the
table, and AddNew to that. You would then need to Requery the form (so it
discovers the new record), and then FindFirst in the form's RecordsetClone
so that it displays the newly added record. This is considerably less
efficient.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rmiller1985" <rmiller1985atearthlinkdotnet> wrote in message
news:A393B403-0FDE-4B25-816A-(E-Mail Removed)...
> Thanks, Allen!
>
> A couple of questions:
>
> 1. The form that I'm using doesn't have fields corresponding to all of
> the
> fields in the underlying table (there are additional forms that deal with
> the
> other fields). So if I use the technique you've outlined, how can I
> duplicate all of the table's fields? I might be misunderstanding the
> "!CustomerID = Me.CustomerID", but doesn't that "Me" refer to a value from
> the form?
> 2. I should have mentioned in my original post that this is an Access
> 2000
> application (I'm trying to get my client to upgrade!). I know the
> recordsetclone object exists, but are all of the methods in this technique
> available?
>
> Thanks again,
> Rich
>
>
> "Allen Browne" wrote:
>
>> To duplicate selected fields from the current record in the form, AddNew
>> to
>> the form's RecordsetClone.