Microsoft Access Tips for Serious Users

Provided by allenbrowne.com, February 2004.  Updated September 2007

Flicker with tab controls

In Access 2003, simply moving the mouse over the pages of a tab control can make the screen flicker annoyingly if Windows XP themes are active.

September 2007 update: Microsoft reports this issue is fixed in Service Pack 3 for Office 2003.

The cause

The flickering is triggered by unattached labels on the page of a tab control. The workaround is to convert these labels to text boxes.

You could also avoid the issue by right-clicking your Windows XP desktop, choosing Properties, and setting the Theme to "Windows Classic". That is not a satisfactory workaround if you develop databases for others. You cannot solve it by deselecting Use Windows Themed Controls on Forms under Tools | Options | Forms/Reports in Access 2003.

Controls in an option group also flicker when the mouse is passed over the label attached to the group. If you wish to address this issue also, see the comments following the code below.

The fix

The code below loops through all the controls on a form and locates the labels that have a tab page as their parent. It changes the ControlType to text box, assigns the label's Caption to the text box's ControlSource, and sets the Enabled, Locked, and BackColor so the text box looks and behaves like a label.

To use it to fix a form named "MyForm":

1. Open a new module (Modules tab of Database window).
2. Paste in the code.
3. Open the Immediate Window (Ctrl+G), and enter:
       ? ConvertLabelOnTabPage("MyForm")

The function lists to the Immediate Window the names of any labels that were converted.

To fix all the forms in an Access 2003 database, enter:
    ? FixAllForms()

Warnings:

1. This solution is not suitable if your application uses labels as quazi-buttons for the user to click. Since a disabled text box cannot be clicked, these "labels" would become inoperative.
2. Backup your mdb before use. This code saves the changes without confirmation.

Function ConvertLabelOnTabPage(strFormName As String, _
        Optional bSaveAndClose As Boolean, Optional bHidden As Boolean)
    'Purpose:   Change unattached labels on pages of tab control into text boxes.
    '           Avoids flicker bug under Windows XP themes.
    Dim frm As Form
    Dim ctl As Control
    Dim strName As String
    Dim strCaption As String
    Dim bytBackStyle As Byte
    Dim bChanged As Boolean
    Const strcQuote = """"
    
    'Open the form in design view
    DoCmd.OpenForm strFormName, acDesign, _
        windowmode:=IIf(bHidden, acHidden, acWindowNormal)
    Set frm = Forms(strFormName)
    
    'Find the labels whose parent is a tab page.
    For Each ctl In frm.Controls
        If ctl.ControlType = acLabel Then
            If ParentIsTabPage(ctl) Then
                bChanged = True
                strName = ctl.Name           'ctl reference will be lost.
                strCaption = ctl.Caption     'For ControlSource.
                bytBackStyle = ctl.BackStyle 'Access doesn't set this.
                Debug.Print strFormName & "." & strName
                'Convert it to a text box.
                ctl.ControlType = acTextBox
                'Set the text box properties.
                With frm.Controls(strName)  'ctl is now undefined.
                    .ControlSource = "=" & strcQuote & _
                        Replace(strCaption, strcQuote, strcQuote & strcQuote) & strcQuote
                    .Enabled = False
                    .Locked = True
                    .BackStyle = bytBackStyle
                End With
            End If
        End If
    Next
    
    Set ctl = Nothing
    Set frm = Nothing
    If Not bChanged Then
        DoCmd.Close acForm, strFormName, acSaveNo
    ElseIf bSaveAndClose Then
        DoCmd.Close acForm, strFormName, acSaveYes
    End If
End Function

Private Function ParentIsTabPage(ctl As Control) As Boolean
    On Error Resume Next
    ParentIsTabPage = (ctl.Parent.ControlType = acPage)
End Function

Function FixAllForms()
    'Purpose:   Run ConvertLabelOnTabPage() for ALL forms in this database.
    'Warning:   Saves changes without confirmation.

    Dim accobj As AccessObject
    For Each accobj In CurrentProject.AllForms
        Call ConvertLabelOnTabPage(accobj.Name, True, True)
    Next
End Function

Flicker with option groups

Passing the mouse over the label attached to an option group also causes the items in the group to flicker. This is not handled by the code above, since 1) it is a lesser issue, and 2) converting the group's label could disable a shortcut key.

To convert the labels of your options groups to text boxes also, replace the ParentIsTabPage() function above with this:

Private Function ParentIsTabPage(ctl As Control) As Boolean
    On Error Resume Next
    ParentIsTabPage = ((ctl.Parent.ControlType = acPage) Or _
        (ctl.Parent.ControlType = acOptionGroup))
End Function