• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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

0
jpirozzolo
Asked:
jpirozzolo
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
 
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 MVP, Access and Data Platform)Commented:
"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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now