I want DAO code to run when I click a button on a form.
The code will take a copy of the current record and
appends it to the recordset. I can do this. However, I
am struggling with the second stage where I need a copy of
all child records of this record in a related to be
appended to its recordset. The primary key for the newly
added record in the parent recordset (foreign key in the
newly added records in the child recordset) is an
autonumber in the parent table.
This is not as simple as it sounds; at least, not if you want a general
solution. I cobbled together the following routine, which seems to work
but has certain restrictions: (1) the subform's recordsource must be a
table or stored query, not an SQL statement, (2) the subform's
LinkMasterFields property must name a single control on the main form,
(3) the subform's LinkChildFields property must name a single field in
the subform's recordset, and (4) you must be using Access 2000 or later.
Here's the code, which is assuming the subform control (on the main
form) is named "SubformName":
'----- start of code -----
Private Sub cmdDupAll_Click()
Dim varOldMainKey As Variant
Dim varNewMainKey As Variant
Dim fld As DAO.Field
Dim strSQL As String
Dim strFieldList As String
Dim strLinkMaster As String
Dim strLinkChild As String
strLinkMaster = Me!SubformName.LinkMasterFields
If Left(strLinkMaster, 1) = "[" Then
strLinkMaster = Mid(strLinkMaster, 2, Len(strLinkMaster) - 2)
End If
varOldMainKey = Me.Controls(strLinkMaster).Value
strLinkMaster = Me.Controls(strLinkMaster).ControlSource
strLinkChild = Me!SubformName.LinkChildFields
If Left(strLinkChild, 1) = "[" Then
strLinkChild = Mid(strLinkChild, 2, Len(strLinkChild) - 2)
End If
' Build a list of the subform fields to be copied.
' This list must exclude the LinkChildField and the
' table's autonumber key.
For Each fld In Me!SubformName.Form.Recordset.Fields
If fld.Name = strLinkChild _
Or (fld.Attributes And dbAutoIncrField) <> 0 Then
' skip this field
Else
strFieldList = strFieldList & ", [" & fld.Name & "]"
End If
Next fld
' Copy the main form's record, noting the new ID
With Me.RecordsetClone
.AddNew
varNewMainKey = .Fields(strLinkMaster).Value
For Each fld In Me.Recordset.Fields
If fld.Name <> strLinkMaster Then
.Fields(fld.Name) = fld.Value
End If
Next fld
.Update
End With
' Build a SQL statement to copy the related records.
Select Case VarType(varNewMainKey)
Case vbString
varOldMainKey = _
"""" & _
Replace(varOldMainKey, """", """""") & _
""""
varNewMainKey = _
"""" & _
Replace(varNewMainKey, """", """""") & _
""""
Case vbDate
varOldMainKey = Format(varOldMainKey, "\#mm/dd/yyyy\#")
varNewMainKey = Format(varNewMainKey, "\#mm/dd/yyyy\#")
Case Else
varOldMainKey = Str(varOldMainKey)
varNewMainKey = Str(varNewMainKey)
End Select
strSQL = _
"INSERT INTO " & Me.SubformName.Form.RecordSource & " " & _
"([" & strLinkChild & "]" & strFieldList & ") " & _
"SELECT " & varNewMainKey & strFieldList & _
" FROM " & Me.SubformName.Form.RecordSource & _
" WHERE [" & strLinkChild & "] = " & varOldMainKey
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery
Me.Recordset.FindFirst "[" & strLinkMaster & "] = " & varNewMainKey
End Sub
'----- end of code -----
