Strange subform behaviour

I've recently created a parent form with two correlated continuous subforms
linked via a hidden unbound text box control in the parent form. The first
of the subforms, whose control's LinkChildFields and LinkMasterFields
properties are references to columns in the underlying recordsets, works as
expected. In the second, however, which is correlated with the first by the
LinkMasterFields property referencing the hidden text box in the parent form,
as soon as any data is entered into a control the subform's record is saved.
This happens immediately on entering the first character, not when the
control is updated by tabbing to the next control in the subform.

Another consequence of this is that, if the record which is saved is earlier
in the underlying recordset's sort order than any previously inserted record
(s), the new row immediately moves to its correct position in the sort order.
This is very confusing of course, so I've had to remove the ORDER BY clause
from the underlying query to prevent this. Also I've had to make sure that
another column in which Null is not allowed has a default value. None of
this affects the basic functionality, but it would be better if I could order
the subform's rows and do without the default value for the other column.

As this behaviour is only experienced in the second subform I can only assume
that it derives from the fact that the second subform is referencing the
hidden text box as the LinkMasterFields property. I initially thought that
the subform was being requeried, which would explain why the row jumps to its
correct position in the sort order, but if that were the case it would be
expected that the record pointer would return to the first row in the subform
(as happens for instance when a requery is forced if a item is added to a
combo box's list by means of its NotInList event procedure in a row in a
subform), but this is not the case; the record pointer stays on the new
record.

The subform has no code whatsoever in its module, so its not that which is
causing the behaviour. The whole thing is a mystery to me.

Ken Sheridan
Stafford, England

--
Message posted via http://www.accessmonster.com

"KenSheridan via AccessMonster.com" u51882@uwe wrote in message
news:9f42997c63ecf@uwe...
I've recently created a parent form with two correlated continuous
subforms
linked via a hidden unbound text box control in the parent form. The
first
of the subforms, whose control's LinkChildFields and LinkMasterFields
properties are references to columns in the underlying recordsets, works
as
expected. In the second, however, which is correlated with the first by
the
LinkMasterFields property referencing the hidden text box in the parent
form,
as soon as any data is entered into a control the subform's record is
saved.
This happens immediately on entering the first character, not when the
control is updated by tabbing to the next control in the subform.

Another consequence of this is that, if the record which is saved is
earlier
in the underlying recordset's sort order than any previously inserted
record
(s), the new row immediately moves to its correct position in the sort
order.
This is very confusing of course, so I've had to remove the ORDER BY
clause
from the underlying query to prevent this. Also I've had to make sure
that
another column in which Null is not allowed has a default value. None of
this affects the basic functionality, but it would be better if I could
order
the subform's rows and do without the default value for the other column.

As this behaviour is only experienced in the second subform I can only
assume
that it derives from the fact that the second subform is referencing the
hidden text box as the LinkMasterFields property. I initially thought
that
the subform was being requeried, which would explain why the row jumps to
its
correct position in the sort order, but if that were the case it would be
expected that the record pointer would return to the first row in the
subform
(as happens for instance when a requery is forced if a item is added to a
combo box's list by means of its NotInList event procedure in a row in a
subform), but this is not the case; the record pointer stays on the new
record.

The subform has no code whatsoever in its module, so its not that which is
causing the behaviour. The whole thing is a mystery to me.


I've never seen this, Ken, and I've used the technique you describe several
times. What version & SP of Access is this?

I've no idea what could be causing this, if you don't have code somewhere
that is both requerying and repositioning the subform. You don't by any
chance call any public functions directly via expressions in event
properties? Could there be a conditional formatting expression that calls a
function that causes this?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Dirk:

Thanks for the prompt reply. Its Access 2002 SP3. There are definitely no
functions being called, either as event properties or in event procedures, so
it’s very weird. I've never noticed the behaviour before, but that may
simply be because where I've used the technique before the correlated subform
has contained a single control, usually a combo box, and the rows are not
ordered, so the behaviour hasn't been readily apparent.

I'm going to try referencing the first subform's key directly via the parent
form's Form property to see if that has an effect. This can be done (in fact
the LinkMasterFields property can even reference a control on a completely
separate form) provided that the second subform is requeried in the first
subform's Current event procedure. I'll post back when I've tried this.

Ken Sheridan
Stafford, England

Dirk Goldgar wrote:
I've recently created a parent form with two correlated continuous
subforms
[quoted text clipped - 44 lines]
The subform has no code whatsoever in its module, so its not that which is
causing the behaviour. The whole thing is a mystery to me.

I've never seen this, Ken, and I've used the technique you describe several
times. What version & SP of Access is this?

I've no idea what could be causing this, if you don't have code somewhere
that is both requerying and repositioning the subform. You don't by any
chance call any public functions directly via expressions in event
properties? Could there be a conditional formatting expression that calls a
function that causes this?


--
Message posted via http://www.accessmonster.com

I can't explain it, but by setting the second subform's LinkMasterFields
property to a fully qualified reference to the first subform's key, and
requerying the second subform in the first's Current event procedure it works
as designed. No immediate save and no jumping to the position in the sort
order.

Unfortunately the same tactic does not seem to stop the immediate requerying
of a subform after an item is added to the list of a combo box in it via the
NotInList event procedure, about which we had a post a few days ago.

Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

Interesting result.
I have found on occasion that if I use a textbox on the main form for the
link field, I must put square brackets around the textbox name in the link
master field.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"KenSheridan via AccessMonster.com" u51882@uwe wrote in message
news:9f4553478f584@uwe...
I can't explain it, but by setting the second subform's LinkMasterFields
property to a fully qualified reference to the first subform's key, and
requerying the second subform in the first's Current event procedure it
works
as designed. No immediate save and no jumping to the position in the sort
order.

Unfortunately the same tactic does not seem to stop the immediate
requerying
of a subform after an item is added to the list of a combo box in it via
the
NotInList event procedure, about which we had a post a few days ago.

Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

Jeanette:

I think I did that, but now I've changed it I can't be 100 percent sure.
When I get a moment I'll make copies of the form and subforms and try it
again with and without the brackets. It was obviously recognizing the text
box on the parent form as the second subform was returning the correct rows
as I navigated from row to row in the first subform, but the behaviour was so
bizarre it would not surprise me in the least to find that the brackets make
a difference.

Ken Sheridan
Stafford, England

Jeanette Cunningham wrote:
Interesting result.
I have found on occasion that if I use a textbox on the main form for the
link field, I must put square brackets around the textbox name in the link
master field.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I can't explain it, but by setting the second subform's LinkMasterFields
property to a fully qualified reference to the first subform's key, and
[quoted text clipped - 11 lines]
Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1