By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,290 Members | 1,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,290 IT Pros & Developers. It's quick & easy.

Copy a Record in VBA

100+
P: 675
I want to copy ALL fields of a record (except Key) to another record.

Expand|Select|Wrap|Line Numbers
  1. Dim rstSource As DAO.Recordset
  2. Dim rstTarget As DAO.Recordset
  3. Dim fld As Field
  4. Dim iiSourceKey As Long
  5. Dim iiTargetKey As Long
  6.     Set rstSource = RecordsetClone
  7.     rstSource.FindFirst "Key=" & iiSourceKey
  8.     Set rstTarget = RecordsetClone
  9.     rstSource.FindFirst "Key=" & iiTargetKey
  10.     rstTarget.Edit
  11.     For Each fld In rstSource.Fields
  12.         If fld.Name <> strKeyFieldName Then
  13.             Debug.Print fld.Name, fld
  14.             rstTarget.Fields(fld.Name) = rstSource.Fields(fld.Name)
  15.         End If
  16.     Next
  17.     rstTarget.Update
Assuming iiSourceKey & iiTargetKey are valid, this doesn't work. The two keys are different during the Set statements, but the records are the same after the second Set statement. I think (but don't know) that only one record in RecordsetClone can be referenced/active at any time.

So how do I do this? Or is there another approach that is better? Like SQL?
Jul 28 '09 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,287
I noticed that in line 9, you use rstSource.FindFirst, and therefore the rstTarget remains at the first record?
Jul 28 '09 #2

100+
P: 675
Sorry ChipR, but from MS Help
... that satisfies the specified criteria and makes that record the current record
from MS Help
Line 7 would set to the key iiSourceKey, and line 9 to iiTargetKey. FindFirst finds the first occurrance, and is not the same as MoveFirst.
At line 14, both rstSource and rstTarget are on the same record, and the values are the same according to the
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fld.Name, rstTarget.Fields(fld.Name), rstSource.Fields(fld.Name)
So I am assuming that RecordsetClone can have only one current position, and that rstSource and rstTarget are pointers not to unique records, but to the current record in RecordsetClone. They seem to not be recordsets, but pointers.
Jul 28 '09 #3

Expert 100+
P: 1,287
If you print the contents of the rstTarget record between lines 8 & 9, or 9 & 10, you can verify whether it points to the first record as expected, or whether it is on some other record.
Jul 28 '09 #4

100+
P: 675
Yes it does. Stepping thru the code says it is doing as I expect now. Anytime after line 9 shows the 2 recordsets the same, and at the second set.
Setting them in the reverse order(Target first) has exactly the same issues.
Jul 28 '09 #5

Expert 100+
P: 1,287
In that case, it may be easier to use SQL. I think this would work, but I've never tried it.
Expand|Select|Wrap|Line Numbers
  1. set rst = RecordSetClone
  2. rst.FindFirst "Key = " & iiSourceKey
  3. strSQL = "UPDATE myTableName SET " _
  4.   & " Field1 = " & rst!Field1 & _
  5.  & ", Field2 = " & rst!Field2 & _ 
  6.  ...
  7.  & " WHERE Key = " & iiTargetKey
Jul 28 '09 #6

100+
P: 675
I was trying to write a generic "Record Copy". This may not be easy.
One purpose is to do a "Save As" on the current (now being modified) record of a continuous form. I haven't been able to do it from the form, but can now do it if I pass my procedure a table and not a recordset and the form and the current key.
This doesn't quite work as smoothly as I want, however. Doing a recordset.addnew creates a key even if the data is never saved with recordset.update. Also, my new data remains in the old record, and the old data in the new record. If the user never sees the key field (autonum), this really isn't too bad.
Expand|Select|Wrap|Line Numbers
  1. Public Sub SaveRecordAs(iiCopyFromKey As Long, strKeyFieldName As String, _
  2.         strTableName As String, frmCurForm As Form)
  3. 'strKeyFieldName = Name of AutoNum Key Field so Excluded in Copy
  4. 'strTableName = Name of Table which is source of records for form
  5. 'iiKeyFrom = Number of Key to Copy
  6. Dim dbs As Database
  7. Dim rstSource As DAO.Recordset
  8. Dim rstTarget As DAO.Recordset
  9. Dim fld As Field
  10.  
  11.     Set dbs = CurrentDb
  12.     'Set Source
  13.     Set rstSource = dbs.OpenRecordset("SELECT * FROM " & strTableName)    rstSource.FindFirst "Key=" & iiCopyFromKey
  14.     'Set Target
  15.     Set rstTarget = dbs.OpenRecordset("SELECT * FROM " & strTableName)
  16.     rstTarget.AddNew
  17.     'Copy fields - this copies values in table, not in form's textboxes
  18.     '   Therefore, the New Record is identical to the old.  When form
  19.     '   is saved, SaveAs record is the original record
  20.     For Each fld In rstSource.Fields
  21.         If fld.Name <> strKeyFieldName Then
  22.             Debug.Print fld.Name, fld
  23.             rstTarget.Fields(fld.Name) = rstSource.Fields(fld.Name)
  24.         End If
  25.     Next
  26.     rstTarget.Update
  27.  
  28.     frmCurForm.Requery
  29.     Call PositionAt("Key=" & iiCopyFromKey, frmCurForm)
  30.  
  31.     Set rstSource = Nothing
  32.     Set rstTarget = Nothing
  33.     Set dbs = Nothing
  34. End Sub 'SaveRecordAs
Jul 28 '09 #7

100+
P: 675
ChipR - Your SQL solution also requires I loop thru the fields to generate the SQL. This is fine, but my solution, posted above, I think gives me more control. I have the Keys, and can then reestablish the correct record after the copy and a requery. Also, with the Keys, I have more control over some of the comboboxes on the form, driven by these same tables.
Thank you for your time and efforts.
Jul 28 '09 #8

P: 4
Dear OldBirdman
the PositionAt Sub is missing.
Please plibish it.
Thank you

@OldBirdman
Dec 14 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.