Hex() and/or Hex$() problems

[Cosmos75]

Am trying to create a color database. I have a form with the following textboxes

ColorID (PK - Autonumber)
ColorName (Text)
Hex (Text)
R (Number)
G (Number)
B (Number)
MSACCESSColorNumber (Number)

Added a button to a Tabular Form with the folowing code to calculate Hex and MS Access Color Number if the user changes the RGB (Red, Green, Blue) values for the color.

Dim HEXCalc As String
Dim MSAccNumCalc As Variant
Dim R As Variant
Dim G As Variant
Dim B As Variant
Dim HexR As String
Dim HexG As String
Dim HexB As String

R = Me.R.Value
G = Me.G.Value
B = Me.B.Value

HexR = Hex(R)
HexG = Hex(G)
HexB = Hex(B)

HEXCalc = "#" & IIf(Len(HexR) = 1, "0" & HexR, HexR) &_
 IIf(Len(HexG) = 1, "0" & HexG, HexG) & IIf(Len(HexB) = 1, "0" & HexB, HexB)

MSAccNumCalc = RGB(R, G, B)

Me.Hex = HEXCalc
Me.MSACCESSColorNumber = MSAccNumCalc

I get Run-time error '13': Type Mismatch for HexR =Hex(R).

I get a compile error for HerR = Hex$(R), "Type declaration character does not match declared data type" - Something to do with how I dimmed?

I've tried
- Dim R as Single/Double
- Dim HexR as String/Double/Single
All combinations don't work.

What am I missing??

I have an appened query that has this formula that works.
HEXCalc: "#" & IIf(Len(Hex([R]))=1,0 & Hex([R]),Hex([R])) & IIf(Len(Hex([G]))=1,0 & Hex([G]),Hex([G])) & IIf(Len(Hex())=1,0 & Hex(),Hex())

 

[Fizzio]

Just Dim them without a qualifier ie

Dim HexR, HexB, HexG

and see if that helps

 

[Shadez]

Hmm,

What its the type of Me.R.Value ie do you need to change it to a int before using Hex

eg

HexR = Hex(cint(R))

Have you tried Dim 'ing

R
G
B

as integer

 

[Cosmos75]

Fizzio and Shadez, thanks for your quick replies.

Unfortunately, neither Dim HexR nor Dim R as Integer.

I've tried using CSgl, CDbl, and CInt like this;
R = CSgl(me.TxtR)
- but that doesn't work either.

But strangely (to me at least) this does work. I created a seperate form with three text boxes (StoreR, StoreG, and StoreB) with their respective datasources being the textboxes on the form where the code doesn't work. I then run the code from the second form refering to the textboxes on the second form. The code will work that way. But I would rather not have to open another form and have the code work from the first form.

I tried to add textboxes to the first form and do the same thing. Doesn't work...

 

[Fizzio]

Unfortunately, neither Dim HexR nor Dim R as Integer.

Have you tried them both together? ie

Dim R ,G, B As Integer
Dim HexR, HexG, HexB

 

[Cosmos75]

Have you tried them both together? ie

Dim R ,G, B As Integer
Dim HexR, HexG, HexB

Yes... No luck.

Maybe it has something to do with the fact that it's referencing a textbox that is a field of a record?

 

[Fizzio]

Here Cosmos, try this. Nicked your code and it works perfectly.

 

[Cosmos75]

Fizzio,

Thanks for the sample. Yours does work, but I am trying to work with the fields for HEX and Ms Access Color Number, whereas you are calculated and showing the values. I want the user to be able to check that the calculated numbers matches the curent record and then change it accordingly if they so choose.

Here's what I have. I don't see much of a difference so I can't figure out what is going wrong.

 

[Fizzio]

I see where you are struggling as I could not get the Hex function to work either in your Db, depite the syntax being correct. Have you tried importing into a ned Db and trying again?
Youe main form has all the info on that you need so I actually do not see the need for this 'checking' form anyway?