Change forecolor of text box depending on query field

I have a sub-report that has one text box.  The control source for the text box comes from a query.  The query includes a field named ForeColor.  When the sub-report prints I want the color of the text being printed to be the same as ForeColor.  So for example, if the query ForeColor indicates Blue, I want the textbox to print blue.  Or if the query ForeColor indicates Red, I want the textbox to print red.  

Make sense and how do I do this with VBA code?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
In the detail sections Format event, use code similar to:

Private Sub Detail_Format

     me.txtFieldName.ForeColor = me!ForeColor

End Sub

Open in new window

Mark EdwardsChief Technology OfficerCommented:
Be aware that simply the name of the color won't do it.  You need to add "vb" to the beginning of your forecolor text value.

Private Sub Detail_Format
     me.txtFieldName.ForeColor = “vb” & me!ForeColor
End Sub

Open in new window

i.e. "vbBlue", not just "Blue"  (I tested it)

Also, the color shade you get might not be the one you want.  You'll need to be willing to put up with some funky shades.  If you don't like them, you'll need to build a code process that gives you the color you want for the forecolor text value.

Also be aware that there might not be a "vb" color constant for all of the values in the forecolor field.
SteveL13Author Commented:
Am getting "type mismatch" with both suggestions.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Mark EdwardsChief Technology OfficerCommented:
Sorry.  The constant is vbBlue, not "vbBlue"  You'll need to create a code process to provide the proper forecolor constant or RGB() value for the value in the Forecolor field.
SteveL13Author Commented:
Sorry.  I don't understand.
Mark EdwardsChief Technology OfficerCommented:
The way you set the forecolor is with a constant (i.e. vbBlue, vbYellow, etc.), an RGB() function (RGB(125,125,125)) or a hex value (which I never use.)

The values in your Forecolor field are text.  You can't set a forecolor with a text value, so you'll need something like:
Select Case me.Forecolor
  Case "Blue"
	Me.txtControlname.forecolor=vbBlue
  Case “Yellow”
	Me.txtControlname.forecolor=vbYellow
‘….etc.
End Select

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
If ever you don't like the preset vb colors, they can be a little harsh, you can use a case statement just like Mark suggests, but instead specify the rgb color you want
Me.txtControlname.forecolor = RGB(87,171,83)

Open in new window

this will allow you to get whatever color you desire.

Another alternative is to use HTML colors and for that you would do something more like
Me.txtControlname.forecolor = Val("&H" & "FFFFFF")

Open in new window


Either of the above will give you much more control on the exact color you want, instead of just a few preset ones.  You can use google for HTML and RGB color charts to make your life easier to determine the values you might wish to use if you are not already familiar with these color standards.
Dale FyeOwner, Developing Solutions LLCCommented:
Steve,

what values are you storing in the ForeColor field of your table?  if they are the results of an RGB function call, then the code I gave you should work.  But it would be helpful to know the name of the control you are attempting to update, and possibly give us a couple of records of data, so that we can see what you are working with.

Dale
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.