duplicating a record

[jnorth]

I'm trying to duplicate a single record from a form. The form is using data from 3 tables. What is the best way to duplicate the record? The built in command button for duplicating records on a form does not work, probably because the form is too complex. What I really need to do is just duplicate the data from one of the related tables that the rest of the form data is grouping on. Should I use the me object and copy the fields to a new record. Thanks in advance for the help

 

[gwinn7]

Look into the "paste append" option in the Help. Here is an example of one...

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

This duplicates the record currently displayed on the form.
I had to do this to help me simplify entering in multiple equipment items with the same make/model/details. I know, there should be a separate table for this, but that is the next step. Anyhow, I think the above code is what you are looking for.

Gary
gwinn7

 

[jnorth]

Thanks for the reply. This is the default code that Access provides when you set up a duplicate command button using the command button wizard. Unfortunately, it's not working in this situation. When I execute this code from a command button on the form, the (I think it's called the staus bar) bar at the bottom of the form window says "calculating..." but after a short pause the message disapears and no records are added.

I seems as though the information from the form is being copied to the clipboard, but when I select a new record and try to paste this information, I get an erro that says my fied is too small for the info on the clipboard??

In Access' defense, this method has worked for me on other computers using the same and different versions of Access(I currently use XP)and the exact same project. However, across several versions and after several tries, this copy, paste append still does not work with this project's form on my work PC.(Complete mystery to me.)

 

[gwinn7]

Well, in that case, what about running an Append query using the current record identifier as the reference?

Gary
gwinn7

 

[jnorth]

That sounds like a good idea. I'm not at all familiar with running a query from VBA. I understand how to write a paramaterized append query from Access. Would you mind suggesting they way to go about this so that I could by pass the whole parameter dialog input for the record identifier?

 

[gwinn7]

Sure, but I wouldn't use a QueryDef object. Instead, just do a runSQL like the following ...

Public Function DuplicateRecord(ID as Long) as Boolean

Docmd.SetWarnings False
Docmd.RunSQL "INSERT INTO MyTable ( Field1, Field2, Field3, Field4 ) SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3, MyTable.Field4 FROM MyTable WHERE (((MyTable.Field5)= " & ID & &quot);"
Docmd.SetWarnings True


End Function

Gary
gwinn7