Color text field based on the value of a combo box (1 Viewer)

[Harry Paraskeva]

Hello to everyone,

I have a problem that is probably the result of lack of of knowledge in Access VBA. The situation is the following:

I have a table named ColorValues with the following three fields:
- ID
- MunsellCode
- RGBValue

30 fields from another table called Color_Analysis are linked to the above with one to many relationships (I've already thought of other ways to normalize data, but this is the most efficient, so no need to go into this topic).

Now, I've constructed a form for Color_Analysis and all 30 fields recording color are included as combo boxes bounded on the ID in ColorValues and displaying the MunsellCode for the user. I've also created unbounded text fields next to the combo boxes and want to use them to display the color that the user selected in the ComboBox. The question is how do I do this?

Essentially I need a piece of code that picks up the value of the combo box (this is essentially the ID in ColorValues), looks up that value in the ID column of the ColorValues table and uses the corresponding RGBValue of the same table as the .BackColor for the unbounded text field.

If anyone could help, it would be awesome! Thank you in advance for even looking.

 

[JHB]

In the After Update event for a combobox, set the backcolor for the corresponding (text) control, to the backcolor chosen in the combobox.

 

[Harry Paraskeva]

But the comboboxes are not coloured. They simply allow the user to select the MunsellCode from the ColorValues table (there is a total of 340 options). They register as a number the ID of the MunsellCode and display as a text the values from the MunsellCode column.

Also I've already tried a quite long Case procedure (about 30k characters), manually entering the RGBs for each ID, but this only works for the After Update event. If I try to gather all the Case procedures for the Form On Load event, Access gives an error for Long Procedure (30k x 30 fields = 900k, Access limit is 64k per procedure).

What I need is for the unbounded text box to lookup the ID registered in the combo box and use the value from the RGBValue in the ColorValues table as its .backcolor property. The RGBValue field is formatted in the way Access expects an RGB value triplet for use in VBA.

Thank you for your time!

 

[JHB]

Could you post some sample from the ColorValues table?

 

[Harry Paraskeva]

Could you post some sample from the ColorValues table?

This is what it looks like:

ID	MunsellValue	RGBValue
1	5R 8/1	        255, 255, 255
2	5R 8/2	        218, 194, 203
3	5R 8/3	        255, 255, 255
4	5R 8/4	        239, 188, 194

My combo boxes in the Form display the MunsellValue, but are bound to the ID. I would like the .backcolor property of the unbounded text field next to the combo box to use the RGBValue assigned to the ID.

If you could help that would be great!

 

[JHB]

Try the attached database, open the form pick the first value in the combobox.

 

[Harry Paraskeva]

Try the attached database, open the form pick the first value in the combobox.

Okay this brilliant workaround significantly reduces the size of the process and can therefore be included in the Form Load event too!

Thank you very much for your help!!!

 

[JHB]

You're welcome, good luck with your project.

 

[Harry Paraskeva]

I've also thrown in a Not IsNull if procedure in order to avoid an error for null values in the text box. Thank again JHB!