Solved

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

Posted on 2009-04-03
10
249 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
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 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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

860 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