Hourglass wont display while code executes

[Penny]

Hi All,

In a search form I have code that builds a select statement using a query
taking values from form controls as parameters and then adding a few more
criteria generated by passing other values in controls through specialized
sub routines. The code then sets the record source of a sub form to the
select statement. Works a treat. This whole process can take up to 30
seconds over the network and I want to visually let the user know that the
search is executing. I dont display the status bar so text or a progress bar
is out of the question there. I've tried displaying an hourglass or custom
built form during the process(as implemented in the code below) but the
hourglass 'displays' only for a tiny fraction of a second at the end of the
process(no good to me) or the 'frmSearchIndicator' displays only its border
and none of the detail section.

Any ideas?

Regards,

Penny.

.....................................

Sub lblSearch_Click()

On Error GoTo HandleErrors
' Create a WHERE clause using search criteria entered by user and
' set RecordSource property of frmSearchCandidatesGenericSUBResults.

Dim strBaseSQL As String
Dim strCriteria As String
Dim strRecordSource As String
Dim intArgCount As Integer
Dim Tmp As Variant

DoCmd.Hourglass True
'DoCmd.openForm "frmSearchIndicator"

' Initialize argument count
intArgCount = 0

strBaseSQL = "SELECT * FROM qrySearchCandidates WHERE "
strCriteria = ""

' Use values entered in text boxes in form to create more criteria for
WHERE Clause.
AddToWhere1 [BasisOfEmployment], "[WorkingHoursSought]", strCriteria,
intArgCount
AddToWhere2 [TypingTestAccuracy], "[TypingTestAccuracy]", strCriteria,
intArgCount
AddToWhere4 [WillingToTemp], "[WillingToTemp]", strCriteria, intArgCount
AddToWhere8 [OverallGrade], "[OverallGrade]", strCriteria, intArgCount

' If no criteria specified, return all records.
If strCriteria = "" Then
strCriteria = "True"
End If

' Create SELECT statement.
strRecordSource = strBaseSQL & strCriteria

' Set RecordSource property of frmCandidateSearchSubResults.
Me![Child1].Form.RecordSource = strRecordSource

' If no records match criteria, display message.
' Move focus to Clear button.
If Me![Child1].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no records that match the criteria you have
entered.", 48, "No Records Found"
'Me.lblClear_Click
Else
' Enable control in detail section.
Me![Child1].Enabled = True
End If

'DoCmd.Close acForm, "frmSearchIndicator"
DoCmd.Hourglass False

ExitHere:
Exit Sub

HandleErrors:
MsgBox Err.Number & ": " & Err.Description, , "lblSearch_Click"
Resume ExitHere

End Sub


......................................

 

[Sandra Daigle]

Try inserting a DoEvents statement right after you set the hourglass on:

DoCmd.Hourglass True
Doevents

I don't recall ever having trouble getting the hourglass to show but
doevents might help.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi All,

In a search form I have code that builds a select statement using a
query taking values from form controls as parameters and then adding
a few more criteria generated by passing other values in controls
through specialized sub routines. The code then sets the record
source of a sub form to the select statement. Works a treat. This
whole process can take up to 30 seconds over the network and I want
to visually let the user know that the search is executing. I dont
display the status bar so text or a progress bar is out of the
question there. I've tried displaying an hourglass or custom built
form during the process(as implemented in the code below) but the
hourglass 'displays' only for a tiny fraction of a second at the end
of the process(no good to me) or the 'frmSearchIndicator' displays
only its border and none of the detail section.
Any ideas?

Regards,

Penny.

....................................

Sub lblSearch_Click()

On Error GoTo HandleErrors
' Create a WHERE clause using search criteria entered by user and
' set RecordSource property of
frmSearchCandidatesGenericSUBResults.
Dim strBaseSQL As String
Dim strCriteria As String
Dim strRecordSource As String
Dim intArgCount As Integer
Dim Tmp As Variant

DoCmd.Hourglass True
'DoCmd.openForm "frmSearchIndicator"

' Initialize argument count
intArgCount = 0

strBaseSQL = "SELECT * FROM qrySearchCandidates WHERE "
strCriteria = ""

' Use values entered in text boxes in form to create more criteria
for WHERE Clause.
AddToWhere1 [BasisOfEmployment], "[WorkingHoursSought]",
strCriteria, intArgCount
AddToWhere2 [TypingTestAccuracy], "[TypingTestAccuracy]",
strCriteria, intArgCount
AddToWhere4 [WillingToTemp], "[WillingToTemp]", strCriteria,
intArgCount AddToWhere8 [OverallGrade], "[OverallGrade]",
strCriteria, intArgCount
' If no criteria specified, return all records.
If strCriteria = "" Then
strCriteria = "True"
End If

' Create SELECT statement.
strRecordSource = strBaseSQL & strCriteria

' Set RecordSource property of frmCandidateSearchSubResults.
Me![Child1].Form.RecordSource = strRecordSource

' If no records match criteria, display message.
' Move focus to Clear button.
If Me![Child1].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no records that match the criteria you have
entered.", 48, "No Records Found"
'Me.lblClear_Click
Else
' Enable control in detail section.
Me![Child1].Enabled = True
End If

'DoCmd.Close acForm, "frmSearchIndicator"
DoCmd.Hourglass False

ExitHere:
Exit Sub

HandleErrors:
MsgBox Err.Number & ": " & Err.Description, , "lblSearch_Click"
Resume ExitHere

End Sub


.....................................

 

[Wayne Morgan]

The problem is that once you assign the RecordSource to the subform, the
code continues. It doesn't wait for the subform to retrieve the records.
There may be a built-in indication in the status bar to indicate that the
form is retrieving the records, if you displayed it.

One problem I do see is that you don't clear the hourglass prior to your
MsgBox, so if the box did pop-up , the user wouldn't be able to click the Ok
button. Also, you need to clear the hourglass in your error handler in case
you wind up there.

To make the hourglass stay until the form is done retrieve records, you may
be able to get a count of the number of records the SQL should return prior
to assigning SQL to the RecordSource, then create a loop that will run until
the RecordCount of the subform matches this count. I don't know if this
would work, but even if it would, you would have to go to the back-end
twice, once to get the count and once to fill the form. Although, getting
the count probably wouldn't take as long as filling the form.

Another option to make the hourglass stay would be to "guess-timate" the
time it will take to retrieve the records. You could get a count first,
adjust this time by the number of records being returned, and set up a loop
to loop for that number of seconds. To do this, you would get the Date and
Time as you entered the loop (i.e. Now()) and loop until that time plus the
calculated number of seconds. You would need a DoEvents statement in the
loop to allow other processes to run while you're looping. When checking for
the ending time, check to see if you've reached it or passed it (>= instead
of =), you may not catch it exactly on the specified time.

Example:
Dim dteLoop As Date, intWait As Integer
'calculate estimated retrieval time in seconds and place
'this value in intWait
dteLoop = DateAdd("s", intWait, Now)
Do Until Now >= dteLoop
DoEvents
Loop

--
Wayne Morgan
MS Access MVP

Hi All,

In a search form I have code that builds a select statement using a query
taking values from form controls as parameters and then adding a few more
criteria generated by passing other values in controls through specialized
sub routines. The code then sets the record source of a sub form to the
select statement. Works a treat. This whole process can take up to 30
seconds over the network and I want to visually let the user know that the
search is executing. I dont display the status bar so text or a progress
bar is out of the question there. I've tried displaying an hourglass or
custom built form during the process(as implemented in the code below) but
the hourglass 'displays' only for a tiny fraction of a second at the end
of the process(no good to me) or the 'frmSearchIndicator' displays only
its border and none of the detail section.

Any ideas?

Regards,

Penny.

....................................

Sub lblSearch_Click()

On Error GoTo HandleErrors
' Create a WHERE clause using search criteria entered by user and
' set RecordSource property of frmSearchCandidatesGenericSUBResults.

Dim strBaseSQL As String
Dim strCriteria As String
Dim strRecordSource As String
Dim intArgCount As Integer
Dim Tmp As Variant

DoCmd.Hourglass True
'DoCmd.openForm "frmSearchIndicator"

' Initialize argument count
intArgCount = 0

strBaseSQL = "SELECT * FROM qrySearchCandidates WHERE "
strCriteria = ""

' Use values entered in text boxes in form to create more criteria for
WHERE Clause.
AddToWhere1 [BasisOfEmployment], "[WorkingHoursSought]", strCriteria,
intArgCount
AddToWhere2 [TypingTestAccuracy], "[TypingTestAccuracy]", strCriteria,
intArgCount
AddToWhere4 [WillingToTemp], "[WillingToTemp]", strCriteria,
intArgCount
AddToWhere8 [OverallGrade], "[OverallGrade]", strCriteria, intArgCount

' If no criteria specified, return all records.
If strCriteria = "" Then
strCriteria = "True"
End If

' Create SELECT statement.
strRecordSource = strBaseSQL & strCriteria

' Set RecordSource property of frmCandidateSearchSubResults.
Me![Child1].Form.RecordSource = strRecordSource

' If no records match criteria, display message.
' Move focus to Clear button.
If Me![Child1].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no records that match the criteria you have
entered.", 48, "No Records Found"
'Me.lblClear_Click
Else
' Enable control in detail section.
Me![Child1].Enabled = True
End If

'DoCmd.Close acForm, "frmSearchIndicator"
DoCmd.Hourglass False

ExitHere:
Exit Sub

HandleErrors:
MsgBox Err.Number & ": " & Err.Description, , "lblSearch_Click"
Resume ExitHere

End Sub


.....................................

 

[Penny]

Thanks for your replies Sandra and Wayne,

The DoEvents doesn't seem to help.

One problem I do see is that you don't clear the hourglass prior to your
MsgBox, so if the box did pop-up , the user wouldn't be able to click the
Ok button. Also, you need to clear the hourglass in your error handler in
case you wind up there.

Good point but doesn't help either.

To make the hourglass stay until the form is done retrieve records, you
may be able to get a count of the number of records the SQL should return
prior to assigning SQL to the RecordSource, then create a loop that will
run until the RecordCount of the subform matches this count. I don't know
if this would work, but even if it would, you would have to go to the
back-end twice, once to get the count and once to fill the form. Although,
getting the count probably wouldn't take as long as filling the form.

Going to the backend twice on this network is not an option for speed
reasons

Another option to make the hourglass stay would be to "guess-timate" the
time it will take to retrieve the records. You could get a count first,
adjust this time by the number of records being returned, and set up a
loop to loop for that number of seconds. To do this, you would get the
Date and Time as you entered the loop (i.e. Now()) and loop until that
time plus the calculated number of seconds. You would need a DoEvents
statement in the loop to allow other processes to run while you're
looping. When checking for the ending time, check to see if you've reached
it or passed it (>= instead of =), you may not catch it exactly on the
specified time.

Example:
Dim dteLoop As Date, intWait As Integer
'calculate estimated retrieval time in seconds and place
'this value in intWait
dteLoop = DateAdd("s", intWait, Now)
Do Until Now >= dteLoop
DoEvents
Loop

Interesting Wayne but I don't see how it addresses the fact that the
hourglass doesn't 'display' when it is called. Isn't it just adding more
code to be processed in the interim? It's not just the hourglass either. If
I try to make a different form(e.g with text saying 'Search Processing) the
source of the child control for the search duration it doesn't display
either, ditto for a pop-up form.

Any other ideas?

Regards,

Penny.

 

[Wayne Morgan]

I suspect that the reason it doesn't display is because there is so little
for the code to do between when you turn it on and turn it off that you're
turning it off before you see it. I doubt that the code needs more than one
or two milliseconds to execute the commands between Hourglass True and
Hourglass False.

 

[Penny]

Hi Wayne,

Because I work on this application on my own computer with the tables
imported back into the app' I slotted a 50 million times loop that
increments an integer to slow it down to take 10 seconds. That is to
simulate the query in the first part of the select statement(which on the
live networked version takes around 10 seconds).

Although I've had no luck with the hourglass, adding the DoEvents command
alows me to change the text and colour of the button users click to initiate
the search(even that wouldn't work before). I'm happy with that for now as
all I really 'needed' to do was let the user know the search is in progress.

Thanks for your help.

Regards,

Penny