Conditional formatting on form

[StephenB]

I'm trying to get the backcolor on a field to change in response to an entry on another field. I have [Date_Submitted] set to change to red when [Submitter} is "Stephen Black", else [Date_Submitted] is green. (This may not make sense, but I don't have the db I'm going to use this on ready, so I'm trying to sort this out on an old db.) I'm using:

Private Sub Form_Timer()
If Me.[Submitter] = "Black, Stephen" Then
Me.Date_Submitted.BackColor = 255
Else: Me.Date_Submitted.BackColor = 8421376
End If
End Sub

This works fine. What I would like to do though is for the code to read a table instead of just "text". This would make it easier on my to add to the list of names that meet the condition. Can I get some help on how I should replace "Black, Stephen" with a table? Also, further down the road, I'd like to be able to identify "Hot Words" in a freeform text or memo field. Right now what I'm using can only identify the condition if the condition is meet exactly. I'd like to have the condition met if a specific word in a paragraph is entered. If the hotword were "picnic", I would like the condition met in sentences such as "The company picnic is July 29" and "The company picnic has been postponed until August 29". Any suggestions?

TIA

 

[StephenB]

Still hoping someone can offer some advice on the above, however since I'm starting off with small amounts of data, I'm willing to add other names along with mine onto the code. I'm willing to add "Doe, John" to:

If Me.[Submitter] = "Black, Stephen" Then

Problem is I can't figure that one out either. Nothing I've tried works (except for the code written above).

Appreciate any suggestions.

[This message has been edited by StephenB (edited 05-09-2002).]

 

[Fizzio]

You mention 'reading a table'. What exactly do you mean by this? Do you mean read the values in a table? Even if you do, you will need an if...then statenment somewhere (or an equivalent) to change the back colour.
If you want to colour the box on a number of names then create a field in the table where the names are stored with a yes/no field to show something that will allow access to colour the fields a different colour then use an SQL statement to pull a match from the table.
Hope this is clear

 

[StephenB]

Thanks for responding, Fizzio.

What I mean by "reading a table" is this: currently, using the code (above in the first entry), I'm able to make field A change back color when field B is populated with my name (Black, Stephen). Ideally, I would like to replace "Black, Stephen" in the above statement with something like "

![tbl_submitter2]![submitter]" so the back color on field A changes whenever field B is populated with any name entered in the table.

Now that I think of it, I would probably want it to read a query base on the original [tbl_submitter] table. So it would probably look more like:

Private Sub Form_Timer()
If Me.[Submitter] = [query]![qry_tbl_submitter]![submitter] Then
Me.Date_Submitted.BackColor = 255
Else: Me.Date_Submitted.BackColor = 8421376
End If
End Sub

This however is not working. When I compile, I get no error message, but when I go back to the form, I get "Run Time Error 2465: Microsoft can't find the field "query" referred to in your expression" with a debug option.

Any suggestions on how I can go about this? At this point, I'm even willing to write each name into the code such as

If Me.[Submitter] = "Black, Stephen" or "Doe, John" Then

That however is giving me a "Syntax Error" when I compile.

 

[Fizzio]

There are two ways to look at this. Do you want to change the textbox if there is anthing in the textbox or only when there is a match in your table.
The first option is the easiest one but I suspect you want the second.

I would use the code on the afterupdate of your name textbox to determine if there is a match in the underlying table. How you do it depends if you want it to change if there is just a match, or a match that fits certain criteria eg match Black, Stephen and Doe, John regardless or only those who meet a certain criterie for example Black, Stephen (Manager) would match but Doe, John (Production Worker) would not.

HTH

 

[StephenB]

Thanks, Fizzio;

At this point, I only need it to read the table, but I've also been looking for a way to perform the second option. This would give me the "hotword" functionality I described above. But I haven't gotten that far yet. If you can recommend something to do that, I'd gratefully take it, but I'm still trying get it to read the table exactly first.

Thanks for your time.

 

[Fizzio]

I'll compile and send you a demo OK

 

[StephenB]

OoRah! Fizzio! Much obliged!