Parent/Child Append Question

[davidson12]

Hello Everyone --

I've been working on an audit database for my company and I'm running into some issues with appending information to a child table.

Here's the basic structure of the two tables, and what I'm looking to do:

Table A (parent)

• Audit ID (Autonumber, Primary Key)
• Customer ID
• Audit Trigger Date

Table B (child)

• Audit ID
• Account Number
• Pass/Fail

Table B would contain a sample of 20 customer accounts from customer ID above. I currently have a one-to-many join on Audit ID between the two tables. I can append new rows to table A without issue, but I'm just not sure what is the best way to append my 20 rows to table B without violating my relationship. Any suggestions?

[llkhoutx]

Add a primary key to the child table.

Parent child relationships work very, very well in form, subform environments. However, never create a child (subform) record without a parent (form) record, if you do, you'll have orphan an child with no parent. Always create the parent record first, regardless of the method used.

If you create the relationship between the two tables in the Relationship window with cascading updates and deletes, you can update the tables in the table view, while displaying the child table. Note that the foreigh key (to the parent table) will be automatically updated, but not displayed.

[davidson12]

Add a primary key to the child table.

Parent child relationships work very, very well in form, subform environments. However, never create a child (subform) record without a parent (form) record, if you do, you'll have orphan an child with no parent. Always create the parent record first, regardless of the method used.

If you create the relationship between the two tables in the Relationship window with cascading updates and deletes, you can update the tables in the table view, while displaying the child table. Note that the foreigh key (to the parent table) will be automatically updated, but not displayed.

I left out some information. I do have a primary key on my child table, it's an account audit ID which is an autonumber. I am able to create records in my child table via a form/subform, but what I'd like to do is have access run a job nightly which will create 1 record in my parent table and 20 records in my child table.

[llkhoutx]

What's in those 20 records? Are they empty?

[davidson12]

What's in those 20 records? Are they empty?

No, the 20 records would represent 20 account numbers. Then each one of the 20 accounts would have items that would be marked as essentially "yes/no" fields.

Ideally the database will refresh overnight and if a series of business rules are met then one record would be appended to the parent table (customer ID) and 20 records (customer account numbers) would be appended to the child table.

[llkhoutx]

Here's the code to create the 20 records

Sub CreateRecords(PrimaryKey as long)
dim db as database
dim rs as recordset
dim i as integer
set db=currentdb
set rs=db.openrecordset("ChildTableBName",dbopendynaset)
for i = 1 to 20 ' loop 20 times
  rs.addnew ' add a new record to recordset
     rs!ForeignKey = PrimaryKey 
     rs!accountnumber = ---- I have no idea where the account Nos. come from
  rs.update ' save the record
Next
rs.close
db.cloise
set rs=nothing
set db=nothing
End Sub 

[ychartrand]

Thanks, found this helpful for related issue.
Regard, ychartrand