Hourglass mousepointer help required..

[itchy]

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

 

[everblue]

try using this ...


at the start of the code, use

DoCmd.Hourglass True


and at the end of the function calls, use

DoCmd.Hourglass False

It will keep the hourglass till the end of the process.


Thanx

 

[itchy]

Hi everblue,
I already tried that, it doesn't work either. The problem seems to be that the hourglass process is running but only gets run aftera ll the other processes have completed (which kind of defeats the purpose!). If I remove the Screen.MousePointer = 0, the mouse pointer will change to the hourglass but only at the end, after my screen has been updated. By the way, I am running Access 2000 on Windows 2000 Pro.
Any more ideas?

 

[Alexandre]

That's funny 'cos everblue's suggestion always worked well for me... I am wondering if taking out the repaint or putting it at the end wouldn't help?

Also, can you give us an idea about what kind of code is behind the functions you call?

 

[itchy]

Hi Alexandre,
I have removed the repaint and rearranged the functions a little in hopes that it might help. 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
__________________________________________

As for what each function does, well 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]

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

 

[rockman]

An old post, but thanks for leaving the answer Itchy...

This is an awesome forum!

Jeff

 

[jeremypaule]

very old post, but it helped me alot. instead of trying to use a form saying that my query is loading, I simply used the hourglass cmd. sweet!