color changing box on form.

[travismp]

Access 2000.
Main Form: frm_ESKER_ENTRY_TERRI (Standard Form View)
Sub From: frm_ESKER_HISTORY (Opens in Datasheet view)

The main form will bring up an employee based off of their social. Then the subform will list all of the jobs they have ever done. In the subform there is a field called [TestResult]

I added a box and named it [BoxAlert] to my main form. I want to add some VBA that will look at the records in the subform and if any of the records are not "NEGATIVE" in [TestResult] then the box will change colors.

So if employee Steve Willson has 5 jobs & one of the jobs has a [TestResult] of "OPEN" my [BoxAlert] will change from a transparent color to a bright red. This will alert me to look into his history quickly.

Thanks, hope this is clear.

I added this to the main form, but I am getting an error:



Private Sub Form_Current()
Dim Yellow As Long, Gray As Long
Yellow = RGB(250, 250, 210)
Gray = RGB(192, 192, 192)
If Forms![frm_ESKER_HISTORY]![TestResult] = NEGATIVE Then
Me![BoxAlert].BackColor = Yellow
ElseIf Forms![frm_ESKER_HISTORY]![TestResult] = POSITIVE Then
Me![BoxAlert].BackColor = Gray
End If
End Sub

 

[travismp]

I then tried this:

Private Sub Form_Current()
Dim Yellow As Long, Gray As Long
Yellow = RGB(250, 250, 210)
Gray = RGB(192, 192, 192)
'this script will change a box color on current form

    If Forms![frm_ESKER_HISTORY]![TestResult] = "Positive" Then
        Me![BoxAlert].BackColor = Yellow
    ElseIf Forms![frm_ESKER_HISTORY]![TestResult] <> "Positive" Then
        Me![BoxAlert].BackColor = Gray
    End If

End Sub

With no luck. It does not like my first line:
If Forms![frm_ESKER_HISTORY]![TestResult] = "Positive" Then

 

[pbaldy]

Is there a reason you don't use Conditional Formatting (Format/Conditional Formatting)? This is exactly what it does.

 

[travismp]

because I have no idea what that is. If that is a good solution I can try to find something...

 

[travismp]

OK I see what conditional formatting is. One problem with that is that only changes the one field. I need it to do something more noticable. Can it change that entire record not just the one column the condition is set on?

 

[pbaldy]

Couple of things I've done are to apply the same format to all the controls, or put one textbox behind them all and set them to transparent, and apply the format to that textbox. Depends on the look you want.

 

[travismp]

Paul,

I played with that some and researched some. That option is not going to work for me. I will need to make this VB work. My subform is in datasheet view. An employee may have 100 cases total, but only the most recent 5 will show on screen due to size restrictions. If their 18th record is not NEGATIVE we will never even notice. The conditional formatting will change the color of that record, yet we will never see it because it will not show on screen.

The box actually takes up the entire screen and is behind all of the other fields. I want the entire screen to go RED to show there is an issue.

Thanks so far... any other thoughts ... anyone??? thanks.

 

[pbaldy]

Ah, I think I misunderstood the question. What is the source of the form & subform? You may be able to do a DCount or something to find out if there are any records in the source of the subform for that employee with Negative in that field.

 

[travismp]

there are two sources

tbl_temp for the main form.
tbl_TESTS for the subform.

 

[pbaldy]

How about something like this:

If DCount("*", "tbl_TESTS", "EmployeeID = " & Me.EmployeeID & " And TestResult = 'Negative'") > 0 Then

Substituting your field names if I've botched them, and adding the single quotes around the employee field if it's text.