asked on

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

MNelson831

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)

Joe Jersey

ASKER

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.

SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MNelson831

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joe Jersey

ASKER

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?

Joe Jersey

ASKER

Thanks! ;-)

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"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

Joe Jersey

ASKER

Ahh that's great! Very useful to know.
thanks again
J.