Solved

Prevent screen flicker when VBA updates a field (No screen flicker when I update a field by hand)

Posted on 2013-10-22
20
4,856 Views
Last Modified: 2013-10-23
I am Microsoft Access (Office 365).  Note :  I am using continuous forms.

I have a field that only holds two values.  They are Null or "Y".  If I change the value by hand, I do not get a screen flicker.  If I do it using VBA,  (activated by the click event on that field),    the code below flips the value with the bonus of a screen flicker.   I have asked a similar question in the past and received answers that minimized the flicker,  but I really hope that you can help me eliminate the screen flicker when changing a value of a field in an existing record using VBA.

Thank you

Private Sub FlipTag()
    Dim TheTable As String: TheTable = "[tblCreate StockItems]"
    Dim TheTag As String: TheTag = "[Tag_CreateStockItem]"
    Dim TheStockNum As String: TheStockNum = "[Stocknum]"
    Dim strNewValue As String: strNewValue = IIf(Nz([Tag_CreateStockItem], "") = "Y", "Null", "'Y'")
    Dim TheStr As String
    TheStr = "UPDATE " & TheTable & " Set " & TheTag & " = " & strNewValue & " where " & TheStockNum & " = " & [StockNum]
    CurrentDb.Execute TheStr
    Application.Echo False
    Me.Refresh
   Application.Echo True
End Sub

Open in new window

0
Comment
Question by:peispud
  • 10
  • 4
  • 3
  • +2
20 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39592474
The code you have there should eliminate the flicker...
Application.Echo False
Application.Echo True

Try putting the code on the After update even instead of the click event...

Is the flicker "unbearable"?

Other:
< They are Null or "Y". >
Then if only two states are allowed for this field, I would use a Boolean field
and set a default value.
I hate using "Null" as a selection/option

Lets see if another expert has more insight into this...


JeffCoachman
0
 

Author Comment

by:peispud
ID: 39592533
The After Update event does not trigger when the field is changed by vba.

The flicker is bearable,  but I very much don't like it at all.  I am trying hard to make my work look as good as I can.

When VBA changes a record, then it should be smooth and seamless.  

Aubrey
0
 
LVL 84
ID: 39592541
I'm curious why you have to call Me.Refresh.

Also, so that we're clear: You're changing the value of a record that is NOT the current record on a form - is that correct? Otherwise, there's no benefit from using VBA ...
0
 

Author Comment

by:peispud
ID: 39592570
This is a "Tag" field.   It has no purpose except for the user to create a group of records  to be referenced to .  This field gets wiped out (set to null) regularly.

The mouse is used to change the value from Null to "Y" so that reports can be generated on any record where the tagged record is set.

So,  the user might click on the several records (on this field) to change the value as desired.  Then, the user would instantiate a report of any record where the record's tag field has been set.

To answer another Jeff's question.  The field could be boolean to be more efficient.  I may do this later.  

This is a continuous form.
0
 
LVL 21
ID: 39592621
Aubrey,

I have seen labels not attached to controls cause flickering. Check to be sure all the labels are attached to a control.


Attach a label to a control

 1) Open a form, report, or data access page in Design view.
 2) Click the label that you want to attach to a control.
 3) Click Cut Button image on the toolbar.
4)  Click the control that you want to attach the label to.
 5) Click Paste Button image on the toolbar.

from: http://office.microsoft.com/en-us/access-help/attach-a-label-to-a-control-HP005187730.aspx
0
 

Author Comment

by:peispud
ID: 39592644
I have all my labels detached from their control.  I could not have imagined that this would cause any problem.  I will work on the immediately and report back here.
0
 
LVL 21
ID: 39592704
I have all my labels detached from their control.

I always attach my label to controls. Why? Because in  Access you can click the label to select the text in the control.
0
 

Author Comment

by:peispud
ID: 39592800
Ok,  all my labels are attached to their controls.  I have commented out the last 3 lines of the code (see code)

Nothing has changed really.  The table is updated correctly as before.  Now that the 3 last lines of the code have been commented out, there is no screen flicker.  The underlying table gets updated but the continuous form does not reflect the update ------ Unless I introduce code that refreshes the screen & causes the flicker.






 Dim TheTable As String: TheTable = "[tblCreate StockItems]"
    Dim TheTag As String: TheTag = "[Tag_CreateStockItem]"
    Dim TheStockNum As String: TheStockNum = "[Stocknum]"
    Dim strNewValue As String: strNewValue = IIf(Nz([Tag_CreateStockItem], "") = "Y", "Null", "'Y'")
    Dim TheStr As String
    TheStr = "UPDATE " & TheTable & " Set " & TheTag & " = " & strNewValue & " where " & TheStockNum & " = " & [StockNum]
    CurrentDb.Execute TheStr
'    Application.Echo False
'    Me.Refresh
'   Application.Echo True

Open in new window

0
 

Author Comment

by:peispud
ID: 39592849
The following code works,  but it's sloppy and slow.  Still, I am hoping that it might help.  See the last two lines.


 Dim TheTable As String: TheTable = "[tblCreate StockItems]"
    Dim TheTag As String: TheTag = "[Tag_CreateStockItem]"
    Dim TheStockNum As String: TheStockNum = "[Stocknum]"
    Dim strNewValue As String: strNewValue = IIf(Nz([Tag_CreateStockItem], "") = "Y", "Null", "'Y'")
    Dim TheStr As String
    TheStr = "UPDATE " & TheTable & " Set " & TheTag & " = " & strNewValue & " where " & TheStockNum & " = " & [StockNum]
    CurrentDb.Execute TheStr
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious

Open in new window

0
 

Author Comment

by:peispud
ID: 39592918
I've been playing around.    The following code works is much much better than anything that I have seen so far.  Again, see the last two lines of code .... docmd

   For clarity, note that [Stocknum] is a field in the same record.  This works nicely.  It's not sloppy anymore.  There is no screen flicker now!

Still, the code is very slow.  It's not elegant!   I want "Elegant" and I want it to be naturally fast.  I am hoping that this issue can be solved completely and without reservation.  



Private Sub FlipTag()
    Dim TheTable As String: TheTable = "[tblCreate StockItems]"
    Dim TheTag As String: TheTag = "[Tag_CreateStockItem]"
    Dim TheStockNum As String: TheStockNum = "[Stocknum]"
    Dim strNewValue As String: strNewValue = IIf(Nz([Tag_CreateStockItem], "") = "Y", "Null", "'Y'")
    Dim TheStr As String
    TheStr = "UPDATE " & TheTable & " Set " & TheTag & " = " & strNewValue & " where " & TheStockNum & " = " & [StockNum]
    CurrentDb.Execute TheStr
    DoCmd.GoToControl ("StockNum")
    DoCmd.GoToControl ("TagCreateStockItems")
End Sub

Open in new window

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
ID: 39592923
You can always stop the flicker with the API. To do that, in a Standard Module add this to the General Declarations section:

Declare Function LockWindowUpdate Lib "user32" (ByVal hwnd As Long) As Long

You can then use that function like this:

LockWindowUpdate <your windows handle>

Where 'hWnd' is the Windows Handle of the form you wish to "freeze". So if I wanted to lock the screen from a button click on a Form, I'd do this:

LockWindowUpdate Me.Hwnd

To "unfreeze", call it again with an argument of 0 (zero):

LockWindowUpdate 0

Just be SURE to call the unfreeze code, otherwise it'll appear to lock up the application.
0
 

Author Comment

by:peispud
ID: 39592969
LSMConsulting

Your idea has merit.  I understand it.   I haven't tried it yet though.
The following code (my code) works perfectly though. It's perfect,  but a little too slow.  I would like to make it faster (more natural).

 It is time for me to get some sleep. So, now there  are two threads to the solution.  I would like the experts on here to consider both threads.

   
Private Sub FlipTag()
    Dim TheTable As String: TheTable = "[tblCreate StockItems]"
    Dim TheTag As String: TheTag = "[Tag_CreateStockItem]"
    Dim TheStockNum As String: TheStockNum = "[Stocknum]"
    Dim strNewValue As String: strNewValue = IIf(Nz([Tag_CreateStockItem], "") = "Y", "Null", "'Y'")
    Dim TheStr As String
    TheStr = "UPDATE " & TheTable & " Set " & TheTag & " = " & strNewValue & " where " & TheStockNum & " = " & [StockNum]
    CurrentDb.Execute TheStr
    DoCmd.GoToControl ("StockNum")
    DoCmd.GoToControl ("TagCreateStockItems")
End Sub

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39593330
Just use the RecordsetClone - far easier, much faster, no flicker:

Private Sub FlipTag()
    Dim rst As DAO.Recordset
    Dim varNewValue As Variant

    Set rst = Me.RecordsetClone
    varNewValue = IIf(IsNull(Me![Tag_CreateStockItem]), "Y", Null)

    rst.Edit
        rst("[Tag_CreateStockItem]").Value = varNewValue
    rst.Update

    Set rst = Nothing
End Sub

/gustav
0
 
LVL 84
ID: 39593566
So, now there  are two threads to the solution.  I would like the experts on here to consider both threads.
EE policy is that you ask a single question per thread. You should ask the question about performance in a separate question.
0
 

Author Comment

by:peispud
ID: 39593827
Point taken,  I will address performance in a separate question.

I have tried LSMConsulting's advice and it does work as advertised.

I have tried to implement Cactus_Data's code.  When run, it reads the correct data but only updates the first record every time.
More importantly, the screen flickers.  Before I decide on the solution, I thought that I might give Cactus_Data an opportunity to address these problems.

It is awesome to be getting the caliber of help that is available here.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39593851
Yes, forgot the bookmark, sorry:

Private Sub FlipTag()
    Dim rst As DAO.Recordset
    Dim varNewValue As Variant

    Set rst = Me.RecordsetClone
    varNewValue = IIf(IsNull(Me![Tag_CreateStockItem]), "Y", Null)
    rst.Bookmark = Me.Bookmark
    rst.Edit
        rst("[Tag_CreateStockItem]").Value = varNewValue
    rst.Update

    Set rst = Nothing
End Sub

If the screen flickers by this, something else is going on, like running (other) Before/AfterEvents or format conditions. That may be taken care for by controlling Painting:

Private Sub FlipTag()
    Dim rst As DAO.Recordset
    Dim varNewValue As Variant

    Set rst = Me.RecordsetClone
    varNewValue = IIf(IsNull(Me![Tag_CreateStockItem]), "Y", Null)

    Me.Painting = False
    rst.Bookmark = Me.Bookmark
    rst.Edit
        rst("[Tag_CreateStockItem]").Value = varNewValue
    rst.Update
    Me.Painting = True

    Set rst = Nothing
End Sub

/gustav
0
 

Author Comment

by:peispud
ID: 39593997
I've tried the revised recordset code.  It still flickers.

As you suggested,  I started to look at form events but could find nothing causing a problem.

So,  I created a new access database and included only the table and the continuous form stripped everything.  The attached file only has the "bare bones" to test the code for flicker.
The database is as basic as I could make it.

I don't expect for you to look at this,  but if you decide to look at it but if you do, then I would be interested in your reply.   That flicker to me is like a bug landing on your nose when you are required to not move a muscle.
Recordset-still-has-flicker.accdb
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 350 total points
ID: 39594082
You are right. I guess it's because you assign the table field, not the textbox.
However, this works:

Private Sub FlipTag()
    Me!TagCreateStockItems.Value = IIf(IsNull(Me!TagCreateStockItems), "Y", Null)
    Me.Dirty = False
End Sub

/gustav
0
 

Author Closing Comment

by:peispud
ID: 39594161
Really excellent answers from the experts here!

Besides having the problem solved perfectly,  I have learned techniques that will help me in the future.

Thank you very much!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39594168
You are welcome!

/gustav
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

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…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

760 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

18 Experts available now in Live!

Get 1:1 Help Now