Solved

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

Posted on 2009-04-03
10
246 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:jpirozzolo
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 24061113
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
 

Author Comment

by:jpirozzolo
ID: 24061192
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
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 150 total points
ID: 24061353
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
 
LVL 15

Expert Comment

by:MNelson831
ID: 24061417
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
 
LVL 75
ID: 24062274
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 350 total points
ID: 24062366
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
 

Author Comment

by:jpirozzolo
ID: 24079738
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
 

Author Closing Comment

by:jpirozzolo
ID: 31566291
Thanks!  ;-)
0
 
LVL 75
ID: 24079897
"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
 

Author Comment

by:jpirozzolo
ID: 24095952
Ahh that's great!  Very useful to know.
thanks again
J.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now