Cannot set Hourglass cursor during Access Form refresh

SimonKravis
SimonKravis used Ask the Experts™
on
I am trying to set the hourglass icon in an Access 2003 form while it runs a long query with a new recordsource with the code snippet below. The icon remains as an arrow all the time the code executes. If I set a breakpoint at line 250, the hourglass icon appears after I continue execution from that point and disappears after executing line 350.
240         lblStatus.Caption = "Executing Regexp filter.."
 250        Screen.MousePointer = 11 ' set to hourglass
 260         DoEvents
....
 
270           Me.RecordSource = vSource(0) & " where " & sFilter & " and " & vSource(1)
 
340       Form.Refresh
345        lblStatus.Caption = ""
350       Screen.MousePointer = 0 ' return to arrow

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this:
DoCmd.Hourglass True
and of course to set it back:
DoCmd.Hourglass False

Author

Commented:
Same results - the hourglass icon does not appear unless set a breakpoint in the code after the hourglass icon has been set
Looking at the code, I bet the pointer changes to hourglass and then back again too fast for you to see it. Try remarking out the line
350       Screen.MousePointer = 0 ' return to arrow
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

BTW:
Form.Refresh
is not needed. The form requeries whenever the record source is changed,

Author

Commented:
Thanks for the tip about the redundant Refresh. The query takes about 15 seconds to run, based on changes to lblStatus so it isn't a case of the hourglass appearing momentarily. The puzzle is why it appears if I set a breakpoint in the code.
changing the record source is asynchronous meaning it processes while the code continues.

To stop the code until the record source change completes, create a do loop looking you the new data:
Do
Loop Until SomeControl = Something

or

Dim ControlWas  

ControlWas  = SomeControl

Me.RecordSource = vSource(0) & " where " & sFilter & " and " & vSource(1)

Do
Loop While  SomeControl = ControlWas  

Author

Commented:
The lblStatus label control doesn't have it's value cleared ( line 340 ) until 15 seconds after the Recordsource setting, so I don't think that execution is asynchronous. Also, when single stepping using F8 from a breakpoint set at line 270, there is a long pause before line 370 is highlighted.
Commented:
Hi simon,
I believe that the DoEvents is causing the system to continue processing.  So as far as the code is concerened you have requested that the code continues onto the next set of statements.  removing the Do events will cause the code to act like it is being processed line by line.  or in break mode

DoEvents is used to force the application to yield it's use of the processor for one cycle (usually 50ms), it's commonly used to allow other events in your application to fire while you're performing a resource intensive operation.


Regards

Eoin
A workaround is to pop up a message box before setting the hourglass icon: see code below. It  seems as though moving the focus to another window allows the hourglass icon to be set, and the code runs as expected. It might be possible to fully automate by using SendMessage to programmatically click on the Message Box OK button but this will do for now.

DoEvents at line 335 is required to get lblStatus to update

Thanhs Eoin and TheNelson for stimulating me into finding this.


220       lblStatus.Caption = "Executing Regexp filter.."
225       If MsgBox("Regexp search may take some time. Continue?", vbYesNo) = vbNo Then Exit Sub
230       Screen.MousePointer = 11
...
310           Me.RecordSource = vSource(0) & " where " & sFilter & " Order By " & vSource(1)
...
330      lblStatus.Caption = "Setting Caption.."
335       DoEvents
340       Call SetCaption2(" where " & sFilter, sSearch)
350       lblStatus.Caption = ""
360       Screen.MousePointer = 0

Open in new window

Commented:
to get the labgel to display you could use the following

330      lblStatus.Caption = "Setting Caption.."
335      lblStatus.Refresh

Will also cause the label to refresh. with out the need for the DoEvents.  and this should also remove the need for the msgbox

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial