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

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

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
peispud
Asked:
peispud
  • 10
  • 4
  • 3
  • +2
2 Solutions
 
Jeffrey CoachmanCommented:
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
 
peispudAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
peispudAuthor Commented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
peispudAuthor Commented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
peispudAuthor Commented:
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
 
peispudAuthor Commented:
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
 
peispudAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
peispudAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
peispudAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
peispudAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
peispudAuthor Commented:
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
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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