[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
6,657 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 85
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 600 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 52

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 85
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 52

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 52

Accepted Solution

by:
Gustav Brock earned 1400 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 52

Expert Comment

by:Gustav Brock
ID: 39594168
You are welcome!

/gustav
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

649 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