Show Form While Processing

[Guest]

Hi:

I have a query that takes a while to run. I would like to display a form
while it is compiling so that the users do not think the system has frozen.

I am having trouble with an IsLoaded statement..........

I have written the following code:

Private Sub cmdCloseSplash_Click()
On Error GoTo Err_cmdCloseSplash_Click

Dim stDocName As String

DoCmd.Close

'Expiry Check takes a while, display form while waiting
DoCmd.OpenForm "frmExpiryStatus"

'Expiry Check
stDocName = "rptExpiry"

If frmExpiryStatus.IsLoaded = True Then
DoCmd.Close acForm, "frmExpiryStatus"
End If

If IsNull(DLookup("strDispositionNumber", "qryExpiry")) Then
MsgBox "Expiry Check OK. Preview cancelled", vbInformation
Else
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.Maximize
MsgBox "Pending Expiration Dates require your attention",
vbInformation
End If


Exit_cmdCloseSplash_Click:
Exit Sub

Err_cmdCloseSplash_Click:
MsgBox Err.Description
Resume Exit_cmdCloseSplash_Click

End Sub

I am getting a message box that just says "Object Required" and my form
frmExpiryStatus stays open.

I'm not very good with efficient with code so any suggestions would be
awesome.

Thanks

 

[Guest]

Hi CJ,

If you step through the code, where is it breaking? Most likely a Set
statement is missing. If you could let us know where it is executing up to,
that would help.

In the Agile way of things, if you just trying to get this done ASAP, you
could just alter the mouse cursor to display an hourglass until the query has
completed execution. This method is simple and effective, but it would
depend on whether or not the user can accomplish other tasks while waiting,
or if they have to wait on the results of the query.

Lance

 

[Guest]

Hi:

The code breaks after the frmExpiryStatus loads. The error pops up right
away, so I know the query hasn't even started yet.

BTW, My cursor does change into an hourglass but that frequently happens
when systems freeze, so I would prefer something a little more obvious in
this case.

Thanks

 

[Guest]

Ahhh... I think I see one of the problems.

Substitute:
If frmExpiryStatus.IsLoaded = True Then
DoCmd.Close acForm, "frmExpiryStatus"
End If


With:
If CurrentProject.AllForms("frmExpiryStatus").IsLoaded = True Then
DoCmd.Close acForm, "frmExpiryStatus"
End If

Let me know if that gets us past that point.

Lance

 

[Guest]

OK, so far so good.

That got past the break point, but now the form does not show up at all.

I need to have;

If CurrentProject.AllForms("frmExpiryStatus").IsLoaded = True Then
DoCmd.Close acForm, "frmExpiryStatus"
End If

nested inside of the next statement;

If IsNull(DLookup("strDispositionNumber", "qryExpiry")) Then
MsgBox "Expiry Check OK. Preview cancelled", vbInformation
Else
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.Maximize
MsgBox "Pending Expiration Dates require your attention",
vbInformation
End If

so that the form stays put until the report is ready.

Thanks for all of your help!!

 

[Guest]

Ok, copy this revised method over and let me know how it goes.

Private Sub cmdCloseSplash_Click()
On Error GoTo Err_cmdCloseSplash_Click

Dim stDocName As String

DoCmd.Close acForm, "frmMain"

'Expiry Check takes a while, display form while waiting
DoCmd.OpenForm "frmExpiryStatus"

'Expiry Check
stDocName = "rptExpiry"

If CurrentProject.AllForms("frmExpiryStatus").IsLoaded = True Then
If IsNull(DLookup("strDispositionNumber", "qryExpiry")) Then
MsgBox "Expiry Check OK. Preview cancelled", vbInformation
Else
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.Maximize
DoCmd.Close acForm, "frmExpiryStatus"
MsgBox "Pending Expiration Dates require your attention",
vbInformation
End If
End If

Exit_cmdCloseSplash_Click:
Exit Sub

Err_cmdCloseSplash_Click:
Dim errorMessage As String
errorMessage = "An error has occurred while attempting to view the
Expiry Status report" & Chr(13) & Chr(13)
errorMessage = errorMessage & Err.Number & ": " & Err.Description
MsgBox errorMessage, vbOKOnly + vbCritical, "Error Message"
Resume Exit_cmdCloseSplash_Click

End Sub

One final suggestion... I usually create a separate class for this kind of
thing. Usually consists of a dedicated progress screen that either shows a
progress bar or an animated GIF showing the operation being carried out. The
form usually has a label or two that I can pass strings into as well so I can
display custom messages. Just a thought for your consideration.

Lance

 

[Guest]

Lance, so so close.....

It all works !!!

However the main form (frmLoadSplash) does not finish closing before my
frmExpiryStatus shows up so the screen is "messy".

I used repaint after the frmExpiryStatus loads but that does not clean up
the closing form.

So, this is what I have:

Dim stDocName As String

DoCmd.Close acForm, "frmLoadSplash"

'Expiry Check takes a while, display form while waiting
DoCmd.OpenForm "frmExpiryStatus"
DoCmd.RepaintObject acForm, "frmExpiryStatus"

'Expiry Check
stDocName = "rptExpiry"

If CurrentProject.AllForms("frmExpiryStatus").IsLoaded = True Then
If IsNull(DLookup("strDispositionNumber", "qryExpiry")) Then
DoCmd.Close acForm, "frmExpiryStatus"
MsgBox "Expiry Check OK. Preview cancelled", vbInformation
Else
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.Maximize
DoCmd.Close acForm, "frmExpiryStatus"
MsgBox "Pending Expiration Dates require your attention",
vbInformation
End If
End If

CJ

 

[Guest]

Hmmm... unfortunately I don't have a copy of Access where I am right now so I
can't really say other than I would try and move the RepaintObject call (or
use the Repaint method if you are using Access 2003;
http://msdn.microsoft.com/library/d...n-us/vbaac11/html/acmthRepaint_HV05186327.asp)
into the area where the actual lookup is being performed. It is most likely
one of those operations that are messing with your screen redraws.

If I get a chance to look at it again later tonight, I will try and see if I
can't figure out a cleaner solution since I have been kind of winging it.

Good luck.

Lance

 

[Guest]

Not a problem.

Thanks for all of your help Lance. If you solve the problem, please reply
and let me know.

CJ

 

[Guest]

Hi CJ,

Did my earlier suggestion of using the Repaint method help, or are you still
getting the redraw ugliness? I tried to get something similar going last
night but couldn't duplicate the same issue.

Let me know.

Lance

 

[Guest]

Hi Lance:

I actually looked at it again last night and found the problem!!

The code in this database was opening a switchboard menu behind the scenes,
it was that form that was causing the problem. I made it open as "hidden" in
the background and "normal" after the report closes.

Thanks again for all of your help Lance.