Help with Conditional Formatting

[jodyblau]

I'm not certain that what I am trying to do is possible; in any event I
haven't been able to figure it out. Here is what I am trying to do:

I have one table that has a list of cases I'm working on. I have a
second table that contains a list of tasks to be performed and relates
to the Case_List through a Case_ID.

So currently, I can bring up a form that has a list of all my cases, I
can then select a case and it opens a new form that displays all of the
tasks that need to be accomplished for that given case.

What I would like to do is this: When I open the form containing the
list of cases, I run through all of the tasks for each Case_ID, and if
all of the tasks have been marked "Completed" (via checkbox field in
the Task_List table), Then I want to set the background of the
textboxes for that case to RED.

So what I haven't been able to figure out how to do is to change the
formatting of a record based upon a condition that doesn't related to
the fields of that record.

I'm not certain if I am explaining myself very well, so let me post my
code so far and perhaps you can see what I am trying to do:
Private Sub Check_Active_Tasks()
On Error GoTo Err_Check_Active_Tasks
Dim fcd As FormatCondition
Dim txt As TextBox
Dim rs1 As DAO.Recordset
Dim myCount As Long
Dim myBool As Boolean

Me.Recordset.MoveFirst
For i = 0 To Me.Recordset.recordCount - 1
Set rs1 = CurrentDb.OpenRecordset("Select * from Task_List
where Case_ID =" & Me.Recordset!Case_ID, dbOpenDynaset)
If rs1.EOF = False Then
rs1.MoveLast
End If
myCount = rs1.recordCount
If rs1.EOF = False Then
rs1.MoveFirst
End If
myBool = False

For X = 0 To myCount - 1

If rs1!Completed = True Then 'this case does have an active
task
myBool = True
Exit For
End If

If rs1.EOF = False Then
rs1.MoveNext
End If

If myBool = False Then 'then we didn't have an active task
and need to do conditional formatting
Set txt = Case_ID
With txt
'SINCE THE TASK LIST FOR THIS CASE DID NOT HAVE ANY
TASKS WHICH WERE NOT MARKED COMPLETED,
'I WANT TO SET THE Case_ID textbox background
formatting to red (but only for this record)
'I suspect I am approaching this from the wrong way;
any suggestions?
End With

End If

Next 'next record

If Me.Recordset.EOF = False Then
Me.Recordset.MoveNext
End If
Next 'next record

Exit_Check_Active_Tasks:
Exit Sub
Err_Check_Active_Tasks:
MsgBox Err.Description
Resume Exit_Check_Active_Tasks

End Sub

I would appreciate any suggestions on how to approach this or what my
alternatives are.

Thanks,

Jody Blau

[Anthony England]

"jodyblau" <jo******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I'm not certain that what I am trying to do is possible; in any event I
haven't been able to figure it out. Here is what I am trying to do:

I have one table that has a list of cases I'm working on. I have a
second table that contains a list of tasks to be performed and relates
to the Case_List through a Case_ID.

So currently, I can bring up a form that has a list of all my cases, I
can then select a case and it opens a new form that displays all of the
tasks that need to be accomplished for that given case.

What I would like to do is this: When I open the form containing the
list of cases, I run through all of the tasks for each Case_ID, and if
all of the tasks have been marked "Completed" (via checkbox field in
the Task_List table), Then I want to set the background of the
textboxes for that case to RED.

So what I haven't been able to figure out how to do is to change the
formatting of a record based upon a condition that doesn't related to
the fields of that record.

I'm not certain if I am explaining myself very well, so let me post my
code so far and perhaps you can see what I am trying to do:
Private Sub Check_Active_Tasks()
On Error GoTo Err_Check_Active_Tasks
Dim fcd As FormatCondition
Dim txt As TextBox
Dim rs1 As DAO.Recordset
Dim myCount As Long
Dim myBool As Boolean

Me.Recordset.MoveFirst
For i = 0 To Me.Recordset.recordCount - 1
Set rs1 = CurrentDb.OpenRecordset("Select * from Task_List
where Case_ID =" & Me.Recordset!Case_ID, dbOpenDynaset)
If rs1.EOF = False Then
rs1.MoveLast
End If
myCount = rs1.recordCount
If rs1.EOF = False Then
rs1.MoveFirst
End If
myBool = False

For X = 0 To myCount - 1

If rs1!Completed = True Then 'this case does have an active
task
myBool = True
Exit For
End If

If rs1.EOF = False Then
rs1.MoveNext
End If

If myBool = False Then 'then we didn't have an active task
and need to do conditional formatting
Set txt = Case_ID
With txt
'SINCE THE TASK LIST FOR THIS CASE DID NOT HAVE ANY
TASKS WHICH WERE NOT MARKED COMPLETED,
'I WANT TO SET THE Case_ID textbox background
formatting to red (but only for this record)
'I suspect I am approaching this from the wrong way;
any suggestions?
End With

End If

Next 'next record

If Me.Recordset.EOF = False Then
Me.Recordset.MoveNext
End If
Next 'next record

Exit_Check_Active_Tasks:
Exit Sub
Err_Check_Active_Tasks:
MsgBox Err.Description
Resume Exit_Check_Active_Tasks

End Sub

I would appreciate any suggestions on how to approach this or what my
alternatives are.

Thanks,

Jody Blau

Provided you don't need the list of cases to be editable from this
particular form (and normally you wouldn't if it's just displaying a list of
cases, then the key to this is to include two columns which show the number
of tasks for each case and the number of these which have been completed.
These columns can be shown to the user or not depending on your preference.
If you create a query, something like that shown below, then you can simply
set the conditional formatting manually to say if the number of tasks = the
number of completed tasks then show it as red. However, you may want to
make a special case for when both of these numbers are zero.

SELECT
(SELECT COUNT(*) FROM Task_List WHERE
Task_List.Case_ID=Case_List.Case_ID) AS AllTasks,
(SELECT COUNT(*) FROM Task_List WHERE
Task_List.Case_ID=Case_List.Case_ID AND
Task_List.Completed=True) AS CompleteTasks,
Case_List.*
FROM Case_List

[jodyblau]

Thanks!
Jody Blau