Re: When does foreign key update?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Marshall Barton <marshbarton@xxxxxxxxxx> wrote in
news:pum0e39doicfbfskt3ec7u46lobbhcmf1n@xxxxxxx:

FP1 wrote:

Marshall Barton wrote

FP1 wrote:

For the sake of simplicity, I have two tables, tbl1 and tbl2

tbl1
tbl1_pk primary key
field1
field2

tbl2
tbl2_pk
tbl1_fk
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 a value for field1 or field2
creates a primary key for tbl1 (expected). If I enter a value for
field3, the foreign key gets set to the primary automatically. If I
do NOT enter any data into field3 or 4 (tbl2), the foreign key is
not
updated, which id bad. (If I define a constraint, I get a foreign
key
error in this situation.) I can force this to happen by using
me("tbl1_fk")=me("tbl1_pk")

The question is: Is this way it's supposed to work in Jet? Is there
a
simple way to force the update to happen even if the data in tbl2
isn't dirty?


If you insist on ceating a record with no data, then you are
doing it in a good a way as any.

Why do you want to force it? Seems like a useless action to
me.


Because both tables are represented on the form. They *may* change
data
belonging to table 2 or they may not. When they don't, the insert
fails.

When I try to update manually I get "you can't update this field"

When I try sneak in an insert through a query, the requery after it
gets
denied because it will wipe out the changes to the form.


What INSERT? Can't you just leave it as a new record in the
subform?


New record is what I mean (I think sql first, access second). How do I
save it without all the above problems? That's issue. There's no
subform, these are all on the same form.

There's a long gory explaination as to why, but I need to undo a bunch
of changes made to a proprietary system and migrate them to a new
database while retaining the same look and feel in access. It's easy to
add columns to one table, not so easy to split them out and make them
look and act the same when they're in different tables/databases.

--
Posted via a free Usenet account from http://www.teranews.com

.

Relevant Pages

  • 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: When does foreign key update?
    ... running a join query on ... On the form, on an insert, filling a value for field1 or field2 ... creates a primary key for tbl1. ... field3, the foreign key gets set to the primary automatically. ...
    (microsoft.public.access.formscoding)
  • Re: When does foreign key update?
    ... running a join query on ... On the form, on an insert, filling a value for field1 or field2 ... creates a primary key for tbl1. ... field3, the foreign key gets set to the primary automatically. ...
    (microsoft.public.access.formscoding)
  • Re: multiple field, primary key
    ... field3 text ... The primary key is the foreign key in several other tables. ... enters field1, field2, field3, and the afterupdate event of each is: ...
    (microsoft.public.access.tablesdbdesign)