I hope someone can help...I feel like I'm walking in the dark without a
flashlight (I'm NOT a programmer but have been called to task to do
some work in Access that is above my head).
I have code that will successfully copy a record and append the
information to a new record in the same table (parent table) within a
form. However, there are related child tables with primary keys (set
to Autonumber) stored in sub-forms. That information is not getting
copied to the new record. Here is the code (found it on
http://www.access.qbuilt.com/html/vba2.html#CopyRec).
Private Sub Command61_Click()
On Error GoTo ErrHandler
Call copyRecord(Me.RecordSource, "OANo", Me!OANo.Value)
Me.Requery
Exit Sub
ErrHandler:
MsgBox "Error in CopyRecBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub ' CopyRecBtn_Click( )
************* The following is in a module...
Public Sub copyRecord(sDataSrc As String, sPKey As String, nPKeyValue
As Long)
On Error GoTo ErrHandler
Dim recSet As DAO.Recordset
Dim rows() As Variant
Dim sqlStmt As String
Dim idx As Long
Dim fOpenedRecSet As Boolean
sqlStmt = "SELECT * " & _
"From (" & sDataSrc & _
") WHERE (" & sPKey & " = " & nPKeyValue & ")"
Set recSet = CurrentDb().OpenRecordset(sqlStmt)
fOpenedRecSet = True
rows() = recSet.GetRows(1)
recSet.AddNew
For idx = 0 To (recSet.Fields.Count - 1)
If (recSet.Fields(idx).Name <sPKey) Then
recSet.Fields(idx).Value = rows(idx, 0)
End If
Next idx
recSet.Update
CleanUp:
If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If
Set recSet = Nothing
Erase rows()
Exit Sub
ErrHandler:
MsgBox "Error in copyRecord( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub ' copyRecord( )
******************************
I think what I need to do is change the line:
Call copyRecord(Me.RecordSource, "OANo", Me!OANo.Value)
Here's examples of my tables (there are many child tables, this is an
example only):
Parent Table
Name: TOrdAck
OANo: Primary Key, Autonumber
Date: Date Field
Comments: Text
Etc....
Child Table
Name: TTool
ToolID: Primary Key, Autonumber
OANo: Number
Comments: Text
Etc....
Is it the Call line that needs to change so that it copies the main
table AND the TTool table? Can I just change the above line or do I
have to also change something in the Public Sub copyRecord function?
Sandy