473,225 Members | 1,344 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

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

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
2 3428
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Ilan Sebba | last post by:
I am trying to add a record using SQL. My problem is that the primary keys are foreign keys, and these foreign keys are autonumbers. I therefore do not know the primary keys of the record I am...
7
by: Megan | last post by:
Hi everybody- I inherited a database that somehow uses a bound combo box as a record selector. Let me give you some background. The form is based on data from 2 tables. The first table, Person,...
1
by: Rosy | last post by:
I have a form that brings in the customer address info. On this form I would like to put a sub-form that displays the Peachtree ID for this customer. This works fine. However, when I create a...
19
by: davidgordon | last post by:
Hi, I need some pointers/help on how to do the following if it possible: In my access db, I have the following: Tables: Products, Sub-Assembly, Product-Pack Table, Products
0
by: igendreau | last post by:
I have a database with a Header table. Each record in tblHeader has two One-to-Many Relationships: with tblLines and tblKeys. The HeaderID field ties tblHeader to the other two tables. The data...
4
by: CK | last post by:
Good Morning, I have a person table with personID. I have a personRate table with personID, rateID, and effectiveDate. I need to select fields from personRate, but I want the fields from the...
6
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called...
7
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent...
0
by: emalcolm_FLA | last post by:
Hello and TIA for any help with this non profit Christmas assistance project. I have an applicant (app history) and child (child history) tables (4 total). I need to grab the next available (in...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.