Conditional Formatting flickering on and off when subform is requeried

Question

I have an Access front end linked to SQL Server for the database. I have a main form containing a subform, set as a Datasheet, to display the records in a grid to the user with conditional formatting. The recordset is not updateable, so I have a separate form which modifies the data. Once that form has modified the data, I need to call a Requery to refresh the data.

The problem is, when this line is hit in the VBA, the subform's conditional formatting momentarily turns off and then back on again, which causes a 'flicker' effect to the end user, where they can temporarily see the numbers of the data rather than the colour coding as required.

I have tried to update the record in the table but as it isn't updateable, this is not an option. I have also tried setting Application.Echo False before the execution to no avail. This seems to make no difference.

How can I stop this from happening?

The code line below:

Private Sub saveData_Click()
    Dim x as Integer

    Call frmSaveData(x)           '// Calls the routine to save the data
    Me.subFormData.Form.Requery   '// Calls the subform query to refresh and formatting 'flickers'
End Sub

`Application.Echo False` has served me well for these types of issues. Can you post the code you tried with this? (Can there be any event subs that turn it back on prematurely?) — Andre, Aug 03 '21 at 14:36
I'd be very wary of using `Application.Echo` without significant error handling, because if VBA throws an error and ejects you from the routine before turning it back on it'll freeze the UI, which will require users to close Access manually. It also (in my experience) leads to a jerky experience when turned back on. — hecon5, Apr 14 '22 at 12:23

score 1 · Answer 1 · answered Aug 03 '21 at 17:37

Switching off painting should do:

Private Sub saveData_Click()

    Dim x as Integer

    ' One or both. Test.
    Me.Painting = False
    Me!subFormData.Form.Painting = False

    Call frmSaveData(x)           '// Calls the routine to save the data
    Me.subFormData.Form.Requery   '// Calls the subform query to refresh and formatting 'flickers'

    Me!subFormData.Form.Painting = True
    Me.Painting = True

End Sub

score 0 · Answer 2 · answered Mar 22 '22 at 12:04

Building off @Gustav's answer:

Turn off painting and (as long as you're not using a passthrough query as your recordsource), requery the recordsource, not the Form. If your requery has a trigger this may not work, as it won't fire that event; but otherwise, these two things have been pretty significant to improve UX for me.

Some general code notes:

You don't need to use Call in VBA. Code will run just fine without it. It won't harm your code to leave as-is though.
Use Long instead of Integer on modern machines. Performance might improve, and you'll avoid overflow issues. This is especially important if you use SQL Server as a backend, as SQL Server's Integer type is actually a Long according to VBA.
It's not clear what the x does; I'm assuming this was abstracted away to make the answer generic. It's (unlikely) but possible whatever's assigning the value is flickering the form. Turning off painting should help with this case, too, though.

Try this:

Private Sub saveData_Click()

    Dim x as Long

    ' One or both. Test.
    Me.Painting = False
    Me!subFormData.Form.Painting = False

    frmSaveData(x)           '// Calls the routine to save the data
    Me.subFormData.Form.Recordset.Requery   ' Requery the recordset here instead

    Me!subFormData.Form.Painting = True
    Me.Painting = True

End Sub

Conditional Formatting flickering on and off when subform is requeried

2 Answers2