PK update OK in Query not Form

[Guest]

Hi:

I have a form based on a query with 5 related tables, each related table is
shown as a subform. There is a left join between the main table and the
related tables and the query has been set to Inconsistent Updates. I have
activated (against my better judgement) cascade updates within the
relationships because on the odd occasion the primary key in the main table
needs to be changed.

Here in lies the problem:

If I change the PK in the underlying query it updates in all of the related
tables but if I change it on the form it doesn't and my subforms go to a new
record. Is this because the Master/Child field is the PK and so when the PK
changes it is trying to find the matching record before the PK is updated?

If so, is there a workaround other than attaching an update query to a
"change" button on the main form?

Any suggestions would be great.

Thanks

 

[Dirk Goldgar]

Hi:

I have a form based on a query with 5 related tables, each related
table is shown as a subform. There is a left join between the main
table and the related tables and the query has been set to
Inconsistent Updates. I have activated (against my better judgement)
cascade updates within the relationships because on the odd occasion
the primary key in the main table needs to be changed.

Here in lies the problem:

If I change the PK in the underlying query it updates in all of the
related tables but if I change it on the form it doesn't and my
subforms go to a new record. Is this because the Master/Child field
is the PK and so when the PK changes it is trying to find the
matching record before the PK is updated?

If so, is there a workaround other than attaching an update query to a
"change" button on the main form?

A side issue: If the related tables are all shown in subforms, why are
they included in the main form's recordsource? That would seem
unnecessary.

I think you're right about the what's going on with the subforms. You
might try something like this in the AfterUpdate event of the control
that is bound to the PK field:

'----- start of proposed "air code" -----
Private Sub PKField_AfterUpdate()

If Not IsNull(Me!PKField) Then

If Not Me.NewRecord Then

' Force the record to be saved.
Me.Dirty = False

' Requery the various subforms.
' *** NOTE: MAY NOT BE NECESSARY
Me!sfSubform1.Requery
Me!sfSubform2.Requery
Me!sfSubform3.Requery
Me!sfSubform4.Requery
Me!sfSubform5.Requery

End If

End If

End Sub
'----- end of proposed "air code" -----

You'd force the record to be saved when the PK of an existing record is
changed, and then requery the subforms. But you may not need to
requery; I'm not sure.

 

[Guest]

Brilliant, that did it.
Thanks Dirk.

p.s. I did not have to requery the subforms.
p.p.s. The 5 tables are not in the query.....poor wording on my part

Thanks again.