Bug in MS Access 2003 subform events do not fire

[Leif]

I have Access 2003 with a Windows based server and XP Professional on the
client. I have a main form with no bound recordset. The main form contains
four subforms. All the subforms are bound forms. The main form has some
combo boxes that are used to control the subforms. On the main form is an
unbound text box that is populated by the the choice on the combo box. The
subforms use master/child link to the unbound main form control to coordinate
the display.

The main form has the vertical scrollbar enabled. The main form is long,
requiring scolling on all but high resolution displays.

The problem is this - Ater updating a record on the subform, and then
changing focus to another subform, sometimes the update event procedure is
fired, and sometimes it is not. My discovery is this - It all depends on if
the scroll bar is used or not. In other words, if the screen resolution is
low the scroll bar for the main form is active. If I used the scroll bar
the subform update event procedure is not fired until I close the form or
change the record, changing focus to a different subform does not cause it to
fire (unexpected Access
behavior).

If I display the form on the high resolution screen, such that the scroll
bar is not active, an update to a subform, and a change of focus to another
subform, causes the update event to immediately fire (expected Access
behavior).



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...53624cae9506&dg=microsoft.public.access.forms

 

[Allen Browne]

Leif, I wan't able to reproduce this, so I guess I'm missing something.

If you don't already have it make these 2 changes to each subform:
1. Set the Record Selector to Yes.
2. Add this line to the Before Update event procedure of the form:
Debug.Print Me.Name & ".BeforeUpdate at " & Now()

Now you will now see the icon change to a pencil when there are uncommitted
edits in the subform. You should be able to use the scrollbar on the main
form without committing the record in the subform, i.e. you can scroll the
main form, but the pencil icon stays in the subform's RecordSelector, and
the cursor flashes in the active field.

But when you click inside another subform, the record in the first subform
should be committed (the icon should change to a triangle.) Also if you open
the Immediate Window (Ctrl+G), you should see the comment that the event
fired.

If you do not see both things, post a reply.

 

[Leif]

Hi Allen,

Thanks for your reply. I made your requested changes. Three of the four
subforms already had the record selector set. I set the fourth. I also
added the debug statements.

I made the change to the top subform. I saw the pencil icon. I scolled
down to the bottom 3 subforms. I made a change to each of the subforms. I
saw all three subforms had the pencil icon. I scroll up to the top subform.
It also had a pencil icon. When I changed the record, using the combo box in
the main form, all the subforms were committed (triangle icon). The debug
statements read:

sfrm_Profile_Annotator_EditA.BeforeUpdate at 8/31/2006 7:07:17 AM
sfrm_Profile_Annotator_EditB.BeforeUpdate at 8/31/2006 7:07:18 AM
sfrm_Profile_Annotator_EditC.BeforeUpdate at 8/31/2006 7:07:18 AM
sfrm_Profile_Annotator_EditD.BeforeUpdate at 8/31/2006 7:07:18 AM

Regards,
Leif

 

[Allen Browne]

Okay, so the events did fire, and the record did save.

Does that mean the form is now working correctly, the record is being saved,
and the events do fire?

 

[Leif]

Yes, the events fire. I mentioned that in my first note. However, look that
the times of the messages. They did not fire as I moved focus from one
subform to the next (as they should), and made my changes. They fired all at
once when I changed the field the subforms are bound to.

Notice the times, they are all within 1 second. I may be fast moving from
one subform to another and updating, but I'm not that fast. It is proof that
the subforms are not firing the before update event when they lose focus (as
they should).

This presents a problem for me. I would like to ask the user if they want
to go forward with the update in the before update event. But, instead of
receiving a message as the user transfers from subform to subform, they
happen all at once when the main form combo box is changed.

Do you want to update?
Do you want to update?
Do you want to update?
Do you want to update?

How, if I run the application on a high resolution screen, such that I do
not need to scroll to update all the subforms, the before update events fire
as each subform loses focus.

I believe this is a MS bug since the screen resolution, and as a result
where the main form scroll bar is used, should not determine the firing order
of update events, IMO.

Regards,
Leif

 

[Albert D. Kallal]

How, if I run the application on a high resolution screen, such that I do

not need to scroll to update all the subforms, the before update events
fire
as each subform loses focus.

It is very possible that the above behaviour is by design. since the other
form is not visible, then updates to the sub-form control may not fire,
since the screen don't need to be updated..

further, since the "main" form that contains these sub-forms is NOT bound,
then again I don't think a update necessary fires when the sub-form looses
focus. I was under the impression that a sub-form update only occurs when
the main form is bound. And, actually, it really is the other way around.
(the main form is updated when you move to a sub-form....I not sure there is
a hard and fast rule that the sub-form updates when you loose the focus, and
to further this, I not seen any documentations that says so). So, we are all
kind of assuming that the sub-form updates when you loose the focus, but I
not really sure of that. I AM SURE the main form updates when you change
focus to a sub-form (it has to when you have link master/child fields..and
the autonumber MUST be generated. However, the reverse being a sub-form
updates when you loose the focus is not a hard and fast rule as far as I
know....).

So, we are all *assuming* that a sub-form when it losses the focus will fire
a update to the current record, and that I don't think is a safe assumption
at all. It seems to be the case *most* of the time, but if you really need
the record to update, then you likely going to have to take control of this,
and force the update in code....

I believe this is a MS bug since the screen resolution, and as a result
where the main form scroll bar is used, should not determine the firing
order
of update events, IMO.

Actually, a good number of code will run, or not run if the sub-form can be
viewed (a good example is when you have expressions, or code bound to a
controls on the form. The code for those controls only fires when the form
is actually in-view..and not out of view. The same goes for functions used
in sql. And, the call back feature of combo boxes, and listboxs is also
another case where if the screen can't be view, then the controls, and code
is NOT called. Anyway, I don't want to stray too much here, but I not really
sure that sub-forms update when focus changes is a given....

However, there is a few things you could do to force the issue.

First, does the sub-form controls on-exit event fire? If the sub-forms
on-exit event does fire, then that would be a place to force the forms
update. if the sub-form controls on-exit event fires, then just put in the
following code:

Me.nameofsubFormContorl.Form.Refresh

And, if the sub-forms control on-exit event does NOT fire, then we found out
that in addition to the sub update (which we wrongly assumed) does not fire,
we now have another event that don't fire!! Just how many events are we
discovering here? But, do test the above, I was UN-able to reproduce your
effect. However, just try the controls on-exit event....

and, to double check...you are NOT using code to move the focus
anywhere..right?

It does seem surprising that the above exit event does not fire. So, we are
taking about the user moving the focus? (as noted, code that moves the focus
will NOT fire the events).

And, if the above on-exit event of each sub-form control does not fire
(again, something I could not reproduce), then simple do the following, and
in the main form, create a public function:
:

Public Function MyUpate

me.subform1.form.Refresh
me.subform2.form.Refresh
me.subform3.form.Refresh
me.subform4.form.Refresh


Then, in each of the 4 sub-form controls on-enter event, simply go

me.myupdate

And, that would most certainly force the issue here....

So, try both of the above approaches. I seem to recall in the last thread
that the on-exit event was ALSO NOT firing. So, we are kind of running up a
large list of things not firing here!!

However, the on-enter likely has to fire, since the user has to "see" the
control on the form to click on it..and that means that the on-enter event
has to fire.....

Also, if this is a2003, then turn before TRYING ANY of the above..turn off
themed controls, as number of focus/replot issues have come up when you have
themes on...

So, try the on-exit event of the sub-form contorls, and if that don't work,
then use the on-enter...as that REALLY has to work!!!

 

[Leif]

Albert,

Another thing to keep in mind - If I run the same form on a high resolution
monitor, so that the entire form is visable, then I do get an update on each
subform as I lose focus from that subform. I don't think that is consistent
with the bulk of your note.

Your first paragraph sounds possible, but then why is that when I scroll
down, and I fully see the three subforms at the bottom (they are in a row), I
can update all three and see three "pencils" at once?

I just cannot believe that MS intended forms to work one way with a high
resolution monitor and another way with a lower resolution monitor were
scolling is required.

 

[Leif]

Sorry, left out input for your other questions.

- The exit event of the subform (from the main form) does not fire with a
change of subform focus when the main form scroll bar is used. On this MS is
consistent.

- I do use docmd.GoToControl, but only in the subform after update event,
which is not getting executed.

- the On enter event only fires the first time, and only if the subform is
visible when the form comes up. I can bounce back and forth between subforms
without the on enter and on exit events firing.

- Themed controls are off.

 

[Albert D. Kallal]

Another thing to keep in mind - If I run the same form on a high

resolution
monitor, so that the entire form is visable, then I do get an update on
each
subform as I lose focus from that subform. I don't think that is
consistent
with the bulk of your note.

Well, actually, it is if in terms of my comments about about controls not
being in view on the screen..then, yes, it would be consistent.

Your first paragraph sounds possible, but then why is that when I scroll
down, and I fully see the three subforms at the bottom (they are in a
row), I
can update all three and see three "pencils" at once?

sure...that pencil only means that the record is dirty. At this point, we
don't really have a problem? The pencil only means that a pending update
exists (ie: the record is dirty). I see nothing wrong with having 1, 2 or 5
forms open each with a pencil to represent a pending write. In fact, this
means that the un-do command would be available for all of those forms
(again, this actually might be a design decision here). And, with a un-bound
main form, this again seems entirely possible, and reasonable.

I just cannot believe that MS intended forms to work one way with a high
resolution monitor and another way with a lower resolution monitor were
scolling is required.

Well, it not really a question of resolution at here. It most certainly does
seem to be a issue of focus and if that control is IN VIEW. So, it not some
rule based on resolution, but simply that of a control being IN VIEW of the
user.

As I mentioned, a number of events and things can change, and
controls/screen objects not in view don't always get reploted, or even
updated as we *think* they should.

Looking right now, I don't see anything that says the sub-form record is
written to disk when focus changes. It *usually* does, and it will
*eventually* write, but I don't see a hard and fast rule here. If you did
not have any after update code that nags and prompts the user for a save,
then you would have NOT noticed this behaviours anyway...

As I mentioned, the updates will *eventually* occur, and further you can fix
this problem with perhaps 3-4 lines of code as I outlined anyway.

The bottom line here is that ms-access saves automatically, and you are
choosing to change this behaviours (and nag the user!!). If you do nothing
to prompt, then you system would work just fine, and you must nag the users
for each sub-form save, then the above suggested code should fix this
anway...

 

[Albert D. Kallal]

- the On enter event only fires the first time, and only if the subform

is
visible when the form comes up. I can bounce back and forth between
subforms
without the on enter and on exit events firing.

The above don't seem right. Do you have any error handling enabled here? I
would turn off ALL ERROR handling code for this test...

 

[Leif]

I guess I'm just look for consistancy here, so that I can present a
consistent experience to the user. Your right, in fact I would prefer to
have only a single update to check when the user changes records or closes
the form. From my other note it doesn't seem I can depend on the On Enter
and On Exit events, so it does appear your coding suggestion will not help.

I'm beginning to think the user will just have to live with automatic
updates. A Save button just doesn't seem to work here.

 

[Leif]

This is what I'm seeing. By turn off error handling are you requesting I
comment out all my "On Error" statements, or is there a simplier, more global
way to disable error handling?

 

[Albert D. Kallal]

This is what I'm seeing. By turn off error handling are you requesting I
comment out all my "On Error" statements, or is there a simplier, more
global
way to disable error handling?

While the focus issues I spoke about in the other post may be possible, the
lack of the on-enter event for sub-form control NOT firing points to
something VERY wrong here...

or is there a simplier, more global
way to disable error handling?

Why not simply create a blank form with two test sub-form controls. They
don't even have to be bound to ANY table. In fact, don't even bother
attached forms to the two sub-form controls. So, make this blank new form
with two sub-form controls. Do the on enter events fire for this test form?

So, try the above, it will take all of 30 seconds to test

.....create new form,
..... size details area to make it large to scroll...
.... drop in two sub-form controls...one near the top....one near the
bottom. (so you have to scroll to switch focus). You don't need any data,
you don't even have to "attach" or setup any sub-forms for those two
sub-form controls you place on the form.

In each of the two sub-forms controls "on-enter" event, place code something
like:


msgbox "on enter subform 1"

and the other one

msgbox "on enter subform 2"

Try the above...do the on enter events work when you change focus from one
sub-form control to another?

If the on-enter event of those two sub-form controls does NOT fire, then we
have something else very serious wrong here. On the other hand, if it works,
then you going to have start looking at what code, or what you are doing in
the actual production form.

Perhaps it some error trap code, or existing event code not yet completed
(such as being in a not in list code event), perhaps some timer code, or as
mentioned error handling code is running here...

Try the above test idea on a blank form....see what happens. If the on enter
events fire, then we know it something particular to your form. If the
simple test works, then make a copy of your existing mdb, and start removing
out error code, or anything else that you might think is causing the
trouble......keep deleing stuff until your on enter events for the sub-form
controls start working. (for testing, it might be a good idea to put those
msg box commands in the form during testing also).

 

[Tom Lake]

This is what I'm seeing. By turn off error handling are you requesting I
comment out all my "On Error" statements, or is there a simplier, more
global
way to disable error handling?

After the On Error Goto xxx statement,
add a temporary On Error Goto 0 to disable all error checking in
the current procedure.

Tom Lake

 

[Leif]

Hi Albert,

I tried your suggestion. I create a main form with 4 subforms. The first
subform takes most of the top area of the main form. The 3 three subforms
(at the bottom of the main form) are only visible after using the scoll bar.
This is like my problem form. This test form has no bound controls, only a
combo box in the main form and a text box in each of the subforms. This is
not like my problem form.

I added an msgbox for each of the subforms for enter and exit. All events
fired. So, there is some unknown "factor" in my problem form that is
preventing the events from being fired.

I will try to see if I can narrow down what is causing the problem.