Link to home
Start Free TrialLog in
Avatar of Bob Collison
Bob CollisonFlag for Canada

asked on

Access 2016 Windows 10 New Form Continuously Recalculating

Hi Experts,

I have just completed coding a new Form and notce that it always dispplays 'Calculating...' the whole time it is Open.

It doesn't happen with other forms in the Application.

I have Decompiled / Compact Repaired it, rebooted my PC and it still does it.

I Googled the problem but the solutions are beyond my knowlege base.

It doesn't seem to actually be causing any problem.

Comments / Solutions?

Thanks,
Bob C.
Avatar of Daniel Pineault
Daniel Pineault

Do you use any code within the form?

Avatar of Bob Collison

ASKER

Hi Daniel,

Yes.  A lot.  I'm quite familiar with VBA Coding.  This application contains hundrfeds of thousands of lines.  This is the first time this isuue has happened.

Thanks,
Bob C.

Hi Bob;


Can you post the function that would display, "always dispplays 'Calculating...'", and the functions that are calling it.


Fernando

Hi Fernando,

The status displays the 'Calculating...' Status all of the time the form is displayed other thasn when a specific Event is involked.  e.g. Field GotFocus, etc.

This form is quite large with many 'Fields' and associated Events.  By Functions do you actually mean only code that is 'Called'?

I have 5 Functions that are called within the form.

Two of them (CloseFormMethod and MouseWheel are in virtually every form I have so I am ruling them out.

Two more (PrepareDocumentData and PrepareLetterData) are called within Events lauched by clucking the Button and only copy data from one Field to another.

The fifth (ApplyFieldBackgroundFortmatToAmounts) simply sets the Background Colour for three fields.  It is called from multiple Events such as the PaymentAmount Field LostFocus Event.  This is also used in a number of other forms that don't have the problem.

I can copy these 'Functions' in a file and attach them to the Question if you wish.  It is not practical to attach the whole form to the question.

Thanks,
Bob C.

Have you tried commenting out all your procs to see if the issue goes away?  Then, implement them, one by one, to identify the culprit and then troubleshoot that specific proc?

Hi Daniel,

I assume by procs you mean the 'Calls'.  That was going to be my next thing to try but it will have to wait until tomorrow.

Thanks,
Bob C.

Are you doing the programming within Access itself or are you programming with in Visual Studio using Visual Basic .Net?


If you are doing this within Access then I will not be able to help. 

Do you have any aggregated functions running in the Recordsource like Dlookup... probably this is the "issue" which is quite natural.

Hi Fernando,

I'm doing it all within Access itself.

Hi John,

I'm not sure what 'aggregate functions running in the Recordsource' are.  I do use DAO like the following code template.

'Step 002-Open Databases.
Dim DB00002 As Database
Set DB00002 = CurrentDb
'Step 003-Prepare Datasets.
Dim RSnn003 As DAO.Recordset                  '00_SELECTION_DATA

'Step n10-Extract ? Data.
'Step n11-Initialize ? Lookup Data.
Dim CmdnnTablenamen11 As String
CmdnnTablenamen11 = "SELECT * " & _
                    "FROM nnTablename " & _
                    "WHERE Fieldname = " & DblDblQuote & ParmFieldname1 & DblDblQuote
'Step n12-Open ? Dataset.
On Error Resume Next
Set RSnnTablename003 = DBnnnnn.OpenRecordset(CmdnnTablenamen11, dbOpenDynaset)
'Step n13-Check If ? Lookup Is Valid.
If RSnnTablename003!FieldName & DblDblQuote & DblDblQuote = DblDblQuote & DblDblQuote Then
   WrkFieldnamennn = "?"
Else
   WrkFieldnamennn = RSnnTablename003!FieldName
End If

STEP_920:
'Step 920-Clean-up.
'Step 921-Close Open Datasets.
RSnn003.Close
Set RSnn003 = Nothing

'Step 922-Close Open Databases.
'DB00002.Close
'Set DB00002 = Nothing
'DB10002.Close

Thanks,
Bob C.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

Bob,


  "Calculating" is displayed whenever Access is working on background tasks.


<< Two of them (CloseFormMethod and MouseWheel are in virtually every form I have so I am ruling them out.>>


 You can't rule anything out.   It may be the interaction of something you consider to be solid with something else in this specific form that is causing the problem.


 Chances are, you have something that is triggering something, which is triggering something else, which triggers the original event or some heavy processing is triggered multiple times and you are not aware that this happens.


 As Daniel suggested, as a quick test, remove all the code.  If the problem is gone, then it's the code (the problem can be caused by what you have for the control sources of contol's as well).


  If it is the code, sprinkle in some Debug.Print statements for the OnCurrent, BeforeUpdate, and AfterUpdate events.  i.e.


 Debug.Print "OnCurrent"


  and also ones in your routines:


 Debug.Print "myProcedure - Start"


  Then execute with the code window open and the debug window up.    It will be easy to spot then if you have a loop or heavy processing that is getting called repeatedly.


Jim.



Side note, why open your recordset as dbOpenDynasetwhen all you're doing is reading a value from it, instead use dbOpenSnapshot.

Hi All,

Thanks for all your comments / suggestions.

I'll have a 'go' at them and let you know the results.

Daniel:
Your comment regarding 'dbOpenDynaset' is a good one.  Being previously an HP Mainframe DBA I have often wondered about various 'Open / Locking options for DAO but have never gotten around to digging into it.  dbOpenDynaset is the only one that has been suggested in the past and that I have ever used.

Thanks,
Bob C.

<< Side note, why open your recordset as dbOpenDynaset when all you're doing is reading a value from it, instead use dbOpenSnapshot.>>


 Watch out...dbOpenSnapshot can be a real performance killer.    What your asking for is a complete snapshot of all the records at a given point in time.


 You almost always want to open a recordset as dbOpenDynaset, even when just reading.   I would use the  dbReadOnly option though.


Jim.

and you might want to use  dbOpenForwardOnly depending on your needs.


Jim.

Hi Experts,

I have the following code in all my Events / Functions so instead of commenting out the code I just placed a GoTo STEP_990 at the beginning of each Event / Function until I found the bad code.
STEP_990:
'Step 990-Exit Event.
Exit Sub

Somehow I had deleted a line from the Form_Timer Event that is in all code to highlight any messages with a colour appropriate to the message.  e.g. red for Error, etc..  When I copied in the code from my Code Template the problem was solved.

So the Solution goes to Gustav although I appreciate all of your comments / suggestions.

Thanks,
Bob C.