"Calculating..." when form opened

[jonnyboy]

Hello,

I have an Access application with a number of forms. On opening one particular form, the application will sometimes appear to get stuck in a loop with 'Calculating...' in the status bar for some time. This is a problem when using the database on a server, since it seems to be running the same query over and over again.

There is one form in particular I have a particular problem with. Both its form and subform are opened using WHERE filter criteria, using a string filter on Standards.refStd.

Standards are linked to Narratives in a many-to-many relationship, i.e. there is a link table called Link-NarraStand that has the primary key of Standards in one column and Narratives in the other.

In the main form, there are a couple of DLOOKUPs in text boxes on the form.

MAIN FORM SQL:

SELECT KLOEs.RefKLOE,
       KLOEs.HeadingsRef,
       KLOEs.DescKLOE,
       OfficersKLOE.DescOfficer AS KLOEOfficer,
       KLOEs.Element,
       Themes.RefTh,
       Themes.DescTheme,
       Questions.RefQ,
       Questions.DescQ,
       Questions.Timescale,
       Standards.RefStd,
       Standards.StdExcellent,
       Standards.StdFair,
       Standards.SA,
       Standards.[L4-DateStart],
       Standards.[L4-DateEnd],
       Standards.[L4-DateActual],
       Standards.L4Team,
       SAlevels.DescSA,
       SAlevels.DescPlan,
       Standards.Officer,
       Standards.OfficerAudit,
       Standards.L4weight
 
FROM ((KLOEs LEFT JOIN Officers AS OfficersKLOE ON KLOEs.Officer = OfficersKLOE.RefOfficer) LEFT JOIN Themes ON KLOEs.RefKLOE = Themes.KLOE) LEFT JOIN (Questions LEFT JOIN (SAlevels RIGHT JOIN Standards ON SAlevels.RefSA = Standards.SA) ON Questions.RefQ = Standards.Question) ON Themes.RefTh = Questions.Theme
 
ORDER BY KLOEs.RefKLOE,
         Themes.RefTh,
         Questions.RefQ,
         Standards.RefStd;

SUBFORM SQL:

SELECT [Link-NarraStand].*,
       qL5NarrativesW.L5order,
       qL5NarrativesW.Level,
       qL5NarrativesW.Status,
       qL5NarrativesW.TextNarrative,
       qL5NarrativesW.TextNarrative2,
       qL5NarrativesW.DateStart,
       qL5NarrativesW.DateEnd,
       qL5NarrativesW.DateComplete,
       qL5NarrativesW.Progress,
       qL5NarrativesW.Officer,
       qL5NarrativesW.Archive,
       qL5NarrativesW.EvidenceLink,
       qL5NarrativesW.WeightL5,
       qL5NarrativesW.L5wgtprog,
       qL5NarrativesW.ModifiedOfficerL5,
       qL5NarrativesW.DateModifiedL5,
       qL5NarrativesW.RefManager,
       qL5NarrativesW.NameManager,
       qL5NarrativesW.DisOfficer,
       qL5NarrativesW.DisDate
 
FROM [Link-NarraStand] LEFT JOIN qL5NarrativesW ON [Link-NarraStand].Narrative = qL5NarrativesW.RefNarrative
 
ORDER BY qL5NarrativesW.L5order,
         qL5NarrativesW.DateStart,
         qL5NarrativesW.DateEnd;

[NeoPa]

@jonnyboy
What causes you to think the delay is any more than the SQL running normally?

[jonnyboy]

Thanks for replying, NeoPa.
When the form opens, it displays the correct records in both the main form and the subform almost immediately.
However, the form seems to be refreshing itself over and again for quite a while, with "Calculating..." in the status bar, which impedes data entry and slows the application right down.
If I select the main form and hold F5, there is a similar effect, although there's nothing in the code that's asking the forms to refresh.

[FishVal]

@jonnyboy
Access doesn't need something asking it to refresh form / table grid / query grid nor it listens to something asking it not to do it.

[Stewart Ross]

Hi. I think you are making an incorrect asumption that the problem relates to form refresh. I have experienced very similar issues with a form where I used an unbound textbox to display the entry date of the last record entered. This used a DLookup of a simple Max query returning the latest incident date within the set of 130,000 records. Across the network its performance was very, very slow (not surprising as Access is not client-server based). Because the unbound textbox update was in progress the 'Calculating...' message was on screen for over 30 seconds, and the whole application was more or less unusable in that time.

As a quick check, copy the form involved and on that test copy remove the textboxes with the DLookups, or substitute a specific value for test purposes. See if that modified copy of the form opens without the Calculating... message staying on. If it does, it is the network traffic involved in the asynchronous query launched by the DLookup that is causing the slowdown in performance.

I should make it clear that the use of the domain aggregate function Dlookup is not the issue here; my slowdown was as a direct result of the network traffic resulting from running the query underlying the Dlookup, which was evident whenever I ran the query by itself across the network. When I ran the same query on a non-network copy of the DB there were no performance issues at all, and hence the unbound textbox update did not cause delays when testing.

In my case I could not resolve the problem by changing the query concerned, no matter what I tried. In the end I removed the unbound textbox altogether...

-Stewart

[NeoPa]

Another possibility might be that, for quite large recordsets, Access will first return a subset of records to be getting along with, before it then goes off to retrieve the rest.

Try this with a large query that has a sort order different from any of its indexes. You will notice the same behaviour (The last record number won't display until all the records have been returned). It seems feasible to me that this explains your extended delay after the forms show up as expected.

[jonnyboy]

Hello again NeoPa, Stewart.

Thanks again for replying so promptly. You're making a really valuable contribution.

I'll check out the indexes situation, and whether I have indexes on those fields I'm looking to sort on.

Jon