Access won't stop "Calculating . . . " till you jiggle the mouse over a certain control

We're experiencing this fault on a customer's site.

On one workstation (Access2K3, happens to have a touchscreen), on one of the key forms, the user reported that it was hanging or freezing with "Calculating ..." showing in the bottom left hand corner.

We took a look and found this to be true, except we noticed that you could "release" the screen again by jiggling t开发者_C百科he mouse around a bit. Then narrowed the jiggling down to one particular Textbox. So long as you move the mouse over this textbox you can release the screen and everything works as normal.

The textbox shows some readonly financials data. Naturally the textbox is blank till after being released, thereafter it shows the correct data.

There is no MouseMove, MouseDown or MouseUp event for this control, nor for any other control. The controlsource of the Textbox is admittedly not trivial, it consists of an IF and also uses a VBA function. However similar types of complex controlsources are repeated elsewhere in the app and don't cause a problem, and even this particular control causes no problems on other machines.

Has anyone else experienced this?

Also for debugging purposes - is there any way to find out what exactly is happening step by step when Access reports that it is "Calculating ..." for minutes on end?

---

I've had similar problems when using conditional formatting. When I've run into the problem I've solved it using two different approaches:

1. Ditch the conditional formatting and apply the format manually through code (ie, via the Form_Current event, After_Update event of a dependent control, etc.). Obviously, this won't work if you are trying to differentiate controls on a continuous bound form. In those cases, I go with my other option

2. Manually add and delete the conditional format itself. I'm not entirely sure why this works but it does. I'll give you a quick example function that shows the technique:

.

Private Const DefaultHLColor As Long = 10092543 'RGB(255, 255, 153); Light Yellow

'---------------------------------------------------------------------------------------
' Procedure : HighlightRow
' DateTime  : 2/22/2008 3:27
' Author    : Mike
' Purpose   : Highlight the detail section of a continuous form.
' Usage     : 1) Add a textbox bound to a unique field (preferrably the primary key)
'                to a form set to continuous view.
'             2) Set Enabled = No, Locked = Yes, BackColor = {Detail Section BackColor},
'                    BackStyle = Normal, SpecialEffect = Flat, ForeColor = BackColor
'             3) Expand the textbox to fill the entire detail section, Send to Back.
'             4) Move it down one pixel ([Ctrl] + [{down arrow key}])
'             5) Add the following to the form's OnCurrent event:
'                =HighlightRow([{TextBoxName}])
' Notes     : We could simply Refresh the form in the OnCurrent event, but Access
'             (2002, at least) does not have a rock solid implementation of conditional
'             formatting.  The problem with Refreshing the form occurs when we select
'             a record (which gets highlighted), then we scroll the form so the
'             highlighted record is no longer visible, then select a new record, and
'             scroll back to the previous record to see that, sadly, it is still
'             highlighted.
'---------------------------------------------------------------------------------------
'
Function HighlightRow(Ctl As TextBox, Optional HLColor As Long = DefaultHLColor) 'vv
    On Error GoTo Err_HighlightRow

    Application.Echo False
    With Ctl
        .FormatConditions.Delete
        If Ctl.Parent.CurrentRecord <> 0 Then
            If Not IsNull(.Value) And Not IsEmpty(.Value) Then
                If IsNumeric(.Value) Then
                    .FormatConditions.Add acFieldValue, acEqual, .Value
                Else
                    .FormatConditions.Add acFieldValue, acEqual, """" & .Value & """"
                End If
                .FormatConditions(0).BackColor = HLColor
                .FormatConditions(0).ForeColor = HLColor
                .FormatConditions(0).Enabled = False
            End If
        End If
    End With

Exit_HighlightRow:
    Application.Echo True
    Exit Function
Err_HighlightRow:
    LogError Err.Number, Err.Description, "HighlightRow"
    Resume Exit_HighlightRow
End Function

---

I know this question has been answered, but I found a strange work-around that might help people.

On my large report in Access 2007 the conditional formatting was causing it to get stuck when opening with just "Calculating..." in the status bar, similar to the problem above. However I discovered it occurs only when trying to open the report from selecting it in the sidebar.

I created a form to act as a faux-toolbar with a button + embedded macro triggering the OpenReport command. When opened this way the report opens instantly! It's a strange thing indeed but it allowed me to keep the conditional formatting.

Hope this might help others, just don't forget when you're in design mode that if you try and switch to report mode it will hang. Close it and use the faux-toolbar button.

EDIT: Further from above, I've realised that this works because the button opens it in Print Preview mode. It hangs in Report mode.

---

I have exactly this problem (Access 2010). I have a form and a subform. The subform has conditional formatting to highlight the selected record, but I have formatting on every field. On the form there is a [edit] button which does a subform.allowedits = true

When I press the button and don't move the mouse over the subform, no problem. When I press the edit-button and instantly move the mouse over a conditionally formatted field on the subform, it keeps blinking/refreshing untill I move the mouse away from the subform or to a non cond.formatted field on the subform.

The above seems to work, but the disadvantage of this method (using one control to display the selected state) is that when you set the color of the control to black and the other controls have black text, you can't read the text.

Of course a simple solution is to not make the background black but another "problem" is that when you focus a textbox the background is not visible anymore through the textbox and that doesn't look good.

When I only format the one control it works. When I use the above code to apply to format all controls (extra control not needed anymore) the problem is back.

---

This appears to be new in Access 2013. I reapplied all of my conditional formatting and the problem was resolved.

---

Create à link with the following target: yourdatabase.accdb /decompile Then lauch your database from this link. This operation will clear your vba code from old compilations.

---

You can set the status bar text to anything you like. Try using this line at the end of the processing task

Application.Echo True “Your Text Goes Here”

Dont know why it would be clearing on that mouse over!