Setting up conditional fore color in access form

Megin
Megin used Ask the Experts™
on
I have a form with a lot of different fields. I want the fore color of all of the items on the form to turn grey when one of the combo boxes is set to "off project. I have tried to do this as a macro, but it isn't working. I would like to do this in vba code. Can someone tell me how?

Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
using the afterupdate event of the combo box

private sub combo_afterupdate()

if me.combo="off project" then
   me.text1.forecolor=rgb(204, 204,204)

   me.textN.forecolor=rgb(204, 204,204)

else

   me.text1.forecolor=rgb(0, 0,0)

   me.textN.forecolor=rgb(0, 0,0)


end if

end sub

Author

Commented:
When I use this code, do I left those spots the terms "text1" and "textN" as they are, or am I using the names of the fields on the form? Also, I realize that rgb (204, 204, 204) must be what is making the font grey, but how does it do that?

Thank you!
Top Expert 2016

Commented:
you will replace those "text1" and "textN"  with the actual names of the controls..
and add all the other controls from your form..
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
But lets be clear:
"I have a form with a lot of different fields. I want the fore color of all of the items on the form to turn grey when one of the combo boxes is set to "off project. "

So you still want them to be able to edit the Grey fields?
Or, ...is your real question here how do "Disable" the controls?
(standard "greyed out "controls that you cannot edit)

You can do this with Conditional formatting.
In design view of the form, Select all the controls you want "disabled" (except the combobox obviously)
Then click the Conditional format icon on the ribbon and do this:
Condition1:
Expression Is  [Status]="Off Project"
...then click the "enable" Button


Screenshot and sample db attached
screenBilling.mdb
Top Expert 2016

Commented:
<I would like to do this in vba code. >
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
cap,

I know what the OP asked for, so no need for the bold text.
I chose to post anyway, in case they were interested in alternative.

We all know that sometimes a person my think they need VBA, ...but CF may be an option because:
1. It runs faster than VBA
2. If this was a report, most VBA wont run in Report View.

As always, if you have an issue with any post I make, you can email me directly

As long as I have been here, you should know my knowledge level


JeffCoachman

Author

Commented:
I don't want to disable, I just want the color of the font to change to grey.

I would do this with the conditional formatting option you are suggesting, but I already have three conditions listed and it won't let me add any more that way.

I want to do this in the code.

Again, when I plug in that code, which appears to me to be something that should work, it doesn't do anything.
Top Expert 2016

Commented:
post the codes that you are using

Author

Commented:
Ooops!

Sorry. I thought I had already posted that code. Here it is:

Private Sub Combo41_AfterUpdate()
If Me.Combo41 = "Off project" Then
    [Me].[First Name].ForeColor = RGB(204, 204, 204)

    [Me].[Last Name].ForeColor = RGB(204, 204, 204)
   
Else

   [Me].[First Name].ForeColor = RGB(0, 0, 0)

   [Me].[Last Name].ForeColor = RGB(0, 0, 0)
     


   
   
   
End If

End Sub
Top Expert 2016

Commented:
what is the rowsource of the combo41 ?  post it here..

Author

Commented:
"On Project";"Off Project";"Drop-In";"Visitor";"Off Site DB Team";"Corson Office";"1051 Building";"Off Site Staff"
Top Expert 2016

Commented:
try this, copy and paste


Private Sub Combo41_AfterUpdate()
If Me.Combo41 = "Off Project" Then
    [Me].[First Name].ForeColor = RGB(204, 204, 204)

    [Me].[Last Name].ForeColor = RGB(204, 204, 204)
   
Else

   [Me].[First Name].ForeColor = RGB(0, 0, 0)

   [Me].[Last Name].ForeColor = RGB(0, 0, 0)
   
End If

End Sub

test the codes by selecting different items from the combo box, observed if the fore color change..

Author

Commented:
My error message.It gives me an error message. Run-time error '2465: Staff Information can't find the field 'I' referred to in your expression.

It looks kind of odd, so I am including the picture here.
Top Expert 2016

Commented:
click on Debug and see which code will be highlighted,
i don't think it is coming from the code i posted above..

Author

Commented:
This is what is highlighted:
 

[Me].[First Name].ForeColor = RGB(204, 204, 204)
Top Expert 2016

Commented:
check if the name of the control ( textbox ) is really [First Name].

replace it with the correct control name

Author

Commented:
The name of the text box is "First Name."

I know it isn't a good name for a text box, but I started building this database when I didn't know any better.
Top Expert 2016
Commented:
try removing the [ ] from Me.

Private Sub Combo41_AfterUpdate()
If Me.Combo41 = "Off Project" Then
    Me.[First Name].ForeColor = RGB(204, 204, 204)

    Me.[Last Name].ForeColor = RGB(204, 204, 204)
   
Else

   Me.[First Name].ForeColor = RGB(0, 0, 0)

   Me.[Last Name].ForeColor = RGB(0, 0, 0)
   
End If

End Sub



save, then click on DEBUG >Compile

make sure that there are no errors raised.

Author

Commented:
Thank you, thank you, thank you!!!

It worked!!!!!

You are magnificent, Cap!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial