How to use record RGB Values to set a form field backcolor?

[JohnGeeee]

Can someone please help me with if the RGB( red,green,blue) function can be used to set the backcolor of a field in a form in Datasheet view?

I have a list of items in a table and each item can have a distinct colour. in a table I have the Item name field and fields for Red, Green, Blue that contain the individual RGB values. I want to have a form in a datasheet view format that shows a selected number of items and various other details for that item from other tables as a list. Then I also want to show a blank field next to the item names in the list result that displays the colour of each item automatically by setting the backcolor of this field using the relevant Red, Green, Blue values . I have tried using something like =Backcolor RGB([Red],[Green],[Blue]) (I know this is wrong, just giving it as an example) in various OnOpen properties for the blank field but cannot get it to work.

Is it possible to use these functions and properties in this way? If so can someone please point me in the right direction on how to correctly write the function? Should I be doing it differently as part of a Dlookup() or IIF() function instead?

[ranman256]

if you look at the color property of the control, youll see hex code like: #255255255

with that, you can assign color via:
txtbox.backcolor = RGB(255,255,255)

rgb uses the hex# in 3 params.

[davegri]

Ranman, not quite. RGB(255,255,255) is equivalent to a backcolor property setting of #FFFFFF, not #255255255.
The hex value FF is equal to decimal 255.
TO confuse matters further, you can also set the backcolor property to the decimal value of the 6 hex positions as a whole. FFFFFF converts to decimal 16777215
In the immediate window, printing rgb values gives the same. ?RGB(255,255,255) will give you 16777215.

However none of this address the OP's immediate problem. I'm having trouble visualizing what JohnG wants to accomplish. It might not be possible in datasheet view, as unbound controls won't cooperate as might be expected with individual row settings.
We need a mockup image of the desired result.

[kd2017]

I don't think it's possible, especially in datasheet view. If it is possible it would be in continious form view. Even then you cannot control the background color of control for each row individually. If you change the color while the cursor is on one row it'll change on every row.

How many possible colors do you think you might need to cover? You could use little images instead.

Or how many rows would you be looking at at a time? What's the purpose of the app? There's some other not very accessy hacks that come to mind.

[JohnGeeee]

Thank you for responses. I have previously created register type lists where the results in one column shows different background colour on each record line ie if value =Pass then backround is green and if value = Fail then background is red. This was pretty straightforward using defined RGB or hex values that i put into the code. What I want to do with this new list is get the values for Red, Green,Blue from values stored in a table for each item. I have created a version as an excel spreadsheet but I had to manually change the background colour for the cell I was using as the colour indicator. In this version I want the results to come from a query showing a list of items but the colour of the background changes according to each item in the query result.

So for example part of the form result would show something like this where the background colour is automatically created by the Red,Green,Blue field values using the RGB() function. The number of possible items that are different colours would 1,000's. The table will also come out with other values like quantity required and order dates. It would also have other colour coordinates in it like X,Y,Z and CIELAB colour cordinates. Then the plan is to be able to sort by various colour value columns to make the colours fall into sequences like light to dark or bring all the greens together or all the reds together as there could be about 300 items in the final filtered query out of the 1000,s of total items in the colour value table

Item	Colour	Red	Green	Blue
Red Item		255	0	0
Green Item		0	255	0
Blue Item		0	0	255
Black Item		0	0	0
Grey Item		150	150	150

I have seen the colour sorting work in a spreadsheet that I have manually created but I need to turn it into a usable App like tool that can import data from other systems so that it can be a day to day tool for understanding what colour items need to be made and how to easier plan manufacture schedules based on daily requirements. So the use of the different colours is a fairly accurate visual representation of each item as the name of the item is not very informative without going and looking for an example of the item in rack etc.

[June7]

Would Conditional Formatting satisfy your requirements?

[isladogs]

Perhaps you can make use of my Colour Selector/Converter http://www.mendipdatasystems.co.uk/c...ter/4594450413
You can assign an unlimited number of different colours to each of your chosen categories

[davegri]

Could you work with this:



GreeeColors-davegri-v01.zip

[Micron]

You can make a continuous form look like a datasheet, but backcolor property has no effect on a datasheet. However as June7 might be saying, cf should work on a datasheet if there are not too many conditions. I forget what the limit is; maybe 3?

[June7]

CF has a limit of 50 rules as of Access 2010.

[JohnGeeee]

Thank you all.
I think there will be far too many different item colours for conditional formatting.
Davgri - that looks interesting. I will have a play with how you have done it and see if I can get that method to work. Many Thanks

[davegri]

Thank you all.
I think there will be far too many different item colours for conditional formatting.
Davgri - that looks interesting. I will have a play with how you have done it and see if I can get that method to work. Many Thanks

Glad to help. If you want to investigate this route further, you can PM me.

[JohnGeeee]

Hi All.
Sorry it has been a few week since had a chance to play with this RGB function request again. Thanks to Davegri his suggestion works in Single Form view once I understood where to put the code in the On Current form property ;-) Now I want it to work in Continuous Forms view. At the moment the field colour just shows the same colour of the record I focus on using the form navigation buttons in all of them when I scroll down the continuous form. I will continue to play unless someone has a hint for me ;-)

[davegri]

That's the way continuous forms work. I don't know a way to overcome that.

[June7]

Programmatically changing property of control affects ALL instances of control in form.

And that's why Conditional Formatting was developed.

Can be done in report using Format event.