Creating a new record from selected fields of existing record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

What I need to do is to create a new record in the same database from an existing record - and I want to specify which fields' contents are copied across. There is a command I can add in the form which duplicates the record to a new record - this generates a new record incrementing my unique numbering, but it copies the contents of all the fields - I want to create a new record that take the content of most of the fields but not all of them - and ideally I would like to do this within the form view. Any ideas please?

Thank you

Su
 
Hi Sue,

This code will 'clone' the current record - attach it to whichever event
makes sense for you. Notice the if clause which skips the CustomerId
field - you can do the same for any other fields you wish to skip.

Dim rst As Dao.Recordset
Dim fld As Dao.Field
Dim lngCustid As Long
Set rst = Me.RecordsetClone.Clone
Me.RecordsetClone.Bookmark = Me.Bookmark
rst.Bookmark = Me.Bookmark
With Me.RecordsetClone
rst.AddNew
For Each fld In .Fields
'skip the primary key field -
'chg CustomerID to the name of your primary key field
If fld.Name <> "Customerid" Then
rst.Fields(fld.Name).Value = fld.Value
End If
Next fld
' If your Primary key (Customerid) is autonum you don't need this
' otherwise, replace this with whatever method you use for getting
' the next primary key value. This one is a simple incrementing value
'
lngCustid = Nz(DMax("Customerid", "Customers"), 0) + 1
rst.Fields("Customerid") = lngCustid
' Update the recordset with the new value
rst.Update
rst.Bookmark = rst.LastModified
' set the form's book mark to the new record
Me.Bookmark = rst.Bookmark
End With
Set rst = Nothing
Set fld = Nothing
 
Thanks Sandra. I am attempting to attach this as event procedure to a command button on the form for "on click" - but the error returned is "User-defined type not defined", highlighting the line: Dim rst As Dao.Recordset
 
Hi Sue,

Check your references, you must be missing the reference to the DAO Library.
Open the module in the Visual Basic Editor (VBE) then click
Tools->References. Make sure that the Microsoft DAO 3.x Object Library is
checked. You might also want to check this article details and info on how
to fix this particular problem:

http://www.mvps.org/access/bugs/bugs0031.htm
 
What fields are in your unique index? You have to alter the value in at
least one of them - for example tack on the word "Copy" to one of the
fields.
 
Back
Top