replace INSERT INTO with a copy and paste

[gwilym40]

Hi

I have a section plan form (with alot of fields on - 50 at least)
There is an option to create a new record, wherby a different form is opened, a new record cerated and the relevant details are copied across (using references) to the new record. The problem is, I could end up with 50 lines of code that need referencing (considering the potential that 50 fields will be populated). Is there a way I can say, if the user chooses to copy the record, that the data is copied and pasted into the new form in one go?

Thanks
Gwilym

 

[ToeShot]

Put this function in a module and call it after an OnClick event of a button or however you want to do it.
in your references make sure you have the Microsoft DAO Library 3.X check and placed above the Microsoft Active Data Objects 2.x


Function CopyRec()
Dim rstcp As DAO.Recordset
Dim rstnew As DAO.Recordset
Dim fieldnum As Integer

'---------------------------------> Change the table names to your tables
Set rstcp = CurrentDb.OpenRecordset("Arch1", dbOpenSnapshot)
Set rstnew = CurrentDb.OpenRecordset("Arch1", dbOpenDynaset)

rstcp.MoveLast
rstnew.AddNew

For fieldnum = 0 To (rstcp.Fields.Count - 1)
rstnew.Fields(fieldnum).Value = rstcp.Fields(fieldnum).Value
Next fieldnum

rstnew.Update
rstcp.Close
rstnew.Close

End Function Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait