Creating Child Records

  • Thread starter Thread starter fh721
  • Start date Start date
F

fh721

Hi,

I have a problem I'm hoping someone can help me with. I have 3
tables, 1 setup as a parent and 2 as child, the relationship is 1 to 1
with referential integrity enforced ( the number of fields required me
to split the table into 3 ), the primary key is the same on each table.

In a form when I create a new record in the parent table I would
like to create a new record in each of the child tables and initialize
some fields in the child records.

I placed a command button ( using the wizard ) to add the new
record to the parent table and assume here is where I would put
the code to add the child records and initialize fields.

Any help would be appreciated and thank you.
 
Hi,

I have a problem I'm hoping someone can help me with. I have 3
tables, 1 setup as a parent and 2 as child, the relationship is 1 to 1
with referential integrity enforced ( the number of fields required me
to split the table into 3 ), the primary key is the same on each table.
Click to expand...

One to one relationships due to the number of fields VERY strongly
suggest that you need to rethink the normalization of your data. 255
fields is four times more than I've ever needed in a Table. Do you
perhaps have some one-to-many relationships embedded in each record?
Or what kind of Entity has over 500 independent, atomic, unique
attributes? I'd be most interested in seeing one!
In a form when I create a new record in the parent table I would
like to create a new record in each of the child tables and initialize
some fields in the child records.
Click to expand...

Empty placeholder records are usually a bad idea in any case... but
see below...
I placed a command button ( using the wizard ) to add the new
record to the parent table and assume here is where I would put
the code to add the child records and initialize fields.
Click to expand...

What you would need to do is either run two Append queries appending
one record to each table, or open a Recordset based on the table and
add a record... but as noted above, a table redesign would make this
operation unnecessary and probably vastly improve your experience with
Access!
 
Hi John,

Thank you for responding. I'll have to give
you a bit of backround on why this is the way
it is.

The driving force on this table design is ease
of import and export, the data for these tables
is coming from a mainframe application that
supplies the data in 3 ASCII text files, the
only duplicate field ( out of aprox. 600 fields )
in all 3 files is a 17 digit numeric field. The
data needs to be exported in the exact same format
as the import files, so rather than import the data
into a database with the properly designed tables
and then reassembling it with querys to the format
of the ASCII test files to be exported to the mainframe
we decided to just do it this way ( this is also just
a short term solution until a new system is deployed
and I don't want to spend alot of time or effort on this ),

The Access application will work like this: each
week the data in the existing tables will be deleted
and new files supplied from the mainframe will be
imported, the Access application will do editing and
new record additions ( while I may not actually need
data in the child tables I'll still have to supply
blank records with just the matching key to the parent
table for exporting ). Also the most records will be
about 2500 so I'm hoping performance won't be to
much of a problem.

Again let me thank you for your help, I appreciate it.

Bob
 
In a form when I create a new record in the parent table I would
like to create a new record in each of the child tables and initialize
some fields in the child records.
Click to expand...

<explanation elsewhere in the thread ommitted>

Ok... thanks for the clarification. Sounds like you're doing it right.

What you would want to do is use the mainform's AfterInsert event to
open a Recordset based on each of the related tables and insert a
record: something like

Private Sub Form_AfterInsert()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("FirstChildTable", dbOpenDynaset)
rs1.AddNew
rs1!KeyField = Me!KeyField
rs1!ThisField = <something appropriate>
rs1.Update
rs1.Close
<same for rs2>
Set rs1 = Nothing
Set rs2 = Nothing
End Sub

You may want to check to see if a record has already been imported to
the tables (you say you'll be importing three text files) using
FindFirst before you add a (possibly duplicate) record.
 
Hi John,

Thanks, I wouldn't have come up with that one
on my own. I appreciate your help

Bob
 
Back
Top