Foreign Key problem

[FP1]

Can't get an insert to work with linked tables. I need both tables to be
updated with a primary key and matching foreign key on insert. For the
sake of simplicity, I have two tables, tbl1 and tbl2.

tbl1
tbl1_pk primary key
field1
field2

tbl2
tbl2_pk
tbl1_fk foreign key
field3
field4

Without a relationship defined, running a join query on tbl2.tbl1_fk=tbl1
_pk (it's a left out join, tbl1 is left) as record source to a form.

On the form, on an insert, filling any tbl1 value (field1 or field2)
creates a primary key for tbl1 (expected) but ONLY if enter a value for
tbl2 (field3, field4). If I do NOT enter any data into field3 or 4 (tbl2),
the foreign key is not updated, I get a "the Microsoft jet database engine
cannot find a matching record..." message, which is bad. I tried different
join types and constraints, same result.

Is there a simple way to force the tbl2 (outer joined record) to be created
with a foreign key if the other fields in tbl2? (BTW the primary key gets
generated as soon as I enter anything into field3 and exit the control
which seems odd to me)

 

[Allen Browne]

For native Access (JET) tables, the AutoNumber is assigned as soon as you
start entering a new record, so your last comment is correct.

Clearly the new record has to be created in tbl1 before there can be a
*related* record in tbl2.

If you want to create a record in tbl2 as soon as a new record is entered in
tbl1, use the AfterInsert event procedure of the *form* where the data is
added. In this event, either execute an Append query statement to insert the
related record into tbl2.

You may find it easier to use a main form bound to tbl1, with a subform
bound to tbl2 rather than try to do them both in the one form. If you do
this, you can AddNew to the RecordsetClone of the subform so the new record
shows up immediately (i.e. without having to Requery the subform.)

 

[FP1]

For native Access (JET) tables, the AutoNumber is assigned as soon as
you start entering a new record, so your last comment is correct.

Clearly the new record has to be created in tbl1 before there can be a
*related* record in tbl2.

If you want to create a record in tbl2 as soon as a new record is
entered in tbl1, use the AfterInsert event procedure of the *form*
where the data is added. In this event, either execute an Append query
statement to insert the related record into tbl2.

You may find it easier to use a main form bound to tbl1, with a
subform bound to tbl2 rather than try to do them both in the one form.
If you do this, you can AddNew to the RecordsetClone of the subform so
the new record shows up immediately (i.e. without having to Requery
the subform.)

Thanks, Allen. I was starting to realize that a subform might be the way
to go, but I'll try the afterinsert solution. I was thinking I'd need to
catch it before insert, but I guess not!