Coding conditional formating on focus

[accesskastle]

I am trying to modify code to apply a backcolor when they have focus. Some
of the forms are already made with very many controls, and I don't want to
have to go in manually and change it all with conditional formatting. I have
a module using a modified version of Marshall Barton's code, (see posting on
9/23/08) but when I run it on my forms, the conditional formatting is not
added.

If anyone can give me an idea of where my code is going wrong, I'd really
appreciate it. I am running Access 2003 with SP3. Thanks!

AK


Please see below:

Sub ColorActiveControl(lngHighlightColor As Long)
'Apply conditional formatting to all existing forms
'so that active control will be highlighted in color specified
'when the field has focus

'lngHighlightColor should be a readable highlight color, e.g., 16777164

'Code modified from Marshall Barton, 9/23/2008

'Dim frm As Forms

Dim obj As AccessObject
Dim ctl As Control
Dim dbs As Object
Set dbs = Application.CurrentProject

On Error GoTo Err_ColorActiveControl

For Each obj In dbs.AllForms
If obj.IsLoaded Then
DoCmd.Close
DoCmd.OpenForm obj.Name, acDesign
Else
DoCmd.OpenForm obj.Name, acDesign
End If

With Forms(obj.Name)
Debug.Print "-----------------------------------------"
Debug.Print obj.Name
Debug.Print "-----------------------------------------"
For Each ctl In Forms(obj.Name)

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox
Then
With ctl.FormatConditions
If .Count = 0 Then
.Add acFieldHasFocus
.Item(0).BackColor = lngHighlightColor
Debug.Print ctl.Name & "'s properties have been changed."
Else
Debug.Print ctl.Name & " count = " & .Count
End If
End With
Else
End If
Next ctl
DoCmd.Close acForm, obj.Name, acSaveYes
End With
Next obj

MsgBox "Completed conditional formatting."

Exit_ColorActiveControl:
Exit Sub

Err_ColorActiveControl:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume Exit_ColorActiveControl
End Sub

 

[Marshall Barton]

Well, I must say that this has been a real mystery, probably
a bug.

My procedure that sets form control properties worked as
expected and I had to start over using the code you posted
to see that there is indeed a problem. The difference
between my procedure and the code you are trying to use is
that my procedure also sets some other control properties
(e.g. FontName, FontSize, BackColor, etc). When I commented
out all of the other property setting code in my procedure,
I finally got it to exhibit the problem.

I conclude from all this mucking about that Access does not
recognize that using code to add a FormatCondition item is a
design change. Consequently, closing the form with
acSaveYes does not save the changes.

As soon as I added a line of code to change some other
property:
.Controls(0).Top = .Controls(0).Top
right before the close, then everthing worked as expected.

 

[accesskastle]

Thanks Marshall for the code and the suggested change. I applied the
property equality you suggested and then the code worked for me too. Like
you said, probably a bug.

AK

Well, I must say that this has been a real mystery, probably
a bug.

My procedure that sets form control properties worked as
expected and I had to start over using the code you posted
to see that there is indeed a problem. The difference
between my procedure and the code you are trying to use is
that my procedure also sets some other control properties
(e.g. FontName, FontSize, BackColor, etc). When I commented
out all of the other property setting code in my procedure,
I finally got it to exhibit the problem.

I conclude from all this mucking about that Access does not
recognize that using code to add a FormatCondition item is a
design change. Consequently, closing the form with
acSaveYes does not save the changes.

As soon as I added a line of code to change some other
property:
.Controls(0).Top = .Controls(0).Top
right before the close, then everthing worked as expected.
--
Marsh
MVP [MS Access]

I am trying to modify code to apply a backcolor when they have focus. Some
of the forms are already made with very many controls, and I don't want to
have to go in manually and change it all with conditional formatting. I have
a module using a modified version of Marshall Barton's code, (see posting on
9/23/08) but when I run it on my forms, the conditional formatting is not
added.

If anyone can give me an idea of where my code is going wrong, I'd really
appreciate it. I am running Access 2003 with SP3.

Sub ColorActiveControl(lngHighlightColor As Long)
'Apply conditional formatting to all existing forms
'so that active control will be highlighted in color specified
'when the field has focus

'lngHighlightColor should be a readable highlight color, e.g., 16777164

'Code modified from Marshall Barton, 9/23/2008

'Dim frm As Forms

Dim obj As AccessObject
Dim ctl As Control
Dim dbs As Object
Set dbs = Application.CurrentProject

On Error GoTo Err_ColorActiveControl

For Each obj In dbs.AllForms
If obj.IsLoaded Then
DoCmd.Close
DoCmd.OpenForm obj.Name, acDesign
Else
DoCmd.OpenForm obj.Name, acDesign
End If

With Forms(obj.Name)
Debug.Print "-----------------------------------------"
Debug.Print obj.Name
Debug.Print "-----------------------------------------"
For Each ctl In Forms(obj.Name)

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox
Then
With ctl.FormatConditions
If .Count = 0 Then
.Add acFieldHasFocus
.Item(0).BackColor = lngHighlightColor
Debug.Print ctl.Name & "'s properties have been changed."
Else
Debug.Print ctl.Name & " count = " & .Count
End If
End With
Else
End If
Next ctl
DoCmd.Close acForm, obj.Name, acSaveYes
End With
Next obj

MsgBox "Completed conditional formatting."

Exit_ColorActiveControl:
Exit Sub

Err_ColorActiveControl:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume Exit_ColorActiveControl
End Sub