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

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

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

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

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.

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

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.

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.

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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

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.

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.

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

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

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!

You are welcome!

/gustav