473,231 Members | 1,519 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

Help with Conditional Formatting

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

Feb 18 '06 #1
2 2315

"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
Feb 18 '06 #2
Thanks!
Jody Blau

Feb 18 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Wayne Aprato | last post by:
I have a simple database which was originally written in Access 97. When converted to Access 2000 file format it ran flawlessly in Access 2002. I've just tried to run it in Access 2003 and I am...
4
by: Bradley | last post by:
I have an A2000 database in which I have a continuous form with a tick box. There is also a text box with a conditional format that is based on the expression , if it's true then change the...
8
by: Dimitri Furman | last post by:
Given: Access 2002/2003 A subform in datasheet or continuous view, placed on a tab page (this last may or may not matter) Conditional formatting applied to some controls on the subform - format...
2
by: Von Bailey | last post by:
I have a form where the conditional formatting is set on some fields to bold if certain conditions are met. However, when the conditions are met some of the data that is to bold is either not...
5
by: Andrew Chanter | last post by:
Does anyone know a way you can use conditional formatting to create a banded style view as is commonly seen on the internet. (In othe words the first record appears on a gray background, the 2nd...
1
by: GGerard | last post by:
Hello Is there a way to use a variable in the Conditional Formatting of a Textbox? Example : I want the background of a textbox in a continuous form to change color when the value of...
8
by: Typehigh | last post by:
I have many text fields with conditional formatting applied, specifically when the condition is "Field Has Focus". Without any events associated with the fields the conditional formatting works...
4
by: midlothian | last post by:
Hello, I have conditional formatting set up on a subform based on a calculated value in the underlying query. For instance, if Sales are >$1000, the query displays "Yes," otherwise it displays...
2
by: Filips Benoit | last post by:
Dear All, Access 2003 adp on SQL_server 2005 A continious form showing 1 month based on table 'CALENDAR_MONTH_GRID' and fill with a SP. Fields: Companyname, Day1, day2, etc. The value in the...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.