473,881 Members | 1,673 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

8 New Member
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
17 18382
puppydogbuddy
1,923 Recognized Expert Top Contributor
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
8,834 Recognized Expert Expert
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
2,653 Recognized Expert Specialist
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.RunComman d acCmdSaveRecord
instead.
Sep 23 '07 #13
MariaTorvalds
8 New Member
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.RunComma nd 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
1,923 Recognized Expert Top Contributor
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
2,653 Recognized Expert Specialist
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.RunComma nd 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.RunComma nd 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.RunComma nd 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
MariaTorvalds
8 New Member
Hi, Maria.

Form.Refresh (Plz, refer to Access help for Form.Refresh method features) method is quite different from “DoCmd.RunComma nd 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.RunComma nd 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 "unreasonab le 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
ShutterBug
1 New Member
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....some times....exactl y whenever.

The fix:

In the combo boxes on the parent form Change event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub MyComboBox1_Change
  2.  
  3. 'Save the current record of the parent form first   
  4.  
  5.      DoCmd.RunCommand acCmdSaveRecord
  6.  
  7. 'Give Access time to finish saving the current record
  8.  
  9.     DoEvents
  10.  
  11. '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.)
  12.  
  13.     Me!SubFrom1.Form.Requery
  14.  
  15. 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!
Apr 27 '22 #18

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

Similar topics

1
3741
by: Simon | last post by:
Hello All I am trying to build a couple of forms in Access 2000. Whilst I have one form which has all of the contact details in it also generates (using autonumber) a unique id for the customer. I have also created a button to open up another form which looks at a second table. I want the ID automatically populated into the form (tables are designed one to many) so that correspondence can be added. At the moment access states that...
7
1691
by: Julia Baresch | last post by:
Hi everyone, My company recently upgraded from Office 97 to Office XP. As those who've read my other posts know I have a database with 3 nested subforms Main form-->First Subform-->Second Subform First subform is a subform of Main Form, and Second Subform is a subform of First Subform.
3
1673
by: Andrew Wrigley | last post by:
Hi I have what seems a corruption of an mdb that is not solved by the decompile command line trick. The problem appears when clicking on a subform control (check box) that has a event handler associated with it. Everything works ok, until I click on the check box, whereupon Access fails with an application error. It appears that execution never
2
343
by: Claude | last post by:
Let' say we have an application for a production facility running 24/7 broken into 3 shifts 6-2,2-10,10-6 each production report date contains 3 shifts as above in each shift there can be from one to several different product runs (the average is about 4) but it could be more or less - it's not defined or definite for many reasons Then, for each run there is a production report of product, good pieces,
2
1579
by: Steve Jorgensen | last post by:
I have recently had reason to worke on 3 different Access/VBA applications of different sizes on Windows XP machines. In all cases, I've had frequent instances of code changes being discarded, and having to re-write the changes. has anyone else seen this problem? It was pretty embarassing for a while delivering code that worked before I closed Access, then failed upon deployment. I finally developed the habit of copying my code into...
0
1877
by: jphelan | last post by:
I have a subform that works fine until you import it into a new database when it crashes if you try to open it in either disign or form view. The form, "Attendees_Subform" in my application was pattern after the same form used in the MS template, "Event Management.mdb" application that is downloaded from the MS Template Gallery on their website. I narrowed down the problem to the Control Source in the, "Attendees_Subform using the...
3
4584
by: Shannan Casteel via AccessMonster.com | last post by:
I have a subform for listing parts. It has fields including: ClaimID, ITEM, NET PRICE, LIST PRICE, Quantity, Supplier, and a calculated field called Part Total. The subform is based on a query. The Part Total field is calculated as (=.*.). Then there is a hidden text box on my subform that calculates the sum of all the Part Totals for each claim. There is another field on my main form set equal to this field on the subform. My...
2
4039
by: Lenin Torres | last post by:
Hi everybody I have an Union Query that works fine. I used this query as the RecordSource for a Form. That Form is used as a subform in another form. Everything works fine, except for the "Filter by form" feature. When the user tries to use Filter by form a messagebox is displayed: "There are too many controls in this form to perform a filter by form", after that,when the user exit the Filter by Form mode, Access crash, displaying a...
6
5891
by: dhowell | last post by:
I have a "form" and "subform" where I would like a calculated control on the form which sums the values of a datasheet column of the subform. (datasheet on subform may have a variable number of entries) I can't seem to get the calculated control on main form to do what I want...... Is this possible, if so, how? Help!
0
9926
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11095
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10716
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10812
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10399
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7108
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5780
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3223
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.