| | |
|
|
|
Creating a new record from selected fields of existing record |
|---|
| message from =?Utf-8?B?U3Vl?= on 6 May 2004 |
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,
Sue
|
| Sandra Daigle replied to =?Utf-8?B?U3Vl?= on 6 May 2004 |
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
|
| =?Utf-8?B?U3Vl?= replied to Sandra Daigle on 6 May 2004 |
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
|
| Sandra Daigle replied to =?Utf-8?B?U3Vl?= on 6 May 2004 |
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
|
| =?Utf-8?B?U3Vl?= replied to Sandra Daigle on 6 May 2004 |
Hi Sandra,
If fld.Name <> "Reference Number" Then
rst.Fields(fld.Name).Value = fld.Value
End If
Thanks for your help,
Sue
|
| Sandra Daigle replied to =?Utf-8?B?U3Vl?= on 6 May 2004 |
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.
|
|
Archived message: Creating a new record from selected fields of existing record (Microsoft Access Error Message)
