423,851 Members | 2,762 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

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

P: 8
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!
Sep 22 '07 #1
Share this Question
Share on Google+
16 Replies


ADezii
Expert 5K+
P: 8,591
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?
Sep 22 '07 #2

P: 8
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.
Sep 22 '07 #3

P: 8
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.
Sep 22 '07 #4

ADezii
Expert 5K+
P: 8,591
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:
Expand|Select|Wrap|Line Numbers
  1. Me!![Date Field] = Date()         ' for Main Form
  2.              OR
  3. Me!<Sub-Form Control>.Form![Date Field] = Date()         'for Sub-Form
What happens if you Requery the Main Form instead of Refresh?
Sep 22 '07 #5

P: 8
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:
Expand|Select|Wrap|Line Numbers
  1. Me!![Date Field] = Date()         ' for Main Form
  2.              OR
  3. 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.
Sep 23 '07 #6

P: 8
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...".
Sep 23 '07 #7

ADezii
Expert 5K+
P: 8,591
...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:
Expand|Select|Wrap|Line Numbers
  1. Dim strMyBookmark As String
  2.  
  3. strMyBookmark = Me.Bookmark
  4. Me.Requery
  5. 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.
Sep 23 '07 #8

puppydogbuddy
Expert 100+
P: 1,923
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
Sep 23 '07 #9

P: 8
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.
Sep 23 '07 #10

puppydogbuddy
Expert 100+
P: 1,923
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.
Sep 23 '07 #11

ADezii
Expert 5K+
P: 8,591
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!
Sep 23 '07 #12

FishVal
Expert 2.5K+
P: 2,653
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.
Sep 23 '07 #13

P: 8
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!
Sep 24 '07 #14

puppydogbuddy
Expert 100+
P: 1,923
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.
Sep 24 '07 #15

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTable.*, Date() as dteCurrentDate FROM tblTable;"
  2.  
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
Sep 24 '07 #16

P: 8
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTable.*, Date() as dteCurrentDate FROM tblTable;"
  2.  
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.
Sep 24 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.