Hourglass mousepointer help required.... 1

[itchyII]

Hello all,
The following is a whole bunch of functions that get called on an 'onclick' event of a button on my form.

______________________________________________
Private Sub Image83_Click()
Dim Text1, Text2, Text3 As String
Dim condition1, condition2 As String
Dim where1, where2, where3 As String

Screen.MousePointer = 11

'check which checkbox is selected and get appropriate query name
qry = CheckSearch()

'unhide form controls
Call unhideSearch(True)

Me.Repaint

'fill search combo boxes with appropriate field names
Call fieldList(qry)

'query all
Call buildQuery(qry)

'set subform
Call setSub

'unhide subform
Me!sub.Visible = True

Screen.MousePointer = 0

End Sub
______________________________________________

The processing time for these functions to finish is anywhere from 5 to 15 seconds. This is still long enough for the user to jump the gun on their next process without waiting for the first finish, which could cause problems. I am trying to find a way to have the hourglass displayed until all of these functions have been completed but the problem is that Access changes the mousepointer to an hourglass and then immediately changes it back without waiting for all the code in between to finish running. I tried moving the Screen.MousePointer, and placing it inside of the last function called but it still doesn't work. I tried adding DoEvents in a few places but that didn't work either. Does anyone know a way of achieving this?

Itchy

 

[Jucius]

You're gonna kick youself because the solution is simple ...



Controlling the hourglass is very easy in VBA:

DoCmd.Hourglass True

'insert many long calculations here

DoCmd.Hourglass False

Also, make sure you enter

DoCmd.Hourglass False

into any error handling sections of the function, otherwise if there is an error during the calculations, the hourglass will remain afterwards.

 

[itchyII]

Hi Jucius, I tried that method already. It gives the same thing. I switched to the Screen.MousePointer method because many people had suggested it in other posts. I tried removing the Screen.MousePointer=0 (or DoCmd.Hourglass Flase) just to see what happens and it actually only changes the mousepointer after all of the other functions have finished running (even though it is the first thing on the list!) Any other ideas?

Itchy

 

[ClydeDoggie]

I believe you have to set/clear the pointer, using either method, inside each function. Once you move out of a function/sub the new function/sub takes control of the system interface stuff. You will see the pointer flicker as it exits/enters functions, but it will be an hourglass for virtually all the time.

 

[itchyII]

Hi ClydeDoggie
I tried what you suggested but it still didn't work. I put the DoCmd.Hourglass True in each of the functions I am calling and left my original code the same. Did I do something wrong?

Itchy

 

[Jucius]

Perhaps one (built-in) function you are calling disables the hourglass.

 

[ClydeDoggie]

Itchy,
I am baffled, I just made a small test case with three functions that ran for about 5 sec. apiece and both commands, docmd and screen.mousepointer, worked fine. Jucius may have a point, I would use some breakpoints or msgbox's to see if the pointer is changing to an hourglass then being forced back by one of the functions.

 

[itchyII]

Hi ClydeDoggie,
I tried it again and it only seems to change the pointer to the hourglass after all of the functions have been carried out. I discovered this by removing the DoCmd.Hourglass False statement just to see if the initial statements weren't working. Well, they are, but there seems to be a delayed reaction! I've tried them one by one (meaning that I went into each function and set the statement to true one by one, without ever setting the statement to false, in order to see when the mouse pointer would change) and in all instances, it was only changed after all functions from the 'onclick' have been completed. Help! This is really getting to me!!

Itchy

 

[VBslammer]

Your best bet is to turn off screen painting while the processes are running. There are many system-level functions that can override your mouse pointer, even an activeX control that has a custom mouse pointer setting will override Access mouse pointers. Usually, Modal forms will reset the cursor to normal so you can click buttons in the event of a problem. I noticed in your code that you have a Form.Repaint call which can also affect screen objects.

I would try to include a 'DoCmd.Echo False' command at the beginning of the procedure and a 'DoCmd.Echo True' in your exit block, but make sure you have error handling set up or you might end up with no way to turn the screen painting back on.

Sub ManyProcs()
On Error Goto ErrHandler
DoCmd.Echo False
DoCmd.Hourglass True

' do stuff
' do more stuff
' do more stuff
' do more stuff

ExitHere:
DoCmd.Echo True
DoCmd.Hourglass False
Exit Sub
ErrHandler:
Debug.Print Err, Err.Description
Resume ExitHere
End Sub

If you set it up this way there's no need to repaint anything because none of your changes will be visible to the user until the processes are complete, and hopefully your mouse pointer won't get overridden.

A good idea is to set up an autokeys macro with a key combination that will restore screen painting in case you need it.

You can also try to include 'DoEvents' at the end of each procedure, sometimes this allows the system to catch-up on delayed processes.

When posting this kind of problem, please inlcude the operating system involved and the Access version...

Good luck.

VBSlammer

 

[itchyII]

Hi VBSlammer,
Thanks for the tips. Sorry for not mentioning it earlier but I'm running Access 2000 and Windows 2000 Pro. I tried adding your lines of code and commenting out the repaint:

Private Sub Image83_Click()

On Error GoTo ErrHandler
DoCmd.Echo False
DoCmd.Hourglass True

Dim Text1, Text2, Text3 As String
Dim condition1, condition2 As String
Dim where1, where2, where3 As String

'check which checkbox is selected and get appropriate query name
qry = CheckSearch()

'unhide form controls
Call unhideSearch(True)

'Me.Repaint

'fill search combo boxes with appropriate field names
Call fieldList(qry)

'query all
Call buildQuery(qry)

'set subform
Call setSub

'unhide subform
Me!sub.Visible = True

ExitHere:
DoCmd.Echo True
DoCmd.Hourglass False
Exit Sub
ErrHandler:
Debug.Print Err, Err.Description
Resume ExitHere

End Sub

Unfortunately, I still get no hourglass during the process. I can try adding the DoEvents to each process (I assume that this would go at the end of each function). Should I also repeat the DoCmd.Hourglass True at the beginning of each function? I really don't know anymore! Its as if Access is running the hourglass command in a seperate thread follwoing all the other threads. So it is probably running the commands but it is running them one after the other. I checked all the functions being called in the procedure and none of them have a repaint. Any more ideas?

Itchy

 

[ClydeDoggie]

Itchy, put the Docmd.Hourglass False before the Docmd.Echo False. The Echo command stops the screen from changing at all until it is turned back on, so you have to set the pointer first.

 

[itchyII]

Hi ClydeDoggie,
Did you mean the DoCmd.Hourglass True before the DoCmd.Echo False? If so, I tried it and it didn't do the trick. This is really getting to me! I can't see any reason that I can't do this. Does anyone know why it seems to run the DoCmd.Hourglass True process last? Is there a hierarchy or precedence commands?

Itchy

 

[ClydeDoggie]

Itchy,
Yeah, DoCmd.Hourglass True was what I meant. Try DoCmd.Hourglass True
DoEvents
DoCmd.Echo False


As to why this is such a problem, I have no clue. I have run into this problem in VB apps I have worked on and had to just keep putting in Screen.Mousepointer=vbHourglass all over the place until I got it to stay that way for as long as I wanted. I just assume that it's as vbSlammer says - There are all kinds of system functions that reset the mousepointer. The funny (not 'HaHa' funny) part is, if you don't have proper error handling it is a pain in the neck to clear the damn hourglass!

 

[itchyII]

Hi ClydeDoggie,
I have tried your latest suggestion as well as rearranging my functions a little (I moved all the function that unhides some controls to the bottom of the sub, but all to no avail. Here is where I'm at:
___________________________________
Private Sub Image83_Click()
On Error GoTo ErrHandler

Dim Text1, Text2, Text3 As String
Dim condition1, condition2 As String
Dim where1, where2, where3 As String

DoCmd.Hourglass True
DoEvents
DoCmd.Echo False

'check which checkbox is selected and get appropriate query name
qry = CheckSearch()

'fill search combo boxes with appropriate field names
Call fieldList(qry)

'query all
Call buildQuery(qry)

'set subform
Call setSub

'unhide form controls
Call unhideSearch(True)

ExitHere:
DoCmd.Echo True
DoCmd.Hourglass False
Exit Sub
ErrHandler:
Debug.Print Err, Err.Description
Resume ExitHere

End Sub
__________________________________________

Here is a brief description of what each function called does (maybe you will see a potential problem in one of them)Here is a summary:

CheckSearch(): is a big long if statement that verifies which checkbox on my form is checked (if none is checked it displays an error message) and returns a text string that contains the name of a query.

fieldList(): opens the query recordset (as named by CheckSearch()), and runs through the fields creating a long text string of all the field names and captions and then populates three listboxes on the form with these names.

buildQuery(): declares three text arrays which are then filled with information from the form. These arrays are then passed to another function that prepares an sql statement which is passed back to the buildQuery function. the sql statement is then passed on one of three other functions which dynamically creates a query based on the sql statement. (at the point that this sub is run, most of the information on the form is null because this is just the initial setup of the form, this function is used again later once the user has entered some information on the form)

setSub(): an if statement that sets the sourceObject for a subform on the form.

unhideSearch(): depending on the Boolean value passed to it, either hides or unhides a list on controls on the form

So that’s it in a nutshell. I have added error handling and the DoCmd.Hourglass True statement to each function. If you see any potential problem spots with any of the functions, please let me know and I can post the code (I didn't post it now because its alot of code!)
Thanks for all your help!

Itchy

 

[itchyII]

I got it!!!! Finally! On a tip from another unrelated thread, I moved my mousepointer command to the On Mouse Down event instead of leaving it in the On Click event with the other functions! It works beautifully! Thank you for all your help!!

Itchy