Cascade Update Behavior of Linked Up Sub-Form

  • Thread starter Thread starter Access User
  • Start date Start date
A

Access User

When I change the ID's value on my main form, the child-subform's ID value
does not change unless I move the scroll wheel to the next record and then
return to the one having the desired change. Is this the way it's supposed to
work? I would've preferred a kind of 'instant' change that took place the
moment the cursor left the ID field on the main form.
 
Access User,

If you mean that you are actually altering/editing the value of the ID
primary key field of an existing record, then I must first of all say
that this is an extremely unusual procedure in a database.

Nevertheless, I will assume what you doing here is valid and for a good
purpose.

I gather from the subject header of your post that you have Cascade
Updates enabled within the Relationship definition between the ID in the
main table and the linked foreign key field of the child table(s). Ok,
that's fine.

So yes, under those circumstances, I think what you are seeing is
expected behaviour. I would expect the changed ID value to cascade
through to the child table only when the change to the parent table is
saved. Which in you case is happening via moving to a new record. So I
think the way to overcome this is to force the save to the parent
record. Not 100% certain without knowing what you've got there, but you
could try putting this code on the After Update event of the ID textbox
on the main form:
DoCmd.RunCommand acCmdSaveRecord

Presumably the ID field is specified in the Link Master Fields and Link
Child Fields properties of the subform.
 
Yes, I am assuming that the user has committed and decided to recitfy an
entry error wrt the PK - they are only human, 'fter all.

I am not at my PC at the moment, but I believe the approach I took was to do
a 'refresh', which had a similar effect but did not re-arrange the records in
their sorted order which was coded into the qry record source object behind
the sub-form.

I will try your approach tomorrow and see what happens. I'm assuming it works.
 
Back
Top