MS Access 2010 - Split form flickering [Conditional formatting]

Question

Problem:

Everything was working fairly well, and then I added two fields to the form body (and the datasheet): "Impact" and "Interest". I set these to have conditional formatting, so that if they contain the text "red", they have a red fill.

When the form loads, the conditional formatting is instantly applied to the first entry in the datasheet view, and it takes a second to apply to the other two entries I put "yellow" and "green" in (which are conditioned to have a yellow and green fill, respectively). If I set the third entry to be selected when the form has loaded, this one has the formatting applied instantly to the datasheet view. MS Access says it is "Calculating", and when this finishes the conditional formatting is applied.

Further to this, when the first text filter is applied the form body text boxes "impact" and "interest" flicker, as well as the second text filter in the header and the "forename" of the selected entry in the datasheet view (my VBA code selects the forename after the filter is applied). When I move the mouse, the flickering stops and the filter applies. Sometimes the flickering doesn't happen, though. This has only happened since I added the "impact" and "interest" fields, with conditional formatting.

This is the basic structure of my form:

Form Header: A few buttons, and two text boxes. The user types in their search terms, and hit the "apply filter" buttons to narrow down the data shown in the datasheet view.

Datasheet view: The key columns are visible, and when the user clicks on one, it's profile is brought up in the form.

Form body: This contains the data from the datasheet, laid out according to category.

Here's the code for when my form loads - it loads all the relevant data, sets up a check box, and sets the focus on the first text filter:

Private Sub Form_Load()

Me.RecordSource = "SELECT * FROM Staff "
CheckImpInt.Enabled = True
CheckImpInt = False
StaffTotalSearchText1.SetFocus

End Sub

And here's the code for the first filter button:

Private Sub Filter1Button_Click()

If CheckImpInt = True Then

Me.RecordSource = "SELECT * FROM Staff " & _
"WHERE [Interest] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Impact potential] Like '*" & Me![StaffTotalSearchText1] & "*'"

Else

Me.RecordSource = "SELECT * FROM Staff " & _
"WHERE [Forename] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Surname] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Position] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Group] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Division] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [All groups] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Expertise] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Institutes] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Roles] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [skills - analysis] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [skills - compute] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Notes] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Paired IC] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
   "Or [Paired Company?] Like '*" & Me![StaffTotalSearchText1] & "*' "

End If

End Sub

I'm confused. When does it flicker - you say "when the form loads" - when does that happen? Is it when you click on datasheet entry? Or does it flicker when you click on Apply Filter button. What is code in Form_Load event - and in the command button click event for Apply Filter — dbmitch
When the form loads, all the colours in the datasheet view asides from the ones in the selected entry only have conditional formatting applied after MS Access has finished "calculating". This "calculating" happens whether there is any conditional formatting or not. — james.sw.clark

dbmitch dbmitch · Accepted Answer · 2016-07-19T14:18:35

I'm not sure why recordsiurce is being set in your FormLoad event - it looks like it remains the same - except for filters.

I'd recommend

Set your form's recordsource to [Staff] and remove Me.RecordSource = "SELECT * FROM Staff" from the Form_Load event
Change your filter_button event to use filters instead of using recordsource

Update FilterButton_Click sub

Private Sub Filter1Button_Click()  
    If CheckImpInt = True Then

        Docmd.ApplyFilter , "[Interest] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Impact potential] Like '*" & Me![StaffTotalSearchText1] & "*'"

    Else

        Docmd.ApplyFilter , "[Forename] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Surname] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Position] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Group] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Division] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [All groups] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Expertise] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Institutes] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Roles] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [skills - analysis] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [skills - compute] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Notes] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Paired IC] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
           "Or [Paired Company?] Like '*" & Me![StaffTotalSearchText1] & "*' "

   End If

End Sub

You'd also get better performance if you moved these two filters into actual queries - then change the ApplyFilter to use the Where parameter instead of Filter, but that's a different tact altogether.

Finally if you want to remove the flicker completely you can add lines to stop the screen updates before you apply the filter - and then turn it on at the end of the sub.

Toggle Screen Updating

Add Application.Echo False before the if block
Add Application.Echo True after the if block

MS Access 2010 - Split form flickering [Conditional formatting]

1 Answers