| re: Help with Conditional Formatting
"jodyblau" <jodyblau@gmail.com> wrote in message
news:1140244640.878211.202070@z14g2000cwz.googlegr oups.com...[color=blue]
> 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[/color]
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
| 
