Here’s a good method for copying values from one record to another, including any linked records.
Access Archon #133
Introduction
In Access Archon #51, I described how to copy values from one record to another, using hard-coded control names. That method does the job, but it requires a lot of editing to make it work for a new form. And it doesn’t deal with any linked records, such as book authors. This article describes a more sophisticated (and flexible) method for copying values from one record to another, including any linked records.
Sample Form
The sample database, Copying Records and Linked Records, has a main form, fpriEBookNotes, that display information about ebooks and their authors. Since a book can have multiple authors, and an author can write multiple books, there is a many-to-many relationship between tblEBooks and tblAuthors, using a linking table (tblEBookAuthors). A subform on frpiEBookNotes displays the authors for each book; it uses the linking table as its record source.
If you want to copy most (but not all) of the data from a book record to a new record, you need a way to mark the controls whose values should be copied. I use the handy Tag property of Access controls for this purpose; entering “No Copy” into this property makes the CopyRecord code skip this control. To copy the author information, the code calls a separate procedure that creates a new record in tblEBookAuthors for each author of the source book, with the AuthorID from the source book and the BookID from the new book.
Figure A shows a book record with two authors; Figure B shows the copy of the record, with data copied from the linked records in the Authors subform, and the cboCategory, cboSource, chkSeries, and cboSeriesName controls (all the other controls have “No Copy” in their Tag properties).
Figure A. A book record with two authors
Figure B. A copied book record, with only authors, category and source copied from the source record
VBA Code
The cmdCopyRecord button on fpriEBookNotes calls the CopyRecord procedure listed below:
Private Sub CopyRecord(frm As Access.Form)
On Error GoTo ErrorHandler
Dim CopyFields() As Variant
Dim lngOldBookID As Long
Dim lngNewBookID As Long
lngCount = CountControls(frm)
Debug.Print “Number of controls to copy: ” & lngCount
‘Redimension array with actual number of controls to copy
ReDim CopyFields(lngCount – 1, 1)
‘Cycle through all controls on form Detail section
‘(except those marked “No Copy”) and save to an array
lngCount = 0
lngArrayNo = 0
lngOldBookID = frm![BookID]
For Each ctl In frm.Section(acDetail).Controls
strControlName = ctl.Name
‘Debug.Print “Control name: ” & strControlName
If InStr(ctl.Tag, “No Copy”) = 0 Then
‘Check field type and skip those that don’t have values
lngControlType = ctl.ControlType
Select Case lngControlType
‘Controls with values
Case acTextBox
GoTo Copy
Case acComboBox
GoTo Copy
Case acCheckBox
GoTo Copy
Case acBoundObjectFrame
GoTo Copy
Case acListBox
GoTo Copy
Case acOptionGroup
GoTo Copy
Case acOptionButton
‘Check for control source, and only copy from
‘option buttons with one (bound option buttons)
strTest = ctl.ControlSource
GoTo Copy
Case Else
‘No Value property; nothing to copy
GoTo NoCopy
End Select
Copy:
strFieldName = ctl.ControlSource
varFieldValue = ctl.Value
Debug.Print strFieldName & ” value: ” & varFieldValue
CopyFields(lngArrayNo, 0) = strFieldName
CopyFields(lngArrayNo, 1) = varFieldValue
lngArrayNo = lngArrayNo + 1
lngCount = lngCount + 1
NoCopy:
End If
Next ctl
Debug.Print “Number of controls to copy: ” & lngCount
‘Create recordset, add a new record, and copy data to it from array
Set dbs = CurrentDb
strTable = “tblEBooks”
Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)
With rst
.AddNew
For lngArrayCount = 0 To lngCount – 1
Debug.Print “Processing row ” & lngArrayCount
strFieldName = CopyFields(lngArrayCount, 0)
varFieldValue = CopyFields(lngArrayCount, 1)
Debug.Print “Field name: ” & strFieldName
Debug.Print “Field value: ” & varFieldValue
.Fields(strFieldName) = varFieldValue
Next lngArrayCount
lngNewBookID = ![BookID]
.Update
.Close
End With
‘Copy authors to linked table for new book
Call CopyAuthors(lngOldBookID, lngNewBookID)
‘Requery form and go to new record
frm.Requery
DoCmd.GoToRecord , frm.Name, acLast
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err.Number = 2455 Then
‘Unbound option button
Resume NoCopy
Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit
End If
End Sub
Private Sub CopyAuthors(lngOldBookID As Long, lngNewBookID As Long)
On Error GoTo ErrorHandler
Dim lngAuthorID As Long
Dim qdf As DAO.QueryDef
Dim rstSource As DAO.Recordset
Dim rstTarget As DAO.Recordset
Dim strQuery As String
Dim strSQL As String
Dim strTable As String
‘Check whether there are any authors to copy
On Error Resume Next
strQuery = “qryCopyBooks”
DoCmd.DeleteObject acQuery, strQuery
On Error GoTo ErrorHandler
strSQL = “SELECT * FROM tblEBookAuthors ” _
& “WHERE BookID = ” & lngOldBookID & “”
Debug.Print “SQL: ” & strSQL
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef(strQuery, strSQL)
lngCount = Nz(DCount(“*”, strQuery))
Debug.Print “No. of authors: ” & lngCount
If lngCount = 0 Then
Debug.Print ” no authors to copy; canceling”
GoTo ErrorHandlerExit
End If
‘Copy authors for new book
strTable = “tblEBookAuthors”
Set rstSource = dbs.OpenRecordset(strQuery, dbOpenDynaset)
Set rstTarget = dbs.OpenRecordset(strTable, dbOpenDynaset)
Do While Not rstSource.EOF
lngAuthorID = rstSource![AuthorID]
rstTarget.AddNew
rstTarget![BookID] = lngNewBookID
rstTarget![AuthorID] = lngAuthorID
rstTarget.Update
rstSource.MoveNext
Loop
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
The final procedure, CountControls, uses code very similar to the code that iterates through the form controls in the CopyRecord procedure.
References
The code in the sample database needs the following references (in addition to the default references):
Microsoft DAO 3.6 Object Library
If you import code or objects into a database of your own, you may need to set this reference. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file, may be downloaded from the Access Archon page of my Web site. It is accarch133.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Copying Records and Linked Records.mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
