Conditional Formatting Multiple Forms

[Damo1412]

Hi,

I have developed a database for work with (a lot) of help from this forum to record and process jobs.

As the database has developed, there are now a lot of forms for different inputs / searches etc. On quite a few of these forms, I have created a sort of conditional formatting using VBA meaning that our biggest customers are allocated a colour which is used throughout the office to quickly recognise the customer.

When the user selects this customer from a combo box, certain fields change colour to match the client's designated colour.

The code I use is:

If Me.ClientURNComboBox = 202 Then
Me.Text113.BackColor = RGB(255, 153, 255)
Else
If Me.ClientURNComboBox = 166 Then
Me.Text113.BackColor = RGB(164, 213, 226)
Else
If Me.ClientURNComboBox = 147 Then
Me.Text113.BackColor = RGB(253, 140, 0)
Else
If Me.ClientURNComboBox = 175 Then
Me.Text113.BackColor = RGB(255, 255, 153)
Else
If Me.ClientURNComboBox = 201 Then
Me.Text113.BackColor = RGB(214, 234, 175)
Else
Me.Text113.BackColor = RGB(255, 255, 255)
End If
End If
End If
End If
End If
Me.Refresh

This code works fine but occasionally the amount of work from a particular client increases or decreases meaning that they may drop off or be added to the list of the "big 5".

When this happens, I have to manually change the code on a number of forms. Is there a way that I can enter the default colours on a table or similar where each form checks the table to show which colour (if any) to apply to the formatting.

Thanks for your help

 

[Ranman256]

put the colors in a table, (not code)
on the client record, put 1 field,RGB
then you would just alter the RGB values on the record, and IT changes the colors.

r = left([RGB],2)
g = mid([RGB],3,2)
b =right([RGB],2)

.color = RGB(r,g,b)

 

[Damo1412]

Thanks Ranman,

I'm not completely sure what you mean.

Do I add a new Field Name to the "Client" table called "RGB" and for the relevant client enter this as "255, 153, 255" or whatever colour I want it to be.

Finally change the VBA on the forms to read:

Me.ClientURNComboBox = .colour

 

[Pat Hartman]

You would need to put the clientID plus the three color codes in the table. Then create a sub or function that you can call. You would pass in the clientID and a reference to the control. The sub would open a recordset to look up the clientID. If found it would set the background property of the control using the other columns in the table. Otherwise, the control would not be changed. The control names don't have to be the same.

 

[Damo1412]

Thanks,

I've got it now