Re: Hourglass wont display while code executes

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


"Penny" <penny@xxxxxxxxxxxxxx> wrote in message
news:newscache$6pw2ui$7f6$1@xxxxxxxxxxxxxxxxxxxxxxx
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


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




.