How do I repaint Access so it doesn't look like it has crashed during a query?

Hi Experts,

I wonder if you can help.  I have a button in an access form that produces a mailmerge and launches MS Word.  As soon as word is launched, it runs some code to update the merge records and creates a new record in a log table.  Basically there is a function called UpdateCommLog that creates a new record with the time stamp etc (in the code sample below).

That all seems to work fine, but if there is a large merge then it can take a while for the process to complete.  But if the user Alt-Tabs from the merge document (word) back to Access, it looks like it has crashed (even though it is running the query).

Is there a way I can repaint Access durng the query, so if the user goes to access it doesn't look like it has crashed?  I think I may need to use something like 'DoEvents', but I'm not sure where to place it if I do.

Thanks in advance, this would be a real help.

Best regards... Joe.
Dim RS As DAO.Recordset
 
    '//Gets the IDs from the recently selected query
    sSQL = "SELECT ID FROM " & strQuery
    
    Set RS = CurrentDb.OpenRecordset(sSQL)
    
    Do While RS.EOF = False
    
        Call UpdateCommLog(RS(0), "Record Merged")
        sSQL = "UPDATE DetailsQuery SET LastLog = 'Merged' WHERE ID = " & RS(0)
        Call ExecuteSQL(sSQL)
            
        RS.MoveNext
    Loop
    
    Set RS = Nothing

Open in new window

jpirozzoloAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MNelson831Commented:
You could try opening a form that says "loading" with a timer event that refreshes a text box so that it looks something like this:

Loading..
Loading...
Loading....
Loading.....

etc, etc.  You could also set docmd.hourglass = true so that the cursor shows an hourglass until the process is finished.

However, you might still get the "not responding" message displayed in the bottom corner as Access displays this by default whenever it is waiting for a response from another program that is taking a long time.  You might be further ahead to simply train the users to be more patient. (I do know that that is a tall order)
0
jpirozzoloAuthor Commented:
Ahh, that may work thanks!
Can you explain how I would use the timer event?
When the user goes back to Access everything is white!  would this stop this from happening?

Thanks!..... J.
0
GrahamSkanRetiredCommented:
If you need to use DoEvents, put it in the loop.
'...
   Do While RS.EOF = False
        DoEvents
        Call UpdateCommLog(RS(0), "Record Merged")
'...

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

MNelson831Commented:
I doubt that it would prevent the white screen.

Using timer:
Create a new, blank, uunbound form.
Add a textbox (name is irrelevant,, but let's call it DisplayText)
Default valule of text box is "Loading"
On form properties got to events-> timer
Create a module on the OnTimer event like this:

If len(me.DisplayText) < 12 then me.Displaytext = me.DisplayText & "." Else me.DisplayText = "Loading"

Set timer interval to 1000 * however many seconds you want between updates.
Cheers
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
As suggested ... use the DoEvents command - which is exactly what it's for.

You can also you the SysCmd() function to display either a 'status' or a progress bar in the Access status bar at the bottom of the Access window.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here is the example using SysCmd progress meter with DoEvents

Dim RS As DAO.Recordset
 
    '//Gets the IDs from the recently selected query
    sSQL = "SELECT ID FROM " & strQuery
   
    Set RS = CurrentDb.OpenRecordset(sSQL)
    If RS.RecordCount = 0 Then
            msgbox "No records"
            Set RS = Nothing
            Exit Function
     Else
           Dim lRecCnt As Long
           RS.MoveLast
           lRecCnt = RS.RecordCount
           RS.MoveFirst         ' ready for code below
      End If
 
      syscmd (acSysCmdInitMeter,"Processing ...",lRecCnt )
      Dim lCnt As Long: lCnt = 0

    Do While RS.EOF = False
        lCnt = lCnt +1
        Call UpdateCommLog(RS(0), "Record Merged")
        sSQL = "UPDATE DetailsQuery SET LastLog = 'Merged' WHERE ID = " & RS(0)
        Call ExecuteSQL(sSQL)
        Call SysCmd(acSysCmdUpdateMeter, lCnt )    
        RS.MoveNext
        DoEvents      '   As suggested by GrahamSkan
    Loop
   
    Set RS = Nothing
    Call SysCmd(acSysCmdClearStatus)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jpirozzoloAuthor Commented:
Hi Experts,

Thank you VERY much for all your suggestions.  Sorry MNelson831, I went for the DoEvents option in the end - but thanks for your help.

Only thing is, I'm not sure how to arrange the points, because GrahamSkan mentioned where to position the DoEvents, but DatabaseMX provided good code that was VERY useful, especially with the Call SysCmd(acSysCmdClearStatus) part.

Thanks again for all your help.

Regards,

Joe.

PS:  No points I know, but can anyone tell me what DoEvents 'actually' does?
0
jpirozzoloAuthor Commented:
Thanks!  ;-)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"DoEvents 'actually' does?"

It allows Windows to 'catch up' with screen processing, etc.  From the Help File:

"DoEvents Function
     

Yields execution so that the operating system can process other events.

Syntax

DoEvents( )

Remarks

The DoEvents function returns an Integer representing the number of open forms in stand-alone versions of Visual Basic, such as Visual Basic, Professional Edition. DoEvents returns zero in all other applications.

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.

DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.

Caution   Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control."

mx
0
jpirozzoloAuthor Commented:
Ahh that's great!  Very useful to know.
thanks again
J.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.