This stumped an MVP. Do you know the answer?

[Leif]

Earilier I placed a question the saving behavior of Access. No one knew the
answer. I've since learned some additional information. Its really weird,
perhaps you can help.

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 active or not. In other words, if the screen resolution is
low the scroll bar for the main form is active. 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).

So I know the problem, I know what causes, however I don't know the
solution. I believe this would be classified as an Access bug, but I need a
work around.

Thanks for your help.

 

[Albert D. Kallal]

you still not outlined a problem?

The sub-form record will not update unless you save it, or move to another
record. I would presume that setting focus to the main form would also
likely trigger a save (and, I suspect that the scroll bar is just fooling
you..since if the user touches the scroll bar, tent he faces changes back to
the main form...and a update occurs). This is like some focus issue, due to
you using/touching the main form.

However, if you navigate or close the main form, the record will be
save...it is not likely the before update event WILL NOT fire...it most
certainly will ALWAYS fire before the record is updated.

Since the before update vent will always fire...I don't see problem here?

You might expand on what you problem is, but when those records get saved,
the before update will fire.

The only issue here seems to be WHEN you want this to occur, or perhaps
just let ms-access do it when needs to!! (the latter is a better solution,
and your code + designs should take this into account).

If you are saying that the before update vent never occurs, then we have a
bug. but, simply changing the focus and using the scroll bar (or not) like
would perhaps change the focus from the sub-form control to the main
form..and trigger a update.

Perhaps we should focus (pun intended) on when you need/want this update to
occur. I am reasonable sure that if you close the form, that the update will
occur.

 

[Leif]

Albert,

Thanks for your reply.

I concur that the update events will eventually fire, regardless of the
status of the scroll bar.

My problem is that I use the before update event to ask the user if he wants
to save the record update. When the scroll bar is not active the event fires
on a change of focus to another subform.

However, if the scroll bar is active then the before update event does not
occur on a change of focus for the subform. In the case where the user
updates all the subforms, then changes the record or closes the form, all
four update prompt message occur one after the other.

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

I would like to have the user only response once to a change. If I know
that it happens on a record change or form close I could code it to only ask
once. However, due to difference in event firing behavior, depending on the
scroll bar being active or not, makes the situtation more complex.

Regards,
Leif

 

[Albert D. Kallal]

Hum, it sounds a bit messy....

(I would just remove all of the save prompts....as they drive users crazy
anyway).

However, perhaps you can try placing a me.Refresh in the sub-form control
on-exit event

This is the sub-form control were you find the link/child setting..NOT the
actual sub-form..but, the control...

I would try the above...

 

[Leif]

The on exit events of the subform controls on the main form do not fire when
the scroll bar is active.

 

[Albert D. Kallal]

The on exit events of the subform controls on the main form do not fire
when
the scroll bar is active.

You mean even when a user changes the focus to another of the sub-forms? I
just tested this, and the events are firing for me...

The fact of a scroll bar on the form makes no difference for me...and the on
exit event is firing for me......

 

[Leif]

Hi Albert,

Yes, when a user changes focus between subforms and exit event does not
fire. Likewise, if an update is made the before update does not fire on a
change in focus to another subform. This happens when the verical scroll bar
is active (the whole form cannot appear on the screen).

I've not had this problem before, but I usually don't use a scroll bar, I
usually use tabs. However, the user prefers a scroll bar (more like IE).

In your first post you mentioned it may not be the scroll bar, but using it.
That seems to be true. If I reduce the window size, on a high res screen,
so that the scroll bar is active, but the subform is still visible, the
before update event fires. The problem seems to arise when I use the scroll
bar to move to the bottom of the main form. The arrangement of my subforms
is that I have a large subform at the top, with three smaller subforms along
the bottom of the main form.

If you like I could send you the database.

Regards,
Leif

 

[Leif]

Well, I guess this thread is going to die without an answer (again). I hope
at least Microsoft is on the ball and picks up the fact that they have this
bug in Access.

 

[Douglas J. Steele]

In reality, it's unlikely Microsoft is monitoring these newsgroups.

You might want to submit a request at
http://www.microsoft.com/office/com...ffice&pt=3a4e9862-cdce-4bdc-8664-91038e3eb1e9

 

[Leif]

Thanks, I've posted the suggestion/bug.

In reality, it's unlikely Microsoft is monitoring these newsgroups.

You might want to submit a request at
http://www.microsoft.com/office/com...ffice&pt=3a4e9862-cdce-4bdc-8664-91038e3eb1e9