Color values from table

[RWR3]

Hi, I am trying to enter a color value in a table and use it in a VBA code that changes. I have the following code that works but, I have to put each value of one field in there and assign it a color in the code. I would like the code to look up the value of one field and use the corresponding color code in another field.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.AAR_Mech_Code
Case "XA"
Me.AAR_Mech_Code.BackColor = 9434879
Case "XM"
Me.AAR_Mech_Code.BackColor = 36095
Case "NE"
Me.AAR_Mech_Code.BackColor = 16745131
Case "FM"
Me.AAR_Mech_Code.BackColor = 2474495
Case "GB"
Me.AAR_Mech_Code.BackColor = 12180223
Case "HM"
Me.AAR_Mech_Code.BackColor = 9539985
Case "MW"
Me.AAR_Mech_Code.BackColor = 3937500
Case "PA"
Me.AAR_Mech_Code.BackColor = 11857588
Case "RP"
Me.AAR_Mech_Code.BackColor = 16758883
Case "SM"
Me.AAR_Mech_Code.BackColor = 128
Case "TM"
Me.AAR_Mech_Code.BackColor = 13092807
Case "FW"
Me.AAR_Mech_Code.BackColor = 3329330
Case Else
Me.AAR_Mech_Code.BackColor = 15631900
End Select
End Sub


Robert

 

[jdraw]

Please describe what you want in plain English. A simple example might help.

You might have a Table along these lines, but I really don't follow the need for all of the various colors.

[U]id[/U]                       ' sample   value 1
Field_ControlName          ' sample   value "AAR_Mech_Code"
Field_Control_ColorGood   ' sample   value 3937500
Field_Control_ColorBad    ' sample   value 11857588

 

[nanscombe]

Try this ...

Public Function controlColour(ByRef theControl as Variant, Byval theColorTag as String)
Dim theColor as long

  theColor = Nz(DLookup("colorInfoField", "tblColorInfoTable", "colorTagField = '" & theColorTag & vbNullString & "'"), -1)
  If theColor <> -1 then theControl.BackColor = theColor

End If

Usage: controlColor(theControl, theColorTag)
controlColor(Me.AAR_Mech_Code, Me.AAR_Mech_Code)

It will look up the value passed in as theColorTag and apply it to the backgroundColor of the control passed in as theControl.

 

[RWR3]

Hi,

My reason for the various colors is that on a report (where the VBA code is) I want each type to have its own background color of its box. The report is generated from a query.

I have a Query where I have several fields but the only important ones are

ID, AAR_Mech_Code, Color, and others.

There are 12 different types each with a corresponding color. The items are one of those 12 different types. I would like to know how to use the color field in the color so I did not have to go in the code and change it there. This is incase I let someone else use this, I forget how to change this, add another type with corresponding color, or what to just change the colors with out having to change the code.

 

[RWR3]

nanscombe,

Public Function controlColour(ByRef theControl as Variant, Byval theColorTag as String)
Dim theColor as long

theColor = Nz(DLookup("colorInfoField", "tblColorInfoTable", "colorTagField = '" & theColorTag & vbNullString & "'"), -1)
If theColor <> -1 then theControl.BackColor = theColor

End If

The field's background color I want changed is AAR_Mech_Code and the color field is BKColor (I changed it when I realized I had another field named that on another table but goes into the same query, but it is a text description) So can you explain what is going on because I would like to know what is happening in this code.

 

[nanscombe]

This is assuming that I've understood that you want a proper table to hold the values rather than the "Select Case" list of values you had above.

Public Function controlColor(ByRef theControl as Variant, Byval theColorTag as String)
Dim theColor as long

  theColor = Nz(DLookup("colorInfoField", "tblColorInfoTable", "colorTagField = '" & theColorTag & vbNullString & "'"), -1)

  If theColor <> -1 then theControl.BackColor = theColor

End If

Using controlColor(Me.AAR_Mech_Code, Me.BKColor)

The field "colorTagField" in the table "tblColorInfoTable" would be searched for a value which matches the value passed as "theColorTag" (Me.BKColor).

If it is found the value in field "colorInfoField" is assigned to the Long Integer variable "theColor", if no matching value is found then a value of -1 is assigned.

If "theColor" is not -1 the control referenced by "theControl" (Me.AAR_Mech_Code) will have it's BackColor attribute set to the value in "theColor".

 

[jdraw]

You could have a lookup table, based on my post#2, with values such as below.
I'm not sure I follow why you need the colors, but you seem to be interested only in
AAR_Mech_Code, so you could have a query that includes the lookup table and assign the backcolor on the Form/report directly from the table

MyVal, MyColor

"XA", 9434879
"XM", 36095
"NE", 16745131
"FM", 2474495
"GB", 12180223
"HM", 9539985
"MW", 3937500
"PA", 11857588
"RP", 16758883
"SM", 128
"TM", 13092807
"FW", 3329330
" ", 15631900

 

[RWR3]

Hi,

I tried this and nothing happens

Public Function controlColour(ByRef theControl As Variant, ByVal theColorTag As String)
Dim theColor As Long
theColor = Nz(DLookup("AAR Mech Code", "Stock List Query", "'BKColor' = '" & theColorTag & vbNullString & "'"), -1)
If theColor <> -1 Then theControl.BackColor = theColor
End If

AAR Mech Code - The field that decides the color
Stock List Query - The query that both fields are part of
BKColor - The field that contains the color values