Solved Changing the colour of a textbox on a continuous form based on a combobox

[Sam Summers]

I have tried so many things to achieve this and no luck.

I am trying to conditionally change the backcolour of a textbox on a continuous form based on a combobox value on the same form.

I tried conditional formatting and also vba in the on load event of the form but nothing works?

Any ideas?

Many thanks in advance

 

[Ranman256]

Make the combo box have 2 columns,
Item to pick and the color.

In the afterupdate event:
Me.backcolor = cboBox.column(1)

Note: in vb,columns begin with zero.

 

[MajP]

Above will not work. It cannot be done in a continuous form using vba. Must use CF

backcolour of a textbox on a continuous form

Expression is: [CboBoxName] = "SomeValue"

 

[Sam Summers]

Above will not work. It cannot be done in a continuous form using vba. Must use CF

Expression is: [CboBoxName] = "SomeValue"

Its not the combobox i want to change the colour of. I want to change the colour of the textbox based on what is selected in the combobox if its possible. Basically i am reducing the combobox to nothing and making the textbox display the contents of the combobox so that the user isn't tempted to try and select from the drop down list.

Hope that makes sense?

 

[MajP]

I want to change the colour of the textbox based on what is selected in the combobox if its possible.

Yes that is exactly why I used "Expression Is" instead of "Field Value Is" .

 

[Sam Summers]

Yes that is exactly why I used "Expression Is" instead of "Field Value Is" .View attachment 90166

I have this now Expression [Status].[Column](2)="In Service" but still not working for some reason?

 

[MajP]

Is the value you are looking for really the 3rd column (0 based) and it is not the bound field? Make sure status is the name of the combo. Also no [] around Column. Only needed around the control name.

 

[Sam Summers]

Is the value you are looking for really the 3rd column (0 based) and it is not the bound field? Make sure status is the name of the combo. Also no [] around Column. Only needed around the control name.

Yes done all that and checked that i am referring to the right column and still nothing? Very strange

 

[MajP]

See demo

 

[Sam Summers]

See demo

View attachment 90168

Ok Thank you. I will have a play about with that.

 

[Sam Summers]

See demo

View attachment 90168

Right i think i have found the problem. Its because i want the textbox to display the text in the combobox with this - =[Status].[Column](2)
'Status' being the name of the combobox.

It must be conflicting with the conditional formatting expression?

 

[MajP]

Even if Status is a reserved word (which it is not), it would not matter since it is inside [].

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

docs.microsoft.com

You do not need the [] around column, but I tested it with them and it works.
Can you post your db or a sample?

 

[Sam Summers]

Even if Status is a reserved word (which it is not), it would not matter since it is inside [].

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

docs.microsoft.com

You do not need the [] around column, but I tested it with them and it works.
Can you post your db or a sample?

Here it is and thank you

 

[MajP]

You need to get rid of the table lookups. These are BAD! You are also creating so much extra work in what you are attempting. See the simple query and simple formatting.

The Access Web - The Evils of Lookup Fields in Tables

You make combo lookups on forms not in tables. Yes I know that MS thought they were doing people a favor, but they just made it easy to hang yourself.

 

[MajP]

FYI. One more thing. Conditional formatting does not work well with transparent controls. In order to change the back color need to set to Normal. I made it look transparent, by giving the background color same as the form color.

 

[Sam Summers]

FYI. One more thing. Conditional formatting does not work well with transparent controls. In order to change the back color need to set to Normal. I made it look transparent, by giving the background color same as the form color.

Thank you. I note all your points and will have a go at putting it into action. I am slowly learning