Creating a new record from selected fields of existing record

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
 
S

Sandra Daigle

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
 
G

Guest

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
 
S

Sandra Daigle

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
 
S

Sandra Daigle

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top