Setting Back Colour to Hex Value in Table Field

[RWRinn]

Hi,

I'm imagining this is probably a very simple thing to do, but I'm at a loss.

I'm using Access 2003, and I have a table where one of the fields is called colourcode and contains a hexadecimal number representing a colour like you would set it in HTML.

What I want to do is, on my tabular form, where that field is represented on each row, instead of showing the hexadecimal number in text (or as well as, doesn't matter), the back colour of that box uses the hex number to set its colour.

It's for a sort of colour chart stock list thing.




Many thanks!



Richard.

 

[YNWA]

Would you table entries for each code change?

Say you have one field blue, would it change to green ever?

 

[RWRinn]

Thanks for your reply!

The table has six fields, thus:

ID (autonumber)
code (this is a four digit 'RAL' code number for a paint colour)
glosslevel (either 'gloss', 'satin', or 'matt')
colourname (the name for the colour)
stockqty (how much of that paint we have in stock in kgs)
colourcode (hex representation of the colour, such as c8971c)

When the fields are shown in the tabular form, all I want is the back colour for the colourcode field's value to be set by the hex value displayed in that field.

Hopefully that makes sense and I'm not just missing the point of your question! Fingers crossed.



Richard.

 

[YNWA]

I am sorry but I have never done this before.

Does anyone know if the back color of fields in forms will allox hex code as you do not use hex code to set the color in the first place?

I would imagine some sort of lookup would be needed, but that could be a very long loopup with every colour you stock in it.

 

[RWRinn]

Another idea - it doesn't have to be the colourcode field's back colour that is set to the hex value, it could be a rectangle or whatever, just want to see a block of colour set by the colourcode field's hex data contents... Or if hex is a problem, if I could convert all the hex numbers to another format before entering them into the table, if that makes it easier to utilise the data in the way that I want...?!

 

[YNWA]

Some sort of VBA is needed for this I think and you will have to define aload of variables to cover all the colors, codes etc...

Someone more advanced may have the answer but I aint got a clue.

 

[vbaInet]

Create a function that will loop through all the controls that require a change in colour and set the BackColor property to the control's value IF that control is not null. You could properly use the Tag property to give those controls the same name then iterate the controls collection checking against the Tag value.

 

[RWRinn]

Okay, that’s a bit out of my depth. Perhaps if I simplify my problem the answer will be simpler (then again perhaps not!).

Assuming I don’t need to check for null values, and that we forget about the hex codes for the colours, that I’m giving Access the colour in the form it uses in the Back Colour property box.

If I want to colour an object called Box18 on my form, is there a way, instead of putting the number in the Back Colour property box to instead lift the number from a field named colourcode in my table, so that on each entry Box18 will have a custom colour lifted from the data?


Thanks for your input!


Richard.

 

[vbaInet]

Have you got some sample data in your db? If you do, zip, upload and post the db. I will have to see how feasible this can be done. Although I'm not so sure this easily achievable with Datasheets.

 

[RWRinn]

My data has the colours in hex, stored in the table field named 'rgb'. When you look at the form, there's a big yellow square, I was wanting that to be whatever the colour stored in the 'rgb' field was...?!

 

[vbaInet]

There you go, just use this:

Private Sub Form_Current()
    Box18.BackColor = Val("&H" & Me.rgb)
End Sub

Would be an idea to check the length of your value and check for empty strings before setting the value. Unless you're 100% sure your values won't contain errors. Let me know how that works.

 

[RWRinn]

I 'thought' it might be something simple like that. The thing is, I've hardly done anything in Access (or indeed similar) so I'm not sure where to actually put that code. I've put some code into an Event/On Click box before, but of course the Back Colour property box doesn't have an option for building expressions and the like. Sorry if that's an exceedingly dim question, don't kick me.

Thanks for your help!


Richard.

 

[vbaInet]

It's really not a "dim" question. It's more of a "public" one in my opinion lol. That was a vba joke

You're welcome. I thought the colours you had were cool colours by the way.

Cheers.

 

[RWRinn]

Perhaps one day I will understand your joke.

I still don't know where to put the code right enough. Help!

The colours are on-screen approximations of paint colours that we use. Not really my doing, so I can't take credit for them.



Richard.

 

[vbaInet]

Attached.

Open your form in design view. Right-click to view the "Properties". Click in the box that has "[Event Procedure]" written in it and a command button with "..." will appear. Click that button and you will be able to see the code.

 

[RWRinn]

Top banana!

 

[Steve R.]

You may be interested in reading this post Hexadecimal Irritation

Specifically, I will direct you to post #17 which has a conversion function that you may find helpful.

 

[vbaInet]

Good tip Steve. I've actually come across that function before. It still is breaking down the hex string representing it as an rgb. Whereas with the line of code I provided access does the conversion for you (via the Val function). If you search your help files, you will find that it is much better to use Val than CInt for this type of conversion. You will notice I appended the string with an ampersand 'H' which Val understands, CInt on the other hand ignores.

 

[RWRinn]

Thanks for that Steve. I was intending to try to figure out how to convert the hex numbers manually and feed them in as numbers Access understands. Hadn't realised vbaInet had put in a function to automatically convert them. I'm not sure it's converting them correctly in that case, as if you look at the names of the colours versus the colours being displayed, most colours are coming up as shades of blue until you get to the blue colours, and then they go more reds.

 

[vbaInet]

That's right, just had a look and compared it to the actual value. Good thing Steve R. gave you that info.