Copy values from one record into a new record (1 Viewer)

Andy_CD · Jun 9, 2017

Hi,
what I would like to do is open a form based on an old record selected from a drop down list. When the new form opens it creates a new record but with the old values. I want it so that the user can then edit these values. When the record is saved it keeps the old record and creates a new one with the edited values.
Is this possible????

Orthodox Dave · Jun 9, 2017

Yes it is possible.

On your "old form" have a Combobox containing the dropdown list of the old records (bound to the ID fields of those records).

in the AfterUpdate event for the combobox, you open the new form:

Code:

DoCmd.OpenForm "[new form name]", , , , acFormAdd

Then you populate the fields:

Code:

Forms![new form name]![Field1] = DLookUp("[Field1]","[table name]","[table name].[ID] = Forms![old form name]![combobox name])"

and do the same for all the fields. Finally save the record:

Code:

DoCmd.RunCommand acCmdSaveRecord

This assumes your ID is an autonumber, so the record will be unique even if the remaining fields are duplicates.

Minty · Jun 9, 2017

I would do this with an insert query based on the existing ID, DLookups are a very inefficient method.

You can build it in the query designer and make the current ID on the existing form the criteria.

Orthodox Dave · Jun 9, 2017

Minty is right. Mine would work. His would go faster.

Andy_CD · Jun 9, 2017

Thanks both, Minty could you give me a little more detail how to do this please?

Copy values from one record into a new record (1 Viewer)

Andy_CD

Bumbling Idiot

Orthodox Dave

Home Developer

Minty

AWF VIP

Orthodox Dave

Home Developer

Andy_CD

Bumbling Idiot

Similar threads

Users who are viewing this thread