MS Access crashes when subform is on a tab control AND calculated control used.

[MariaTorvalds]

I have a problem where if I use a calculated control, for example =Date()) on my form, PLUS I have a subform on a tab control on the same form, then when I refresh the record (by menu command or VBA me.refresh), the DB crashes!

I've stripped the thing right down, and tested with a) no calculated control - no problem, b) no subform - no problem. c) moving the subform off the tab control to a free space on the underlying form - again, the problem disappears. I'm stumped! ... any help gratefully received!

[ADezii]

I have a problem where if I use a calculated control, for example =Date()) on my form, PLUS I have a subform on a tab control on the same form, then when I refresh the record (by menu command or VBA me.refresh), the DB crashes!

I've stripped the thing right down, and tested with a) no calculated control - no problem, b) no subform - no problem. c) moving the subform off the tab control to a free space on the underlying form - again, the problem disappears. I'm stumped! ... any help gratefully received!

Are you Refreshing the Sub-Form directly from the Main Form?

[MariaTorvalds]

The problem was originally caused by me.refresh being part of the OnCurrent event code on the main form - which contains both the tab control (with subform on one of three pages) and the control containing =Date().

I removed all the form events to track down the problem. The DB crashes either when the main form is refreshed by manually selecting "Refresh" from the standard toolbar (under Records), or when I click a command button on the main form which just does a me.refresh.

[MariaTorvalds]

Are you Refreshing the Sub-Form directly from the Main Form?

To be specific, it's the main form I'm refreshing, from the main form. The subform has a single master / child link, so it takes care of itself.
This form / subform combination has been used with no problem for some time... it looks like some kind of conflict between the form control calculation firing and the subform doing its thing, with the crash only happening when the subform is on the tab control page. Weird!
I've also imported all the objects into a new database - same problem.

[ADezii]

I have a problem where if I use a calculated control, for example =Date()) on my form, PLUS I have a subform on a tab control on the same form, then when I refresh the record (by menu command or VBA me.refresh), the DB crashes!

I've stripped the thing right down, and tested with a) no calculated control - no problem, b) no subform - no problem. c) moving the subform off the tab control to a free space on the underlying form - again, the problem disappears. I'm stumped! ... any help gratefully received!

How about removing the calculation in the Control and directly assign a Value to that Control with VBA code. You mentioned =Date() as an example, how about replacing that with VBA code as in:

Me!![Date Field] = Date()         ' for Main Form
             OR
Me!<Sub-Form Control>.Form![Date Field] = Date()         'for Sub-Form

What happens if you Requery the Main Form instead of Refresh?

[MariaTorvalds]

How about removing the calculation in the Control and directly assign a Value to that Control with VBA code. You mentioned =Date() as an example, how about replacing that with VBA code as in:

Me!![Date Field] = Date()         ' for Main Form
             OR
Me!<Sub-Form Control>.Form![Date Field] = Date()         'for Sub-Form

What happens if you Requery the Main Form instead of Refresh?

Hi ADezii - thanks for the suggestions - Requery works OK but I don't want to re-order the records, just ensure the form data is written to the underlying table/s.
I use Refresh in a lot of places, and it's been rock-solid in the past. Given that this innocent command causes a DB crash, I'd be loathed to work around it without knowing why it happens.. something else might trigger a refresh-like process, then I'd be in big trouble.
New info:- it turns out the crash also occurs when the control is unbound, and with no control source specified (so I could do as you suggested and write the value with VBA). If I bind the control with a field, the problem goes away - but I'm gonna land up with a ton of dead fields if I use that workaround, plus I still wouldn't have any idea what's going on.

Just cannot figure why the thing works fine with the subform off the tab control page, but messes up when placed on the tab control page.

To restate the problem a bit more clearly:

If a "requery" is done (by VBA or manual menu selection) when an unbound texbox control is on the main form (or tab control page on main form) AND a subform is on the tab control page, then the DB crashes.

If there is no unbound control on either the main form or tab control form, or if the subform is moved off of the tab control page and onto the underlying form, then "requery" works fine.

I should have mentioned - it's a 2000 - format DB running under 2003.

Very grateful for any further ideas. Thanks.

[MariaTorvalds]

Hi ADezii - thanks for the suggestions - Requery works OK but I don't want to re-order the records, just ensure the form data is written to the underlying table/s.
I use Refresh in a lot of places, and it's been rock-solid in the past. Given that this innocent command causes a DB crash, I'd be loathed to work around it without knowing why it happens.. something else might trigger a refresh-like process, then I'd be in big trouble.
New info:- it turns out the crash also occurs when the control is unbound, and with no control source specified (so I could do as you suggested and write the value with VBA). If I bind the control with a field, the problem goes away - but I'm gonna land up with a ton of dead fields if I use that workaround, plus I still wouldn't have any idea what's going on.

Just cannot figure why the thing works fine with the subform off the tab control page, but messes up when placed on the tab control page.

To restate the problem a bit more clearly:

If a "requery" is done (by VBA or manual menu selection) when an unbound texbox control is on the main form (or tab control page on main form) AND a subform is on the tab control page, then the DB crashes.

If there is no unbound control on either the main form or tab control form, or if the subform is moved off of the tab control page and onto the underlying form, then "requery" works fine.

I should have mentioned - it's a 2000 - format DB running under 2003.

Very grateful for any further ideas. Thanks.

...sorry..in my last reply (third from last para.) I ment: "....then "refresh" works fine...." not "...requery...".

[ADezii]

...sorry..in my last reply (third from last para.) I ment: "....then "refresh" works fine...." not "...requery...".

For now, you can use the following code as a bandaid until this issue is resolved - just substitute this code for a Refresh:

Dim strMyBookmark As String
 
strMyBookmark = Me.Bookmark
Me.Requery
Me.Bookmark = strMyBookmark

If you are really pressed for a resolution to this problem, if you are interested, and if it is feasible, you can attach the Database along with a subset of the data, but in its current Error State, to an E-Mail addressed to me. I would be more than happy to look at it for you, if you so desire. If it comes down to this just let me know, and I'll post my E-Mail address. I will not post it until I receive explicit confirmation from you. See you arround.

[puppydogbuddy]

Verify that Name AutoCorrect is turned off. This bug causes problems on refresh/requery similar to what is happening here.

see this link:

http://allenbrowne.com/bug-03.html

[MariaTorvalds]

Thanks to ADezii & puppydogbuddy for the advice. I did the object import to a new DB as per Allen Browne's recommendations - no joy.

I was stripping down the DB for ADezii to look at, and the thing started working.

The full story is now:

If the subform is placed on the tab control, AND there's an unbound textbox control on either the main form or the tab control AND the subform records contain a calculated field AND the subform has an "Order by" entry (still with me? :-) ..... then when I hit the main form with a "refresh", the DB goes into what I think Microsoft lovingly call an "infinite recursion" - (I found that wondrous title in Microsoft Article ID. 288374). The description of the symptoms seems to fit.
If I remove ANY of the elements listed above, everything works fine.
Since the easiest thing to change was the "Order by" entry for the subform, (which is now blank), I'm just left to figure out how to sort the continuous records in the subform by some other means.... preferably one which doesn't crash the DB!

The subform calculated control is just giving the product of two other controls.... nothing fancy - and the DB has been working fine with this subform for years.

I'd be grateful if you wiser folk would let me know if you consider this to be a bug, or if I'm just doing things the wrong way. Either way, I recon a few others out there may have been caught by something similar?

Many thanks for the advice thus-far.

[puppydogbuddy]

Thanks to ADezii & puppydogbuddy for the advice. I did the object import to a new DB as per Allen Browne's recommendations - no joy.

I was stripping down the DB for ADezii to look at, and the thing started working.

The full story is now:

If the subform is placed on the tab control, AND there's an unbound textbox control on either the main form or the tab control AND the subform records contain a calculated field AND the subform has an "Order by" entry (still with me? :-) ..... then when I hit the main form with a "refresh", the DB goes into what I think Microsoft lovingly call an "infinite recursion" - (I found that wondrous title in Microsoft Article ID. 288374). The description of the symptoms seems to fit.
If I remove ANY of the elements listed above, everything works fine.
Since the easiest thing to change was the "Order by" entry for the subform, (which is now blank), I'm just left to figure out how to sort the continuous records in the subform by some other means.... preferably one which doesn't crash the DB!

The subform calculated control is just giving the product of two other controls.... nothing fancy - and the DB has been working fine with this subform for years.

I'd be grateful if you wiser folk would let me know if you consider this to be a bug, or if I'm just doing things the wrong way. Either way, I recon a few others out there may have been caught by something similar?

Many thanks for the advice thus-far.

It is generally recommended for performance reasons that any fields used for sorting should be indexed. If the fields you were Ordering By are not indexed, maybe that could slow the system down enough to show symptoms similar to what you describe as infinite recursion. Try indexing the fields in your Order By, and test.

[ADezii]

Thanks to ADezii & puppydogbuddy for the advice. I did the object import to a new DB as per Allen Browne's recommendations - no joy.

I was stripping down the DB for ADezii to look at, and the thing started working.

The full story is now:

If the subform is placed on the tab control, AND there's an unbound textbox control on either the main form or the tab control AND the subform records contain a calculated field AND the subform has an "Order by" entry (still with me? :-) ..... then when I hit the main form with a "refresh", the DB goes into what I think Microsoft lovingly call an "infinite recursion" - (I found that wondrous title in Microsoft Article ID. 288374). The description of the symptoms seems to fit.
If I remove ANY of the elements listed above, everything works fine.
Since the easiest thing to change was the "Order by" entry for the subform, (which is now blank), I'm just left to figure out how to sort the continuous records in the subform by some other means.... preferably one which doesn't crash the DB!

The subform calculated control is just giving the product of two other controls.... nothing fancy - and the DB has been working fine with this subform for years.

I'd be grateful if you wiser folk would let me know if you consider this to be a bug, or if I'm just doing things the wrong way. Either way, I recon a few others out there may have been caught by something similar?

Many thanks for the advice thus-far.

I'm just left to figure out how to sort the continuous records in the subform by some other means.... preferably one which doesn't crash the DB!

How about changing the Record Source of the Sub-Form to reflect the new Sort Order?

BTW, do you have any idea of what the odds are of all 4 events that you describe happening concurrently and causing the crash? They must be astronomical!

[FishVal]

Since the easiest thing to change was the "Order by" entry for the subform, (which is now blank),

Hi, Maria.

This actually doesn't eliminte problem as long as clicking Sort button on toolbar or context menu (if you don't take special measures to prevent user from doing this) writes to Form.Orderby property.

If you invoke Refresh method just to save current record, then you may use
DoCmd.RunCommand acCmdSaveRecord
instead.

[MariaTorvalds]

Hi folks:

Taking your responses in order:

Puppydogbuddy: The field being sorted on in the subform is indexed.
The calculation on the subform records is a simple “Long Integer x Double” sum, so do you think the problem is more likely to be caused by the refresh process colliding with the sort process (which I guess gets started by the “refresh”) than by any single complex process?

ADezii: I’ll stick a query between the table and the subform to try and accomplish what I need sort - wise. Thanks for the suggestion.
Regarding the odds, I’m not too sure about this... Subforms on tab controls... very common. Unbound controls somewhere - also very common. “order by” being used on the subform - again, fairly common I guess? Using refresh... again common.
If it really is the case that few others have seen this problem, I’d have to agree with you. The combination must be unusual OR my system is dodgy in some other way.

FishVal: Thanks for the warning. I guess I’d have to disable the “refresh” menu item also. Is there anything I need to watch out for re. the difference between “refresh” and the “DoCmd.RunCommand acCmdSaveRecord “ command? I’ve used refresh in a number of different modules / procedures, so I’m a little worried about any hidden impact on changing over.

The stripped - down database which demonstrates this problem is very simple... I’d be happy to email anyone both the working and non- working editions (only difference being the subform “Order By” entry is empty on the working DB). Just open the frmMain and click the command button “Refresh Test”. Works fine on the good DB, but crashes the bad.

Thanks again for the help!

[puppydogbuddy]

so do you think the problem is more likely to be caused by the refresh process colliding with the sort process (which I guess gets started by the “refresh”) than by any single complex process?

Maria,
Not the sort process per se, but something related to the output of the fields used in the sort process that Access does not like. For example, do any of the rows of the sort field(s) return a null? if so, try testing using the nz function on those fields or exclude nulls.

[FishVal]

FishVal: Thanks for the warning. I guess I’d have to disable the “refresh” menu item also. Is there anything I need to watch out for re. the difference between “refresh” and the “DoCmd.RunCommand acCmdSaveRecord “ command? I’ve used refresh in a number of different modules / procedures, so I’m a little worried about any hidden impact on changing over.

Hi, Maria.

Form.Refresh (Plz, refer to Access help for Form.Refresh method features) method is quite different from “DoCmd.RunCommand acCmdSaveRecord“. The latter just saves the current record (and doesn't crash Access in this case). If you invoke Form.Refresh method to save current record only it will be suitable to use “DoCmd.RunCommand acCmdSaveRecord“ instead, otherwise ADezii's suggestion (post#8) will be preferrable.

BTW, You may bind the unbound control to RecordSource. I mean if you use the unbound control for displaying current date, then you may just retrieve it to RecordSource

SELECT tblTable.*, Date() as dteCurrentDate FROM tblTable;"
 

and set ControlSource of the unbound control to [dteCurrentDate].

The stripped - down database which demonstrates this problem is very simple... I’d be happy to email anyone both the working and non- working editions (only difference being the subform “Order By” entry is empty on the working DB). Just open the frmMain and click the command button “Refresh Test”. Works fine on the good DB, but crashes the bad.

No need. I've easily reproduced the same crash on my system.
Access 2003, Win XP SP2

Regards,
Fish

[MariaTorvalds]

Hi, Maria.

Form.Refresh (Plz, refer to Access help for Form.Refresh method features) method is quite different from “DoCmd.RunCommand acCmdSaveRecord“. The latter just saves the current record (and doesn't crash Access in this case). If you invoke Form.Refresh method to save current record only it will be suitable to use “DoCmd.RunCommand acCmdSaveRecord“ instead, otherwise ADezii's suggestion (post#8) will be preferrable.

BTW, You may bind the unbound control to RecordSource. I mean if you use the unbound control for displaying current date, then you may just retrieve it to RecordSource

SELECT tblTable.*, Date() as dteCurrentDate FROM tblTable;"
 

and set ControlSource of the unbound control to [dteCurrentDate].




No need. I've easily reproduced the same crash on my system.
Access 2003, Win XP SP2

Regards,
Fish

Thanks very much for the advice Fish - and for reproducing the problem. Hopefully that eliminates the possibility of my system being dodgy.

Just for my sanity.. can you relate this behaviour to any other bug you've seen? Does it represent "unreasonable behaviour" of the DB, or am I doing something "wrong" or just unusual? - For Access to crash in this way on a very simple DB, when using a common action.... I just don't get it!

If this behaviour does represent a bug, is there a clear way of describing it, so others may be warned? Maybe the mighty MS would have something to say?

As a "Newbie" I'm not sure if this thread should be closed because I have a workaround, or left open to allow discussion of what is actually happening to cause this crash? Please advise.

Thanks.

[ShutterBug]

I know this post is quite old, but I am posting my solution for anyone else searching for a fix for this issue. I scoured the Internet and did not find a reliable solution and it seems that a number of folks have had this problem but did not really find a viable fix that should work in all cases. I hope that my solution is just that. I certainly took my share of Advil while finding what worked for me and hopefully this will spare others the some heartburn.

OK, so here is my scenario before creating the fix. I have a form with 2 subforms. The parent form has 2 combo boxes with their datasources set to fields in the tables that underly the queries which are the datasource for the 2 corresponding subforms. I hope that is clear. When an item is selected or deselected from the combo boxes the controls' Change event would run Me.Refresh / MeRequery, both of which would randomly crash Access. This is to refresh the records shown in the subforms. At times it worked fine, other times not so much = spontaneous Access crash with no errors.

I also have code running in the Parent form's Current, Load , BeforeUpdate and AfterUpdate events. So this likely causes a delay in the Parent and subforms' record being commited to the underlying tables. Thus, at times, the subforms are requering while the commit transaction is still open on their underlying tables. At least that is my theory.

The issue seems to be that the parent forms' record is not completely saved when the subform is called to requery....sometimes....exactly whenever.

The fix:

In the combo boxes on the parent form Change event:

Private Sub MyComboBox1_Change
 
'Save the current record of the parent form first   
 
     DoCmd.RunCommand acCmdSaveRecord
 
'Give Access time to finish saving the current record
 
    DoEvents
 
'Requery the corresponding subform ONLY (not the parent form as that will trigger the Load and Current events again, and not Refresh as 'that will not update the subform's displayed records.)
 
    Me!SubFrom1.Form.Requery
 
End Sub

For good measure I also set the parent form and subforms' "Wait for Post Processing" property to Yes, but not sure if that has any effect.

Peace, love and happy coding!