Copy Record - How do I copy info to child tables?

Swinky

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

Dec 13 '06 #1

Rich P

Greetings,

When you say you want to copy records to child tables are you saying
that you want to copy records that exist in one table and add them to
another table (the child table)? Or are you just adding new data to a
table(s)?
Here is one way to add a record to a table using sql. This example
copies a record from one table and inserts it into another table.

Sub copyRecordfromTblAtoTblB(IDarg as Integer)

Insert Into TblB(fID, col1, col2, col3)
Select ID, col1, col2, col3 From TblA
Where ID = IDarg

End Sub

This is just a straight forward example with no error trapping or
comments. You call the Sub and pass an ID value to it. The sub will
add a record to TblB from TblA. What record will get copied? you ask.
The record from TblA which contains an ID field with the same value as
IDarg will get copied. Note: If TblA contains multiple records with
the same ID (say a foreign key ID) then all of those records will get
added to TblB.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

Dec 14 '06 #2

Swinky

Rich,

What I'm attempting to do is a little more complicated. Here's what
I'm trying to do:

1. Copy a record from the parent database into a new record (same
database). Actually this is working using the code I provided earlier.
2. There are additional child tables with records that also need to be
copied when the parent is copied. To help explain, here is an example:

Parent Table
Field 1: ParentID (Autonumber)
Field 2: Date
Etc.

Child Table (1)
Field 1: Child(1)ID (Autonumber)
Field 2: ParentID
Etc.

Child Table (2)
Field 1: Child(2)ID (autonumber)
Field 2: ParentID
Etc.

All the tables are related based on the ParentID. In my form, I update
the Parent Table and in sub-forms, I update the child data.

The problem is, when I copy, only the parent data is copied to the last
record---it doesn't copy the child table data and carry it to the new
parent record.

I've been going at this for 2 solid days now & can't get it to work
(doesn't help that I don't know VB---although I'm starting to learn by
the seat of my pants). If I can keep the code that does work and
modify it, I would be a happy camper.

Sandy

Rich P wrote:

Greetings,

When you say you want to copy records to child tables are you saying
that you want to copy records that exist in one table and add them to
another table (the child table)? Or are you just adding new data to a
table(s)?
Here is one way to add a record to a table using sql. This example
copies a record from one table and inserts it into another table.

Sub copyRecordfromTblAtoTblB(IDarg as Integer)

Insert Into TblB(fID, col1, col2, col3)
Select ID, col1, col2, col3 From TblA
Where ID = IDarg

End Sub

This is just a straight forward example with no error trapping or
comments. You call the Sub and pass an ID value to it. The sub will
add a record to TblB from TblA. What record will get copied? you ask.
The record from TblA which contains an ID field with the same value as
IDarg will get copied. Note: If TblA contains multiple records with
the same ID (say a foreign key ID) then all of those records will get
added to TblB.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

Dec 14 '06 #3