When does foreign key update?

[FP1]

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?

 

[FP1]

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.

 

[Marshall Barton]

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.

 

[FP1]

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.

 

[Marshall Barton]

Marshall Barton wrote


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?

 

[Marshall Barton]

Marshall Bartonwrote

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.

Ok, I won't ty to understand why ;-)

Try setting field2 to some legal value (which should
populate the pk and fk fields), then setting it back to
Null.

I'm not clear enough on what else is going on to guess at
what event you should use, possible a button's Click event?

 

[FP1]

Ok, I won't ty to understand why ;-)

Try setting field2 to some legal value (which should
populate the pk and fk fields), then setting it back to
Null.

I'm not clear enough on what else is going on to guess at
what event you should use, possible a button's Click event?

Not a bad idea as a workaround. Thanks! I don't need to understand why
right now either, just how!