Hi SKV. If you want to display a messge rather than an hour-glass, here is a method I use a lot in Excel that I'm sure would work in MS Access.
Create a user form (e.g. called 'PlsWaitFrm') and add a label. Make sure the 'ShowModal' property is equal to False.
Just before you need to use it, display the userform using:
Code:
PlsWaitFrm.Show vbModeless
This will ensure the userform is running modless (i.e. it doesn't stop execution of any other code, like a modal userform would).
You could just make the label display a message like 'Pleaes wait...', but if you are iterating through a list or number of records or something, you could use the label to update the number of records or the name of the current record for example. Say you wanted to show the name of the current record being updated/actioned, you can do the following:
Code:
PlsWaitFrm.Label1.Caption = "Updating record for " & strRecordName & "..."
DoEvents
Where
strRecordName (just as an example) is a variable holding the persons name of that record. If the person was "John Citizen" and this is stored in the variable
strRecordName, the userform will show, "Updating record for John Citizen...".
The
DoEvents built-in function is used to pass control onto the operating system to ensure messages are processed. You may not strictly need this, but I found it forces changes to the userform to be shown (especially if you have Application.ScreenUpdating equal to False).
Lastly, if you're using the same userform from different functions/subs, then you should include a check to make sure that the userform is still shown (hasn't been accidentally closed down by a previous sub/function), something like:
Code:
bDlgOpen = IsUserFormLoaded("PlsWaitFrm")
If bDlgOpen Then
PlsWaitFrm.Label1.Caption = "Your message here!!!"
DoEvents
End If
The code for the IsUserFormLoaded function is below:
Code:
' Code taken from URL: [URL="http://www.ozgrid.com/forum/showthread.php?t=152892"]Test if a specific userform is loaded[/URL]
Public Function IsUserFormLoaded(ByVal UFName As String) As Boolean
Dim UForm As Object
IsUserFormLoaded = False
For Each UForm In VBA.UserForms
If UForm.Name = UFName Then
IsUserFormLoaded = True
Exit For
End If
Next
End Function
When you no longer need the userform any more use:
Apologies if this was a long winded solution to a simple question!