Global Conditional Formatting

[Earl Anderson]

At work, my department uses a customized A2k3 db for most of the processes
needed for our various jobs. I have somehow become the departmental db
guru. I've been asked to make the forms in the db "more user friendly" by
highlighting the textboxes which have the focus with the 'light orange'
backcolor. I know I can go into design view of each form, select each txtbx
control (collectively) and then 'Format-->Conditional Formatting-->at
Condition 1-Field Has Focus-->'light orange' background color-->OK. This is
relatively simple, except with 283 forms in this departmental db, the
process could become tedious & tiresome. Is there a programmatically way of
globally coding the function for every form in the db (283) to accomplish
this tedious task so that any form in the db when opened has the 'focused'
field's background as 'light orange'?

Thx...
Earl

 

[Marshall Barton]

At work, my department uses a customized A2k3 db for most of the processes
needed for our various jobs. I have somehow become the departmental db
guru. I've been asked to make the forms in the db "more user friendly" by
highlighting the textboxes which have the focus with the 'light orange'
backcolor. I know I can go into design view of each form, select each txtbx
control (collectively) and then 'Format-->Conditional Formatting-->at
Condition 1-Field Has Focus-->'light orange' background color-->OK. This is
relatively simple, except with 283 forms in this departmental db, the
process could become tedious & tiresome. Is there a programmatically way of
globally coding the function for every form in the db (283) to accomplish
this tedious task so that any form in the db when opened has the 'focused'
field's background as 'light orange'?

You can write a procedure to loop through all the form
documents, open each one in design view, loop through the
form's controls and if it's a text box or combo box, set its
FormatCondition object. Use error handling to catch any odd
situations.

Here's a very rough outline of the kind of code you can use

Dim ao As AccessObject
Dim ctl As Control

For Each ao In CodeProject.AllForms
If ao.IsOpen Then DoCmd.Close acForm, ao.Name
DoCmd.OpenForm ao.Name, acDesign
With Forms(ao.Name)
For Each ctl in .Controls
If ctl.ControlType = acTextBox _
Or ctl.ControlType = acComboBox Then
With ctl.FormatConditions
If .Count = 0 Then
.Add acFieldHasFocus
.Item(0).BackColor = RGB(255,255,0)
End With
End If
Next ctl
DoCmd.Close acForm, ao.Name, avSaveYes
End With
Next ao

Be sure to have a good backup of your database after you
make any change to the code and restore from the backup
after each test and before making another change.

I don't have a suggestion about what to do for text boxes
that already are using CF so the code just skips them.

You'll have to figure out a different way of highlighting
controls that don't have conditional formatting.

 

[Earl Anderson]

Much appreciate the direction. I will get at it right away.
Thanks much...
Earl