Solved

Screen flicker Access 2002

Posted on 2012-12-23
50
605 Views
Last Modified: 2013-01-17
Hi All

I am trying to get Access to calculate all calculated controls before a screen is refreshed either when moving to a new record on a form or simply just opening it.

DoCmd.Echo False  does not seem to do anything useful.
0
Comment
Question by:DatabaseDek
  • 28
  • 9
  • 8
  • +2
50 Comments
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Instead of DoCmd.Echo try Application.Echo, don't forget to reset the echo before the end of your code.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Do a
me.repaint

All pending screen updates will complete before Access continues

Jim
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
You also might want to do a repainting = false. ( similar to echo off, but its just for the form)
0
 

Author Comment

by:DatabaseDek
Comment Utility
Application.Echo = True I get Augment not optional

Repainting or Me.Repaint = False gets Expected Augment
0
 

Author Comment

by:DatabaseDek
Comment Utility
Sorry

Repainting or Me.Repaint = False gets Expected Function or Augment
0
 

Author Comment

by:DatabaseDek
Comment Utility
Oops! It's the subform that's flickering. But I still cannot stop it whatever I do.
0
 

Author Comment

by:DatabaseDek
Comment Utility
So when I move to a new record on the main form the subforms controls flicker like a Christmas tree.

Is there a "Don't repaint subform until you have finished messing around" Function or property?
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Use this to prevent the subform controls from being repainted:
Me.NameOfSubformControl.Form.Painting = False

And when done use:
Me.NameOfSubformControl.Form.Painting = True
0
 

Author Comment

by:DatabaseDek
Comment Utility
Hi and thanks

That's a lot better for stopping flicker but Painting False makes the subform disappear showing the underlying form and the brings it back when processing is finished. So it works but causes a different problem.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Without knowing anything about the main/subform or the nature of these calculations, it is difficult to envision your issue here.

<I am trying to get Access to calculate all calculated controls>
What are these calculations doing (in a control), that cannot be done in a query?

Also can you first take a step back and explain:
1. What's in the Main form.
2. What is in the subform
3. What is the nature of these calculations...

When you ask: "get Access to calculate all calculated controls", it is not clear why this does not happen automatically...?
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
If you can upload a copy of your database with only the relevant forms and tables, that would help us.  Just remove any private info first.
0
 

Author Comment

by:DatabaseDek
Comment Utility
Hi All

There are a number of Dlookup controls to show relevant info in the subform.

I will also look at any calculated controls that may be possible to calculate in the underlying query. (Thanks Boag) Other than that I am not sure what it is doing. (there are 200 fields in the subform. It describes a very complicated product.)
0
 

Author Comment

by:DatabaseDek
Comment Utility
Regrettably I cannot upload a sample DB. It is so immense that it would take me hours to separate and keep all the relevant queries tables and dlookup references that it would make more sense to accept the flicker.

Boag's "When you ask: "get Access to calculate all calculated controls", it is not clear why this does not happen automatically...?" ................It is calculating all stuff automatically it's just that in the process the screen flickers. It's as if it is running a requery or refresh 4 times. But I do not have any code in the form or subform's underlying OnOpen, OnCurrent, OnLoad  etc. to do this
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Then let's go back to your original post, ...you stated
<DoCmd.Echo False  does not seem to do anything useful. >

Without seeing the actual code you tried this in, it is difficult to troubleshoot.

Docmd.Echo false
...basically turns off the screen, so you may be able to use this to avoid the flicker.

But this is an "old school" trick, (and had to be managed tightly in your error handler, or the app would appear to be "Locked, if there was an error)

"Flicker" has been noted in "tab controls"
See here: http://allenbrowne.com/ser-46.html

JeffCoachman
0
 

Author Comment

by:DatabaseDek
Comment Utility
Thanks Jeff

Docmd.Echo false works in the subform (which is the form that flickers) but not when I move to a new record on the main form (Which is when the subform flickers). So something like:

SubForm.Docmd.Echo false

might work. I repeat i cannot see anything in the code anywhere that is requesting a requery which is what it looks like it is doing.

Derek
0
 

Author Comment

by:DatabaseDek
Comment Utility
Sorry forgot. I don't have any Tab controls.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<
You also might want to do a repainting = false. ( similar to echo off, but its just for the form)
>>

<<
painting or Me.Repaint = False gets Expected Function or Augment
>>

 Should have been .Paint = False as Ron pointed out.

 However if that didn't do the trick, then you don't have many options.

 I do have some API code to lock window updates and then unlock at a given point.  However I'd really go after the process itself.

 First, while Dlookup() is handy, is not meant to be used everywhere and you need to look carefully at where your using it.  All the Domain functions represent a SQL statement and were meant to be used where an Expression is, but a SQL statement not.

 So most importantly, they should never be used in a query.  They should also not be used in VBA code unless your really picking up only a field or two.  If more then that, your far better off to open a recordset with a WHERE clause to restrict to one record or open a set and fetch the record needed.

That way you have all the fields available at one time.

Second thing is I would put a STOP in the OnCurrent event and execute the code.  Then step through with F8 to see what actually is getting fired.  Something your doing may be firing the OnCurrent event multiple times.

 But without seeing the DB, it's tough to say why your seeing the flicker.

 Let me know if you want to try the API code.

Jim.
0
 

Author Comment

by:DatabaseDek
Comment Utility
Hi Jim

I tried the stop and F8. This is weird. The code ran on the main form and switched to the subform oncurrent  code, but ran it twice. Literally from top to bottom and without a break or leaving the procedure back to the top and ran it again????

Since then, I must have switched something off because stop and F8 ignores the oncurrent code in the subform and only runs through the main for code?
0
 

Author Comment

by:DatabaseDek
Comment Utility
Hi

Think I am getting somewhere with it. The flicker is caused by having (ANY) code in the oncurrent event of the subform. When I move between records on the subform I get no flicker. When I move to a new record on the main form it flickers four times. When there is even 1 line of code in subform's oncurrent it runs the code top to bottom without a break or leaving the proceedure twice and flickers four times. All I typed in was Docmd.Echo True and using F8 I can see that it runs the code twice? and flickers 4 times. When I take out that one line docmd.echo true. Then all flicker stops.

So the Echo false and true are probably working but are not the problem it seems that having any code that then runs twice is the problem. I have no idea why it does this.

This is weird.
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
How about uploading a copy of the database with just the main form and subform, never mind tables or queries since you're saying that would take too long to separate.  That way we can have a better picture and give you a more definitive answer.

Ron G.
0
 

Author Comment

by:DatabaseDek
Comment Utility
I will do that
0
 

Author Comment

by:DatabaseDek
Comment Utility
Sample DB.

Have a look at the attached Capture.PNG first as it shows two of the combos that cause the problem.

Put a stop in the code for either combo and whatch the code in the details subform run twice. It's very strange. Notice that the subform flickers as a new project is selected but not if you run through the records in the subform
Capture.PNG
RS.mdb
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Your database doesn't have any forms, it just has 3 queries.
0
 

Author Comment

by:DatabaseDek
Comment Utility
I have just opened the file and I have them. Do you have Tools- Options - "Show Hidden Objects". Ticked?

I have just un-hidden them in this attachment so should work anyway.
RS.mdb
0
 

Author Comment

by:DatabaseDek
Comment Utility
Any Ideas Yet?

Cheers

Derek
0
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.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
When posting a sample database please follow these steps:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
0
 

Author Comment

by:DatabaseDek
Comment Utility
Hi Jeff

Sorry, silly mistake. With linked tables of course when I test, it all works OK. The tables are now in the DB.

The problem is that when one of the combos is used (Screen top left corner) to select a project, the screen flickers about 4 times. The oncurrent code in the details form runs twice?
RS.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
1. I still get the Missing reference error...
2. Your code does not compile (undeclared variables)

In any event, I got no flicker when I selected a "Project Ref"

You have so much going on there code-wise, ...it is difficult to say what the issue might be.
What are your machines, specs?

I am running:
Win XP  
Intel Dual Core 2.2 GHZ
2GB Ram
500MB NVIDIA video
1GB Free memory
...and I got no flicker

JeffCoachman
0
 

Author Comment

by:DatabaseDek
Comment Utility
Hi Jeff

Really sorry for slow response. Had a couple of days off short notice.

I have a i.8 GH on a laptop with 4 GB Ram

The real query is why the code in the details form runs twice?

I have taken all unessassary code out compiled it and attached the DB.

You can now only select a new project by the Project Combo.
RS.mdb
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
The code only runs once for me when I put a breakpoint and step through the code.  But let's see how it works with Jeff.

Ron
0
 

Author Comment

by:DatabaseDek
Comment Utility
Hi Ron

Oh dear, that would indicate a real problem I guess!.

Derek
0
 

Author Comment

by:DatabaseDek
Comment Utility
Hi Ron

Where you stepping through from the after update code from the Project combo, top left of screen?.
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Yes I was.
0
 

Author Comment

by:DatabaseDek
Comment Utility
Can you imagine any reason why it would happen on my computer and not another?

This is really strange. Is there any setting that could cause this?
0
 

Author Comment

by:DatabaseDek
Comment Utility
Anyone else tried this sample DB?
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Have you checked whether the code runs twice in your computer on the sample database you made? I'm wondering if the problem is only on the original database.
0
 

Author Comment

by:DatabaseDek
Comment Utility
Yes, Definitely.
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Do you have another computer you can try the sample on?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Anyone else tried this sample DB? >>

 I'll try it a little latter.

<<Can you imagine any reason why it would happen on my computer and not another?

This is really strange. Is there any setting that could cause this? >>

  What can happen is that because there are some multi-thread operations within Access and also the interaction between "Access" and VBA, you can when moving a DB to a faster/slower machine get differences in order of execution.

 This can cause weird things like this.  Basically it boils down to something your doing some where that you should not be.   This has a tendency to happen more when you try to control a bound form to any degree.  

 The whole idea of a bound form is that Access does the work so you don't have to.  If you try to control that process, it leads to problems.

 A simple example; a timer event updates the form and causes OnCurrent to fire.   OnCurrent on one machine is over and done in an eye blink, but on a slower machine, takes longer and all of suddent OnCurrent starts to interfer with something else that is going on.

 That's just a simple example, but it gives you the idea.  

  I'm pretty busy at the moment, but I'll try and get to this in a bit.

Jim.
0
 

Author Comment

by:DatabaseDek
Comment Utility
My other computer is currently inoperable. So I do not but I will send the sample to a friend that can at least confirm screen flicker. Does the screen flicker when you try it?

I will update tomorrow.

Thank you.
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
I don't see the flicker on mine
0
 

Author Comment

by:DatabaseDek
Comment Utility
Thanks Ron, Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
FYI in Access 2002, I get the flicker and OnCurrent of the detail form fires 5 times.

Jim.
0
 

Author Comment

by:DatabaseDek
Comment Utility
Oh my god! What have I done??
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
OK got a work a round but it's not really addressing the root problem.   I'm not 100% positive, but I believe it's the calcaulted fields in the underlying query that causes the OnCurrent event to fire multiple times and that's the really problem.

 It might also be something in the form...there's a lot there and I have not have gone through it all.

 The work around is to use this code:

Public Declare Function api_LockWindow32 Lib "user32" Alias "LockWindowUpdate" (ByVal hwndLock As Long) As Long
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWndParent As Long, ByVal hWndChid As Long, ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Public Sub atLockWindow(TrueOrFalse As Boolean)

    Dim Status&, hWndTarget&
   
10  On Error Resume Next

20  If TrueOrFalse = True Then
30      hWndTarget = FindWindowEx(Application.hWndAccessApp, 0&, "MDIClient", vbNullString)
40      If hWndTarget <> 0 Then Status = api_LockWindow32(hWndTarget)
50  Else
60      Status = api_LockWindow32(0)
70  End If

End Sub

 Which locks the window from painting at the OS level.  If you do this in the AfterUpdate event of the combo:

Private Sub Combo32_AfterUpdate()
   
    Dim rs As Object

    On Error GoTo Err_Combo32_Click
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[AccountCode] = " & Str(Nz(Me![Combo32], 0))
    atLockWindow True
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    atLockWindow False

Exit_Combo32_Click:
    Exit Sub

Err_Combo32_Click:
    MsgBox "The selected Project has been Deleted. Click OK and select another Project"
    Resume Exit_Combo32_Click

End Sub

 You will see that the flicker is greatly reduced (you see the screen paint only once).  Problem with that is you need to do that everywhere you do something that causes the form to fire OnCurrent.

  Nice thing about your sample DB though; you've got it down to the point where you should be able to whittle down what's causing the multiple OnCurrent's.  I would eliminate the calculated fields in the query first and see if that gets rid of it.  If it does, move the calculations into the controls themselves on the form.

  However, you may not be able to eliminate the problem even if you find what's doing it as it may be "as designed" and simply a result of what your doing.   OnCurrent fires a lot more then people think and it's not just when a record first receives the focus (which is what the on-line help suggests).

 As a side note (although somewhat on point), your form is very "busy".  Personally I would never put that many controls in front of the user at one time.  I would suggest that you consider adding a tab control and breaking up the sections onto multiple tabs.  That would be another way of working around the current problem.

 The flicker is more evident right now because there's so many controls and it takes a long time to repaint the form.  If there are less controls, your not going to notice it as much.

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<< As a side note (although somewhat on point), your form is very "busy".  Personally I would never put that many controls in front of the user at one time. >>

 BTW, don't take that too much to heart.  Form layout and design is an art.   Mostly a matter of style, but sometimes partly a result of the subject at hand.  In this instance, having all those fields in front of the user at one time may make sense.  You did do a great job on that layout.

 I myself though would have done it differently (and I've often been chided for my form layouts<g>).

Jim.
0
 

Author Closing Comment

by:DatabaseDek
Comment Utility
Well that's the first reasonable explanation I have had. I have queried this problem 5 or 6 times over the last 10 or so years without any resolution. So thank you for that and the code. Brilliant.

"Busy forms". having to go to another form for anything is my pet hate. I have seen programs that take you down an endless ladder of different screens to get where you want and in my case by the time I get there I have forgotten what I got there for. Drives me mad.. So I am guilty of a little self indulgence here. The users for this system are professionals who inevitably only carry out this one task. It's called Door scheduling, specifying requirement for Fire Doors and Frames. Which means they know what every control is for, intuitively. I have not had a customer say it looks busy, (That doesn't mean they don't think it is, so I'd better check)

My thanks to you

Derek
0
 

Author Comment

by:DatabaseDek
Comment Utility
Is there anything in

Public Declare Function api_LockWindow32 Lib "user32" Alias "LockWindowUpdate" (ByVal hwndLock As Long) As Long

that would not work on a 64bit windows OS or is window32 nothing to do with the OS type?

One other simple question: Is carrying out calculations in controls rather than underlying queries either slower or more processor burdening
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<that would not work on a 64bit windows OS or is window32 nothing to do with the OS type?>>

 Nothing to do with the OS type; the 32 bit call should work fine under a 64 bit OS.  However, if you move to Office 2010 64 bit (and there is no good reason to at present), then you will need to use ptrSafe in the call.  You can read about that here:

Compatibility Between the 32-bit and 64-bit Versions of Office 2010
http://msdn.microsoft.com/en-us/library/office/ee691831.aspx

<<One other simple question: Is carrying out calculations in controls rather than underlying queries either slower or more processor burdening >>

 In general, no, but it does depend on how their used.  

 Before you make wholesale changes, make sure that really is the problem.  Take your sample DB, remove all the calculated fields from query, and then try the form.  See if the multiple oncurrent calls are gone.

 If not, try the opposite; remove all the calculated controls on the sreen.

 I really didn't dig through what was related to what, but something is causing Access to think the record is changed, which forces it to refire OnCurrent.

 If I had more time, I would have tried to pin it down for you, but I'm behind (like everyone else<g>) on a number of projects.

Jim.
0
 

Author Comment

by:DatabaseDek
Comment Utility
Thanks yet again Jim
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

762 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

11 Experts available now in Live!

Get 1:1 Help Now