Re: Foreign Key problem

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"FP1" <f@xxxxxxxxxx> wrote in message
news:Xns99A399D956FC0fchancecom@xxxxxxxxxxxxxxxx
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)

.

Relevant Pages

  • Foreign Key problem
    ... updated with a primary key and matching foreign key on insert. ... sake of simplicity, I have two tables, tbl1 and tbl2. ...
    (microsoft.public.access.formscoding)
  • Re: Monthly Total
    ... Can we assume there is relationship between tbl1 and tbl2 based on the ID, ... Accept Group By in the Total row under this field. ... ID1 Date Error ...
    (microsoft.public.access.queries)
  • Re: When does foreign key update?
    ... running a join query on ... creates a primary key for tbl1. ... field3, the foreign key gets set to the primary automatically. ... do NOT enter any data into field3 or 4 (tbl2), ...
    (microsoft.public.access.formscoding)
  • Re: When does foreign key update?
    ... running a join query on ... creates a primary key for tbl1. ... field3, the foreign key gets set to the primary automatically. ... do NOT enter any data into field3 or 4 (tbl2), ...
    (microsoft.public.access.formscoding)
  • Re: linking two forms (2 cont)
    ... Andrey, ... from tbl1 to tbl2 using Tools/Relationship. ... That way, whenever you create a new record in tbl1, a corresponding ... Candia Computer Consulting - Candia NH ...
    (microsoft.public.access.forms)
Loading