Form Fields copy

[SjCc]

Hi

I am having a command button named "Start" now what i want that when i click this button then it to copy all fields data in that particular form and to be pasted in same form as new record.

Suppose form name is "Lounge"

 

[Dreamweaver]

You could use an append query to copy the current record to a new one, not something I have done as breaks the rules the only time i copied from one record to another is for things like rebookings and even then it was only the ID's copied across.

 

[SjCc]

You could use an append query to copy the current record to a new one, not something I have done as breaks the rules the only time i copied from one record to another is for things like rebookings and even then it was only the ID's copied across.

good idea but still waiting for for some more advises if any..

 

[Dreamweaver]

Dim m_Db As Database
Dim RMD As DAO.Recordset
Dim NewID As Long
Dim Emp As DAO.Recordset


Set Emp = m_Db.OpenRecordset("SELECT * FROM YOUR TABLE", dbOpenDynaset)
With Emp
.AddNew
!Field1= Me![Field1]
!Field2= Me![Field2]
!Field3= Me![Field3]
'And so on or all your fields
NewID = !FieldID
.Update
End With


Use the NewID to go to the new record

 

[Gasman]

Don't we need a WHERE clause in there

WHERE FieldID = Me.FieldID

in fact I'd go so far as creating a query that produced no records at all, if using that method.?

 

[Dreamweaver]

Don't we need a WHERE clause in there

WHERE FieldID = Me.FieldID

in fact I'd go so far as creating a query that produced no records at all, if using that method.?

No As he is copying the active form record he only needs the recordset to be able to add a new record

 

[isladogs]

I would never recommend copying all the data from one record into another record.

However if you are going to 'break all the rules' as MickJav wrote, then it doesn't matter whether you use an append query (or SQL equivalent in VBA) or a recordset to do so.
Your recordset code has to do two things - add a new record then populate it with the required data.
Both will work BUT using an append query is a better solution as it needs far less code & will be faster (though both will be fast for one record)

I also agree with Gasman about the need to use A WHERE clause to select the required record for copying (by either method)

 

[Gasman]

No As he is copying the active form record he only needs the recordset to be able to add a new record

So that will not select all records in the table?, which is what I *thought* it would do?

 

[Dreamweaver]

See it now the where will be faster as limits the recordset I'll have to see if I can find the project I did that copy ID thing in and see what i did there thanks

 

[Gasman]

TBH I was thinking of 'WHERE 1 = 2' (anything that produces no records) on my second thought, just the recordset structure.?

 

[missinglinq]

Why not simply

   DoCmd.RunCommand acCmdSaveRecord 'In case it's a new, unsaved Record
   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdCopy
   DoCmd.GoToRecord , , acNewRec
   DoCmd.RunCommand acCmdPasteAppend

Linq ;0)>