Solved Variable fails with Type Mismatch

[dibblermail]

I assume I'm being an muppet, but cant see where.

I have this code

ColourBack = DLookup("ItemValue", "BackgroundStoredValues", "[UseID]=1 AND [ItemID]=1")

the Public variable ColourBack is stored as a String

ItemValue is saved as short text in the table
UseID & ItemID are stored as short numbers in the table

I'm using ColourBack here

Me.TBOX_DateInitial.BackColor = ColourBack

Its finding what I need it to when I hover over the variable. But its giving a type mismatch error when it hits the line above.

If I use the immediate window & type ?ColourBack it resolves correctly as RGB(254, 254, 254)

If I hard code it works fine

Me.TBOX_DateInitial.BackColor = RGB(254, 254, 254)

What am I missing?

 

[theDBguy]

Try it this way:

Me.TBOX_DateInitial.BackColor=Eval(ColourBack)

 

[dibblermail]

Try it this way:

Me.TBOX_DateInitial.BackColor=Eval(ColourBack)

That's perfect, it resolves it correctly. I'll do it that way going forward.

But what was wrong with it in the first place? What did I miss that meant it required the Eval function?

Or is it a "because Access" sort of issue?

 

[MajP]

You have a string stored.

Eval Function - Microsoft Support

support.microsoft.com

 

[dibblermail]

You have a string stored.

What was the right way to store them?

 

[theDBguy]

What was the right way to store them?

The BackColor property requires a Long number, that's why you got a Type Mismatch error.

 

[Mike Krailo]

What was the right way to store them?

Long

 

[MajP]

If you like working with R, G, B then you can create your table to store R, G, B.
Something like

CatID	Red	Green	Blue
A	150	166	60
B	28	17	17
C	31	148	112
D	90	161	115
E	157	71	85
F	248	232	96
G	124	105	241
H	115	160	49
I	223	157	197
J	124	102	172
K	54	161	140
L	10	115	86
M	197	158	172
N	182	43	222

Then you can build a function that returns the RGB value for a UserID and ItemID. In my case I only have a CategoryID I pass to the function.


Something like

Public Function GetColor(Category As Variant) As Long
  Dim r As Integer
  Dim g As Integer
  Dim b As Integer
 
  If Not IsNull(Category) Then
    r = DLookup("Red", "tblCategories", "CatID = '" & Category & "'")
    g = DLookup("Green", "tblCategories", "CatID = '" & Category & "'")
    b = DLookup("Blue", "tblCategories", "CatID = '" & Category & "'")
    GetColor = RGB(r, g, b)
  Else
    GetColor = RGB(255, 255, 255)
  End If
End Function