Wierd subform behaviour, subform not cycling through after updating a subform record

[Mikal]

I have a form using a tab control for editing customer details and a
single subform that can be used to add/edit up to 5 addresses.

The underlying tables are linked by a ClientID field in each table. The
subform is linked as follows Master Field ClientID;[optGrpLocation] and
Child Field ClientID;Location (being any value from 1 to 5). The idea
being that a change in the option group on the main form causes the
subform to display the relevant address.

Everything works fine whilst scrolling through records, I can navigate
between the different addresses using the option group, I can navigate
between records on the main form and the address is updated. However,
as soon as a record in the subform is edited or added the contents of
that record remain on the subform no matter what I do. For example if I
go to another record on the main form the old subform contents remain
displayed, and the subform loses its link to the record on the main
form (ie the subform displays a different clients address on the
subform and the ClientID fields are different). I have tried requerying
the subform and changing the subforms recordsource through code. This
only leads to the subform remembering the value of the last editied
field in the subform and displaying it in that field until such time as
I close the form.

There is no code in the subform (or rather i've stripped it all out for
now) Using A97.

Any help much appreciated as its had me stumped for too long.

Cheers

Mikal

 

[Allen Browne]

This sounds frustrating, and we might have to ask Access what's going on.

After opening the frm, open the Immediate Window (Ctrl+G) and enter:
? Forms![Form1].[Sub1].LinkMasterFields
replacing Form1 with your main form name, and Sub1 with the name of your
subform control. You should see the response:
ClientID;[optGrpLocation]
Then ask it to give you both those values:
? Forms!Form1!ClientID, Forms!Form1!optGrpLocation
You should see the 2 values returned.
Then perform the edit that messes things up.
Once it goes wrong, ask those questions again.

Is the LinkMasterFields property being lost?
Is the subform inheriting a value that is inappropriate?

Another angle would be to open the subform's table directly, and examine
what happened the record. Is the ClientID still the correct value? What
about the Location number?

In this way, you can determine whether the data itself is going wrong, or
the form is displaying things wrongly.

 

[Mikal]

I tried those things. The LinkMaster and Chiled fields are being
retained correctly.

The data in the table is also stored correctly. The good news is that
the subform data is not being updated with wrong values.

Mystery Solved. There was an ExitControl event that InitialCaps the
address causing the record not to be saved, but i was allowed to move
to another record in the main form with the subform being "locked" as
it were. Still seems wierd to me but at least I have solved the mystery
by putting the function in the lost focus event.

Sometimes leaving a problem and starting with a fresh head is the only
way to go. Thanks Allen for your reply.
Mikal