Form Click Event

[bigukfan]

I have a single Form with a Tab Control with multiple pages, and over 200 controls, mostly Textbox and ComboBox. I have some code that is fired by the KeyPress event of the Form (KeyPreview = True) that loops through controls, changing the BackColor to white except for the control with the focus, which has a BackColor of yellow. This all works fine.

Problem is the end users often move around the Form using the mouse and clicking into controls they want to update, so I am trying to find a way of firing the code from a Form MouseDown or Click event. Obviously I could put a reference to the Sub Routine in each individual control to call it on Click, but that means repeating it over 200 times. There must be a simpler way!

Here is the code that works on Form KeyPress event (variable iTabPage is the Tab page value, so I only loop through the Page with the focus)

Dim Ctl As Control
For Each Ctl In Me![TabCtl].Pages(iTabPage).Controls
    If Ctl.ControlType = acTextBox Or Ctl.ControlType = acComboBox Then
        Ctl.BackColor = IIf(Ctl.Name = Screen.ActiveControl.Name, 8454143, 16777215)
    End If
Next
 

[missinglinq]

First off, if you have 200 controls on a single form that can be updated, meaning, in essence bound to a single underlying table, your database is almost surely not normalized. Most experienced developers will tell you that they never have more than 25-35 fields per table.

Having said that, you're making an incredible amount of unnecessary work for yourself. Assuming you're running version 2002 or later, all you have to do is use Conditional Formatting.

1. Select as many controls as you want, holding down the <Shift> key
2. Goto Format Menu - Conditional Formatting
3. Under Condition select Field Has Focus
4. Now use the BackColor Icon to the right to set your color

Repeat this until all controls are included.
Linq ;0)>