Search for record and save as a new record

[Cyberjake]

I have an Access 2000 DB that allows the user to search on a particular field. After the user finds the record they searched for, I want the user to be able to change the entry in that field and save it as a new record (and still retain the original record) using the data from the entry they originally searched for. I am at a loss on how to proceed with this. Any suggestions would be greatly appreciated.

Thanks,

Cyberjake

 

[GingerR]

If you put a button on a form, and using the wizard choose to Duplicate Record, then look at the code in the OnClick event of the button, you can see some code how to do it. Or maybe that button is all you need. See if that helps.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cyberjake]

Thanks for that info, I will try that and see if it will work for me.

Thanks,

Cyberjake

 

[Cyberjake]

This has not worked for me. It keeps telling me that it would cause a duplicate entry. I believe what is happening is when I change the field entry, Access auto-saves that change to the current record which causes a duplicate entry error. Are there any more suggestions at this point?

Thanks,

Cyberjake

 

[GingerR]

First, how come you want to duplicate the record? Obviously you have a primary key in the table, that would cause the duplication. But what's the use of having a primary key if you're going to have a duplicate record?

A coupla options:
1) write an APPEND query, referencing the ID of the current record on the form. Launch the query in the OnClick event of a button or something like that (...docmd.openquery "blah"). Just don't put the Key field in the Append query.

2) Try using some code. Don't know if you're familiar with DAO, but here it goes. You'd put this in an event, such as in the ONClick event of a button. You also have to make sure a reference to DAO is made (open any code module, from the menu pick TOOLS+REFERENCES, scroll down til you find Microsoft DAO... and pick the highest version). Sub your table name for [YourTableName], your table's field names for field1,2,3 and the form control names for FormField1,2,3. add more as necessary:

'Opens the table and adds a new record with the same data as is on the current form

Dim rs, rsTemp as dao.recordset
set rs = currentdb.openrecordset("Select * from [YourTableName]")

rs.addnew
rs!Field1 = me.Formfield1
rs!Field2 = me.Formfield2
rs!Field3 = me.Formfield3
.
.
.
rs.update

msgbox "Done!",vbokonly
set rs = nothing

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244