BUG: Multiple Form_Current events in ADP projects with subforms

[Pedhoncdoc]

To all ADP form gurus:

I have an extremely bad Undocumented Bug in Access XP ADPs:

Whenever there is a subform on a parent form, the parent form sends multiple
( 3 - 30 ) Form_Current Events to every subform of the ParentForm, whenever a
new record is inserted in the ParentForm.

Most people will never notice this (they don't use Form_Current event code),
but when you have any Form_Current event code, it will frequently crash
Access after multiple inserts involving ParentForm and Subform record insert
cycles. This happens even with the most simple forms in all of my test
projects. (I don't know what happens in MDBs.) Apparently, the aberrant
events lead to memory leaks or stack overflows that eventually crash Access.
All of my procedures are logged and have error-logging error handlers, so I
know that the problem does not occur in any of my code. When I exam a
procedure stack trace during the aberrant Form_Current events, the error
arises in "Non-Basic" code, or there is no parent procedure shown that is
producing the Form_Current event.

The problem occurs regardless of the form/subform RecordSource (SQL vs SP vs
parameterized SQL or SP), and is not solved by changing FetchDefaults,
UniqueTable, or ResyncCommand, etc.

The problem never occurs on the first insert, always on the second or third
insert into the Parent form.

The problem will occur in the middle of the ParentForm Form_BeforeInsert
event if that event is in use. Removing all BeforeInsert code does not
remove the problem, though.

----------------
My only workaround is to remove the subforms' SourceObjects, or to remove
the OnCurrent Event capture in subforms by setting
SubformControl.OnCurrent="", and resetting it in the AfterInsert and Undo
events. I'm not sure if the latter approach really solves the problem though.
----------------

This is really bad, especially when you have a lot of bound subforms.

Can anyone else confirm this? Any ideas for a solution? How does one
submit this to Microsoft - there used to be an email, but I don't have it now.

 

[Brian]

To all ADP form gurus:

I have an extremely bad Undocumented Bug in Access XP ADPs:

Whenever there is a subform on a parent form, the parent form sends multiple
( 3 - 30 ) Form_Current Events to every subform of the ParentForm, whenever a
new record is inserted in the ParentForm.

Most people will never notice this (they don't use Form_Current event code),
but when you have any Form_Current event code, it will frequently crash
Access after multiple inserts involving ParentForm and Subform record insert
cycles. This happens even with the most simple forms in all of my test
projects. (I don't know what happens in MDBs.) Apparently, the aberrant
events lead to memory leaks or stack overflows that eventually crash Access.
All of my procedures are logged and have error-logging error handlers, so I
know that the problem does not occur in any of my code. When I exam a
procedure stack trace during the aberrant Form_Current events, the error
arises in "Non-Basic" code, or there is no parent procedure shown that is
producing the Form_Current event.

The problem occurs regardless of the form/subform RecordSource (SQL vs SP vs
parameterized SQL or SP), and is not solved by changing FetchDefaults,
UniqueTable, or ResyncCommand, etc.

The problem never occurs on the first insert, always on the second or third
insert into the Parent form.

The problem will occur in the middle of the ParentForm Form_BeforeInsert
event if that event is in use. Removing all BeforeInsert code does not
remove the problem, though.

----------------
My only workaround is to remove the subforms' SourceObjects, or to remove
the OnCurrent Event capture in subforms by setting
SubformControl.OnCurrent="", and resetting it in the AfterInsert and Undo
events. I'm not sure if the latter approach really solves the problem though.
----------------

This is really bad, especially when you have a lot of bound subforms.

Can anyone else confirm this? Any ideas for a solution? How does one
submit this to Microsoft - there used to be an email, but I don't have it

now.

Nope, doesn't do it for me.

 

[Dirk Goldgar]

To all ADP form gurus:

I have an extremely bad Undocumented Bug in Access XP ADPs:

Whenever there is a subform on a parent form, the parent form sends
multiple ( 3 - 30 ) Form_Current Events to every subform of the
ParentForm, whenever a new record is inserted in the ParentForm.

Most people will never notice this (they don't use Form_Current event
code), but when you have any Form_Current event code, it will
frequently crash Access after multiple inserts involving ParentForm
and Subform record insert cycles. This happens even with the most
simple forms in all of my test projects. (I don't know what happens
in MDBs.) Apparently, the aberrant events lead to memory leaks or
stack overflows that eventually crash Access. All of my procedures
are logged and have error-logging error handlers, so I know that the
problem does not occur in any of my code. When I exam a procedure
stack trace during the aberrant Form_Current events, the error arises
in "Non-Basic" code, or there is no parent procedure shown that is
producing the Form_Current event.

The problem occurs regardless of the form/subform RecordSource (SQL
vs SP vs parameterized SQL or SP), and is not solved by changing
FetchDefaults, UniqueTable, or ResyncCommand, etc.

The problem never occurs on the first insert, always on the second or
third insert into the Parent form.

The problem will occur in the middle of the ParentForm
Form_BeforeInsert event if that event is in use. Removing all
BeforeInsert code does not remove the problem, though.

----------------
My only workaround is to remove the subforms' SourceObjects, or to
remove
the OnCurrent Event capture in subforms by setting
SubformControl.OnCurrent="", and resetting it in the AfterInsert and
Undo events. I'm not sure if the latter approach really solves the
problem though. ----------------

This is really bad, especially when you have a lot of bound subforms.

Can anyone else confirm this? Any ideas for a solution? How does one
submit this to Microsoft - there used to be an email, but I don't
have it now.

I haven't work on ADPs much at all, but I've never reproduced this
problem with an MDB. It's quite common for the Current event of a
subform to fire more than once, but not normally as many times as you
report. Is conditional formatting involved? Are there many calculated
controls on the main form that reference the subform? Is there code
firing in the Current event that is causing the actual crash due to
being called multiple times?

 

[Pedhoncdoc]

Hi again,

Thanks for responding on this. I wish I could figure out how to get this
submitted as an "official" bug to MS.

Anyway, the problem occurs on my machine, and on another machine as well.
It recurrs even with completely different tables and form/subform
combinations. So it is reproducible at least for me. To test it you must
have debug code in the Form_Current events of all the linked subforms. And
both parent and subforms must have records in them. The problem only appears
after the first insert.

I have noticed other undocumented ADP event irregularities as well. (For
example, the order of the BeforeDeleteConfirm and Delete events are reversed
in ADPs compared to MDBs).

I think that ADPs are very different than MDBs, and it would be nice is we
could get some documentation of what Access is doing with events and with the
SQL database when working with forms. Especially info about the "secret" SQL
statements that it sends to the server with every data operation.

Rich

 

[Pedhoncdoc]

Hi Dirk,

Thanks for responding:

No calculated fields, no conditional formatting, and my test case involves
no code in the subform Current event other than debug.print.

In my real project, there is a lot of code in Form_Current, but the problem
occurs even with no extra code in Form_Current.

Also, the cursor bar (i.e. the vertical text cursor) "vibrates" rapidly
during all the processing for about a second or two.

Rich

 

[Pedhoncdoc]

Hi Brian,

Thanks for responding. Did you actually test it with debug code in the
subform's Current event? Did you try it with an ADP? How many subform
Current events do you see after the third insert on the parent form?

Thanks,
Rich

 

[Daniel]

Hi,
I have an application that does this all the time, It crashes frequently and
my customer is very disappointed by this.

It crashes especially after a click on a checkbox (that's bounded to a bit
colmn)
and causes a lot of refresh to the sub form.

Don't know what to do with this,

By the way - It's a form inside a form inside a form
(Perent->SubParent->SubFrm)

 

[Brian]

Hi Brian,

Thanks for responding. Did you actually test it with debug code in the
subform's Current event? Did you try it with an ADP? How many subform
Current events do you see after the third insert on the parent form?

Thanks,
Rich

Yes, Debug.Print statement in the subform's Current event.

Yes, in an ADP.

Current event triggered twice, as normal and as expected.

 

[Brian]

Hi again,

Thanks for responding on this. I wish I could figure out how to get this
submitted as an "official" bug to MS.

Anyway, the problem occurs on my machine, and on another machine as well.
It recurrs even with completely different tables and form/subform
combinations. So it is reproducible at least for me. To test it you must
have debug code in the Form_Current events of all the linked subforms. And
both parent and subforms must have records in them. The problem only appears
after the first insert.

I have noticed other undocumented ADP event irregularities as well. (For
example, the order of the BeforeDeleteConfirm and Delete events are reversed
in ADPs compared to MDBs).

I think that ADPs are very different than MDBs, and it would be nice is we
could get some documentation of what Access is doing with events and with the
SQL database when working with forms. Especially info about the "secret" SQL
statements that it sends to the server with every data operation.

Rich

How can the subform have records in it if a new record is being added to the
parent form (unless the subform is not linked)?

 

[Pedhoncdoc]

Sorry, I meant that the RecordSource for the subform (and Parent Form) is not
empty. I only tested this with RecordSources that are not empty. You are
right, when doing an insert on the Parent Form, the subform shows no records.

Rich

 

[Pedhoncdoc]

Hi Daniel,

I feel your pain! There does not seem to be any way to track down these
bizarre Access Event errors. For me, it's just trial and error, and lots and
lots of debugging code in the Event handlers. If you can find out the events
that cause the problem, then you have a good shot and findng some kind of
work-around.

(Don't get me wrong - I love Access, but it drives me crazy sometimes.)

Rich

 

[Pedhoncdoc]

Hi Brian,

I really appreciate that you tried this out! You give me hope that there
may be a solution within my reach.

I will start up a new project in a brand new SQL Server database and see
what happens.

Rich

 

[Brian]

Hi Daniel,

I feel your pain! There does not seem to be any way to track down these
bizarre Access Event errors. For me, it's just trial and error, and lots and
lots of debugging code in the Event handlers. If you can find out the events
that cause the problem, then you have a good shot and findng some kind of
work-around.

(Don't get me wrong - I love Access, but it drives me crazy sometimes.)

Rich

The Current event is just about the most annoying and unpredictable of all -
but you can't ignore it because it's one of the most useful! Anyway, if I
were seeing this in one of my developments, I would be wondering if there
was an event sink knocking around somewhere that I had forgotten about. But
then, I love event sinks and use them extensively, but most people don't
seem to use them at all.

 

[Brian]

Hi Brian,

I really appreciate that you tried this out! You give me hope that there
may be a solution within my reach.

I will start up a new project in a brand new SQL Server database and see
what happens.

Rich

FWIW, I created a new ADP using the Northwind SQL Server database, and I
used the form wizard to create a form bound to Orders, and another one bound
to Order Details, and then I (obviously!) added the latter to the former as
a subform.

 

[Pedhoncdoc]

Brian, you are reading my mind! I use Event sinks all over the place. In
fact I use them for form events and command button events extensively. Now I
am looking to see that all of my class instances have been closed. I am
thinking that if I have dangling pointers to to event-handling classes, that
might be the problem.

On the other hand, I only see the problem for Form_Current, and not for any
other events. And only when I insert in the Parent.

Hmm....

Rich

 

[Brian]

Brian, you are reading my mind! I use Event sinks all over the place. In
fact I use them for form events and command button events extensively. Now I
am looking to see that all of my class instances have been closed. I am
thinking that if I have dangling pointers to to event-handling classes, that
might be the problem.

On the other hand, I only see the problem for Form_Current, and not for any
other events. And only when I insert in the Parent.

Hmm....

Rich

But, of course, if you could reproduce the problem in a new project which
definitely has NO event sinks (or indeed any code at all apart from a
Debug.Print statement) then you would be back to questioning Access' sanity
rather than your own!

Or, if you can't reproduce it thus, you will at least have narrowed it down
to some feature of your specific project. It's what you said about things
vibrating rapidly that makes me wonder if there is some code running
somewhere, say in an event sink.

 

[Pedhoncdoc]

OK, here's some more data:

The problem only occurs when I am using my global event handler for forms.
This is just a class that encapsulates a bunch of common code used for form
data events. Even with no code (except for debug.print) in the event
handlers, it seems that just shadowing the form events with this class (just
one instance) causes the Current event to fire multiple times after each
Insert on the parent form. It seems that no other event is affected! When I
remove the class, the events behave normally.

So, my work-around is to disconnect (set SubForm.OnCurrent = "") in the
Form_BeforeInsert event, and then re-connect (set SubForm.OnCurrent = "[Event
Procedure]") in Undo and AfterInsert events. I just hope that I'm not
missing something....

Thanks Brian,

Rich

 

[Dirk Goldgar]

I think Brian may be on to what's causing your particular problem.

I have noticed other undocumented ADP event irregularities as well.
(For example, the order of the BeforeDeleteConfirm and Delete events
are reversed in ADPs compared to MDBs).

Are you familiar with

http://support.microsoft.com/default.aspx?scid=kb;EN-US;234866
ACC2000: Order of Form Delete Events Differs in ADPs and MDBs

?

 

[Brian]

OK, here's some more data:

The problem only occurs when I am using my global event handler for forms.
This is just a class that encapsulates a bunch of common code used for form
data events. Even with no code (except for debug.print) in the event
handlers, it seems that just shadowing the form events with this class (just
one instance) causes the Current event to fire multiple times after each
Insert on the parent form. It seems that no other event is affected! When I
remove the class, the events behave normally.

So, my work-around is to disconnect (set SubForm.OnCurrent = "") in the
Form_BeforeInsert event, and then re-connect (set SubForm.OnCurrent = "[Event
Procedure]") in Undo and AfterInsert events. I just hope that I'm not
missing something....

Thanks Brian,

Rich

Oh dear. I've added an event sink to my little test rig, and it still
behaves itself!