H E L P - How to get Update Query to Add Records to a related table ??

[Will]

THE SETUP:
- we are running a query with fields from two related tables... tblParent
and tblChild...
- selecting records on a field in tblParent
- now change to Update Query
- to update a field in tblChild

THE PROBLEM: If there is no tblChild record for a particular tblParent
record... none is added.

We want Access to add a tblChild record if none exist for any tblParent
record.

How can we do this?

Oh, we are doing this using the Access query grid and not raw SQL or Code.

Thanks for any help.

 

[Allen Browne]

Access lacks triggers, so you will not be able to get Access to add a new
relaed record from the query grid.

Instead use a form. (The form can be in datasheet view if you want it to
look like a query/table.) Use the AfterInsert event procedure of the form to
execute an Append query statement to add a record to the related table.

 

[HELENA SMITH]

(e-mail address removed) hi are you

 

[Will]

Allen,

- We have about 2,000 records in tblParent that meet our 'criteria'
- For each of those we want to add a related record in tblChild
- And in each populate a "yes/no" field with 'yes'
- In other words add the related child record and then check a Check Box
named IncludeMe in each tblChild record
- I was attempting to do this using an update query but if the parent record
has no child... no workie !

Will "AfterInsert" do this? I would think "AfterInsert" would add the child
record as we 'inserted' new parent records.
Maybe I didn't ask the question clearly the first time. (Sorry.)

Hopefully you or someone will take another shot at getting me going on this
as I need to get it done this morning.

thanks for any help.

 

[Allen Browne]

For your existing records, use an Append query to add the related records.

The form's AfterInsert event will add the child record for new records you
add to the main table in the future.

 

[Will]

Allen, the Append Query did what I wanted..... Thanks!