Form calculating troubles

[Ufalufa]

I have a form that I list merchandise with prices. This automatically figures
tax and and quanity I enter to give me a item total. At the bottom of the
page it gives me a total of all the merchandise listed.

This has been working great for the past year but suddenly the totals at the
base of the page will not calculate. At the bottom left hand corner it say
Calculating..... but never seems to finish.

The weird thing is that if I minimize access, then bring it up again the
total magically appears. The totals also appear when I print out the page.

Refreshing the form doesnt seem to do anything. If any one has any
suggestions please let me know.....

Thanks, Mike

 

[Tom Wickerath]

Hi Mike,

First, make a back-up copy of your database. Have you tried some of the
standard fixes, such as:

1.) Compact and repair database
2.) Using the undocumented /decompile option
3.) Importing all objects into a new database

You might have a corrupt form (or forms, if you have a subform as well). Try
using the following from the Immediate Window:

Application.SaveAsText acForm, "NameOfForm", "FileName"

where "NameOfForm" is the name of the form enclosed in quotes, and
"FileName" is the name to save the form's definition to (for example,
"C:\Temp\MyForm.txt"). After saving the form(s) to a text file, delete the
source forms from the database, do a compact and repair, and then reload the
forms using Application.LoadFromText.

You might have a corrupt index. Try deleting any indexes used in the tables
in question, compact the database, and re-establish the indexes.

These are my guesses for the moment. Can't hurt to try, but do make that
backup copy first.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ufalufa]

Tom,

Thanks for the info. I gave it all a try but no luck. I am downloading any
updates I can find and still screwing around with it a bit.

It does seem that a few areas are slow to refresh or react. I did decide to
do some simple forms and see how they reacted but got the same results. I
did notice that as I was creating new forms they did not appear on the list
of forms on the left taskbar. Like the original problem with the
calcualtions I had to minimize the program then bring it up agian to see the
newly created form...odd. While I play with it I still have access 2003
which runs my database great...just not as pretty!

Thanks agian for your reply,
Mike

 

[Tom Wickerath]

Hi Mike,

It does seem that a few areas are slow to refresh or react.

In different databases, or just this database?

I still have access 2003 which runs my database great...just not as pretty!

Are you using Access 2007 when experiencing these problems? If so, I didn't
see that mentioned before. (And for the record, I *much* prefer the
appearance of Access 2003 to the newer 2007).

...as I was creating new forms they did not appear on the list
of forms on the left taskbar.

Are you describing the Navigation Pane in Access 2007?

Just yesterday, Dec. 11, 2007, Microsoft released service pack 1 for Office
2007. If indeed you are running Access 2007, have you downloaded and
installed this update?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ufalufa]

I dont have many databases going but they all seem to be doing the same
thing. I just finshed a simplified version and its doing the same thing with
the Sum calculation.

Yes, I am using Access 2007. I have been using it for the past 4 months
with out this new problem.

Yes, I do mean the Navigation Pane.

Maybe 2007 is having trouble with my forms. This is what I have (Sorry in
advance for the description, I'm not very technical)

I have a Customer Form which gives the normal info (name street....blah blah).

On that form is a subform with Work Order Information (when the job is, what
needs to be done ...etc) This is linked to what ever Customer is shown.

On the Work order subform I have another subform. In this I list the items
I use, Labor ect. This is the area where all the calculations are taking
place. they are all working great except for the sum total in the footer of
this subform. It wont calculate until I minimize the program and that
maximize it agian.

Thanks agian for the info. Any ideas let me know. I am going to reload
Access 2003, so work wont be a problem, but I will still play with 2007 until
I figure it out. The more diggin I do the more I am learning, so this is
kind of fun... I think.

Thanks,
Mike

 

[Tom Wickerath]

Hi Mike,

I would try initiating a repair of your installation of Office 2007. I'm not
at a PC that currently has this version available, so right now I cannot
provide guidance on how to initial a repair. In Access 2003, one could click
on Help > Detect and Repair...

I suspect that in Access 2007, one would start by clicking on the Office
button thingy, and then perhaps Access Options... or look up Detect and
Repair in the 2007 Help file.

Did you install Service Pack 1 for Office 2007? If not, do so.

Have you cleaned out your temporary files folder lately? You might have so
many files in there, that it's just kind of jamming up everything. Have you
initiated a disk defragmentation lately? If not, do so, but clean the temp.
folder out first.

Finally, consider that this might be a video driver problem. Did you by
chance do anything with your video card and/or drivers about the same time
that you noticed this issue in Access? Try installing the latest video driver
for your system, to see if that helps. Often times, you can find the latest
drivers on the web site for the manufacturer of your computer, or on the web
site for the video card manufacturer, if you purchased an after-market card.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ufalufa]

Tim,

Thank you. I think you are leading me down the right track. I am going to
load this on another computer just as a quick check and do all the things you
rec. I will let you know how it goes.

Mike

 

[Bill]

Did you get this issue solved? I've had this same problem before over the
years. Some PCs are affected and others are not. In the past I've solved
this by changing WinXPP Performance setting to "Optimize for Performance".
Currently I have a couple PCs that display "Calculating" in lower left and
never seem to retrieve sum from a subform footer. Other PCs in the exact
same form/subform are fine. Only way so far that works is Minimize/Restore
Access and once restored, the values are there in the main form. However, if
they advance to next record, same problem and the Minimize/Restore trick gets
kinda cumbersome ;-) I've tweaked virtual memory on these PCs, too. Do you
really think it is a video driver issue? Thansk in advance.

 

[Tom Wickerath]

Hi Bill,

Do you really think it is a video driver issue?

I don't know, but I think it is certainly worth considering as a possibility.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Bill]

Got this solved and the solution is weird enough to share. The 2 PCs that
experienced this behavior are the slowest in the office. All the other PCs
would show "Calculating..." in the lower left for a moment and then back to
"Form View". These 2 would continue to show "Calculating..." and they would
never display totals on a main form grabbed from the footer of a sub-form.
Other PCs, no problem. As I said, in the past I'd solve this by changing
Windows performance setting to "Optimize for Performance". In this
particular MDB, a main "menu" form would open via AutoExec and then users
would open a sub-menu form and finally this main/subform. So, at this point
all 3 forms open. The main menu form had a couple text boxes sourced from a
DCount and DLookup respectively. An on-timer event would fire every 30
seconds, do a requery to these 2 controls and act accordingly -- like a
trigger. I changed the source for these unbound controls to blank and moved
the DCount and DLookup to the Event Procedure of the ontimer event to set
their values, etc. This tweak in performance apparently was enough to make
the main/subform behave as it should: "Calculating..." shows only briefly and
the totals are refreshed from the subform footer and "Form View" is then
displayed.

So, it's a Performance issue obviously, but the solution is to
tweak/optimize where ever it is possible. Hope this helps somebody in the
future.

 

[Allen]

I am adding to this thread because this same problem has brought a critical
set of my databases to their knees. I am very concerned about understanding
the source of this problem and fixing it.

This is the problem: In Access 2007, when I open a any form, query, or
table, the status bar reports "Calculating. . ." forever. On forms containing
subforms, totals are not working (controls whose data source is
"=Sum(recordset field)". It may be very significant to note that Access 2007
is consuming 50% of CPU power even when IDLE!! This is obnoxious!

On a specially constructed test case, the subform total does not work when
there are 12 or more records in the subform. I can force the calculation of
the larger totals if I use the subform's navigation control to go to the last
record in the subform; or if I minimize the entire Access Application window
and maximize it again. I notice that when the subform total is failing with
12 or more records, the navigation control does not show the total.

Whenever I force success by either of the two manual interventions, the
navigation control will show the total number of records in the subform. It
is as if some background operation is preventing the normal, automatic
iteration over the larger sets of records in the subform.

When I do perform the manual intervention and the totals are calculated
properly, the status bar reads "Form View". Interestingly, the CPU is still
50% occupied. If I minimize the application, I notice that upon maximizing
the window and advancing to the next record in the MAIN form, the status bar
goes back to "Calculating. . ." and sometimes the records in the subform are
totaled and sometimes they are not! There is a random facet to this problem!

I should say that I compacted and repaired the database, created a new
database, insured no "Name Autocorrect", and imported the older database into
the fresh new one. The problem persists in the new database.

I use 4 different databases to run a small business. They are
interconnected. When I migrated to a new laptop a few weeks ago, I also moved
from MS Office 2003 to the newest Office 2007, SP1.

The new laptop is a Lenovo T61p Thinkpad. Centrino Dual Core processor T7500
(2.2 GHz). 2 GB memory.

The old machine is an IBM T30 Thinkpad. Single processor, 1 GHz with 1 GB
RAM. Access 2003 consumes only 3% of CPU while at rest on that machine. And
over the course of 4 years, I never experienced such an intractable problem
with a Microsoft product.

I must un-install Office 2007, put Office 2003 on the new machine and test
the performance. It is very hard to believe that this is a hardware or driver
problem. All other programs are performing very nicely on the new machine.

Could it be that a new virus has invaded my Access databases! I have run AVG
and Spybot; no problems found.

I am quite interested in any further thoughts or testimonials about this
problem with Office/Access 2007.

 

[Jeanette Cunningham]

Hi,
I don't have any solutions. This sounds a bit similar to the calculating bug
that happens in Access 2003 where on some forms the calculation goes on and
on and never completes. I have seen it on continuous forms where the
calculated fields don't populate with data and just keep flashing on the
screen.

Jeanette Cunningham