RGB backcolour (1 Viewer)

[Snowflake68]

Ive spent far too long trying to work out why this code doesnt work. This should be so easy and its not even Friday.
I want to programmatically create the RGB for the header backcolour on a form by looking up the values in a table based upon the status

The table is called 'tbl_Status and contains the status as well as the RGB value such as RGB (0, 255, 255)
I have tried to do it several ways by just having the numeric values in the table without the RGB() and trying to create the string but just cant get it to work.
I get runtime error 13 type mismatch when loading the form. The field properties in both the Status and the RGB fields are both text so I am not sure why it is a type mismatch.

Dim strStatusRGB As String

strStatusRGB = DLookup("RGB", "tbl_Status", "Status = txtStatus")

Me.FormHeader.BackColor = strStatusRGB

 

[Minty]

RGB Is a long not a string.

In the immediate window type

? RGB (100,10,200)
13109860

And see the result

 

[plog]

BackColor requires a number, not a string. So you can either convert RGB to the appropriate number (I have no clue what that is, 500000 turns into green, 999 is red) and store that in tbl_Status. Or you can some how add more code so that you can use the RGB() function.

You didn't tell us exactly what string is in tbl_Status.RGB so can't really provide a specific solution or guidance. But I bet the easiest way is to somehow convert what you have into 3 distinct pieces of data so that you can use RGB()

 

[theDBguy]

The field properties in both the Status and the RGB fields are both text so I am not sure why it is a type mismatch.

As others have said, the BackColor Property expects a Long Integer, and you're trying to pass a String to it, so that's why you're getting the Type Mismatch error.

Just for fun, what happens if you change your code to this?

Me.FormHeader.BackColor = Eval(strStatusRGB)

 

[sonic8]

strStatusRGB = DLookup("RGB", "tbl_Status", "Status = txtStatus")

I suggest you focus on the where condition of the function call before doing anything else. - As it is now, the where condition is a static string that will be evaluated in the query exactly as it is written above.

 

[theDBguy]

I suggest you focus on the where condition of the function call before doing anything else. - As it is now, the where condition is a static string that will be evaluated in the query exactly as it is written above.

Ah, I missed that. That could be another cause for a Type Mismatch error. When DLookup() doesn't find a match, it returns a Null value, and you cannot assign a null value to a String variable.

 

[moke123]

this works. Used a separate procedure for the split to avoid excessive typing since I assume your using this in more than one place

Dim x, y, z
Dim strRGB as string

    GetRGB "85,142,213", x, y, z

    Me.t1.BackColor = RGB(x, y, z)

Sub GetRGB(strRGB As String, ByRef x As Variant, ByRef y As Variant, ByRef z As Variant)
    Dim m As Variant
    m = Split(strRGB, ",")

    x = m(0)
    y = m(1)
    z = m(2)

End Sub

 

[arnelgp]

I get runtime error 13 type mismatch when loading the form.

maybe pass to Eval() function your strStatusRGB:

Dim strStatusRGB As String

strStatusRGB = DLookup("RGB", "tbl_Status", "Status = '" & txtStatus & "'") & ""

If Len(strStatusRGB) <> 0 Then
    Me.FormHeader.BackColor = Eval(strStatusRGB)
End If

 

[Snowflake68]

RGB Is a long not a string.

In the immediate window type

? RGB (100,10,200)
13109860

And see the result

Thank you, this has put me on the right path now

 

[arnelgp]

you can also use String:

?Eval("RGB (0, 255, 255)")

result:

16776960