conditional formatting using more than 1 control (w/ datasheet vie

G

Guest

hi,

in my case, i have a sub-form which the user views as a datasheet. on it
there are three controls that are relevant.

a) Outcome
b) Date_on_List
c) Time_on_List

Time_on_List is computed on the fly for every record in the underlying table.

what is desired is the following behavior, namely when the value of Outcome
= "Pending" and the value of "Time_on_List" >= 30 we'd like to see the value
of "Time_on_List' displayed with a red background using bold yellow letters.

perhaps i ought to add the fact that using the "Conditional Formatting"
feature of my A2K installation, i've formatted Outcome to dispaly the value
in bold red letters whenever Outcome = "Pending".

the wall we seem to be running into is my understanding that "Conditional
Formatting" seems engineered to work with just one Control at a time.

anyone?
 
G

Geof Wyght

Ted,
I think I have it! Thanks to Access 2000 Developer's
Handbook by Getz, Litwin, Gilbert:

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
..Delete
Set fcd = .Add(acExpression, , "[outcome]=""pending"" And
[Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

Geof.
 
G

Guest

hi geoff,

well....

here's the code i have running for "Screening Log (DS View)" which is the
subFrm

Private Sub Form_Current()
Dim lngRed As Long
Dim lngYellow As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim Time_on_List As Integer

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

Me.Time_on_List = DateDiff("d", Date_on_List, Now())

If Me.Time_on_List >= 30 And Me.Outcome = "Pending" Then
Me.Time_on_List.ForeColor = lngYellow
Me.Time_on_List.BackColor = lngRed
Else
Me.Time_on_List.ForeColor = lngBlack
Me.Time_on_List.BackColor = lngWhite
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
' Set fcd = .Add(acExpression, , "[outcome]=""pending"" And
' [Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

on opening it, this results in 'Runtime Error 91' which means 'Object
variable or With block variable not set'.

does this mean something to you?

regards,

ted



Geof Wyght said:
Ted,
I think I have it! Thanks to Access 2000 Developer's
Handbook by Getz, Litwin, Gilbert:

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
..Delete
Set fcd = .Add(acExpression, , "[outcome]=""pending"" And
[Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

Geof.
-----Original Message-----
hi,

in my case, i have a sub-form which the user views as a datasheet. on it
there are three controls that are relevant.

a) Outcome
b) Date_on_List
c) Time_on_List

Time_on_List is computed on the fly for every record in the underlying table.

what is desired is the following behavior, namely when the value of Outcome
= "Pending" and the value of "Time_on_List" >= 30 we'd like to see the value
of "Time_on_List' displayed with a red background using bold yellow letters.

perhaps i ought to add the fact that using the "Conditional Formatting"
feature of my A2K installation, i've formatted Outcome to dispaly the value
in bold red letters whenever Outcome = "Pending".

the wall we seem to be running into is my understanding that "Conditional
Formatting" seems engineered to work with just one Control at a time.

anyone?

.
Click to expand...
Click to expand...
 
G

Geof Wyght

Yeah. You commented out the Set statement!
Dump the If statement in the current event. The format
condition object is going to replace it.
Geof.
-----Original Message-----
hi geoff,

well....

here's the code i have running for "Screening Log (DS View)" which is the
subFrm

Private Sub Form_Current()
Dim lngRed As Long
Dim lngYellow As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim Time_on_List As Integer

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

Me.Time_on_List = DateDiff("d", Date_on_List, Now())

If Me.Time_on_List >= 30 And Me.Outcome = "Pending" Then
Me.Time_on_List.ForeColor = lngYellow
Me.Time_on_List.BackColor = lngRed
Else
Me.Time_on_List.ForeColor = lngBlack
Me.Time_on_List.BackColor = lngWhite
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
' Set fcd = .Add(acExpression, , "[outcome]=""pending"" And
' [Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

on opening it, this results in 'Runtime Error 91' which means 'Object
variable or With block variable not set'.

does this mean something to you?

regards,

ted



Geof Wyght said:
Ted,
I think I have it! Thanks to Access 2000 Developer's
Handbook by Getz, Litwin, Gilbert:

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
..Delete
Set fcd = .Add(acExpression, , "[outcome]=""pending"" And
[Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

Geof.
-----Original Message-----
hi,

in my case, i have a sub-form which the user views as
Click to expand...
a
datasheet. on it
there are three controls that are relevant.

a) Outcome
b) Date_on_List
c) Time_on_List

Time_on_List is computed on the fly for every record
Click to expand...
in
the underlying table.
what is desired is the following behavior, namely when the value of Outcome
= "Pending" and the value of "Time_on_List" >= 30 we'd like to see the value
of "Time_on_List' displayed with a red background
Click to expand...
using
bold yellow letters.
perhaps i ought to add the fact that using the "Conditional Formatting"
feature of my A2K installation, i've formatted Outcome
Click to expand...
to
dispaly the value
in bold red letters whenever Outcome = "Pending".

the wall we seem to be running into is my
Click to expand...
understanding
that "Conditional
Formatting" seems engineered to work with just one Control at a time.

anyone?

.
Click to expand...
Click to expand...
.
Click to expand...
 
G

Guest

by george, geoff, i think you got it that time :)

just a quick postmortem....

a) can i get it to give me yellow background when Time_on_List >= 30 in
addition to the red foreground

b) does your recent code have to co-exist with the On Current event property
code; my guess is probably so, but that only this part " Me.Time_on_List =
DateDiff("d", Date_on_List, Now())" needs to remain

c) if i start getting creative and want to display Time_on_List <30 with a
green foreground when Outcome = "Pending" can i toss that into the mix

d) looks like Getz' book would be a welcome addition to my one book Access
reference library (consisting of "Microsoft Access '97 Bible")

with best regards,

ted



Geof Wyght said:
Yeah. You commented out the Set statement!
Dump the If statement in the current event. The format
condition object is going to replace it.
Geof.
-----Original Message-----
hi geoff,

well....

here's the code i have running for "Screening Log (DS View)" which is the
subFrm

Private Sub Form_Current()
Dim lngRed As Long
Dim lngYellow As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim Time_on_List As Integer

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

Me.Time_on_List = DateDiff("d", Date_on_List, Now())

If Me.Time_on_List >= 30 And Me.Outcome = "Pending" Then
Me.Time_on_List.ForeColor = lngYellow
Me.Time_on_List.BackColor = lngRed
Else
Me.Time_on_List.ForeColor = lngBlack
Me.Time_on_List.BackColor = lngWhite
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
' Set fcd = .Add(acExpression, , "[outcome]=""pending"" And
' [Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

on opening it, this results in 'Runtime Error 91' which means 'Object
variable or With block variable not set'.

does this mean something to you?

regards,

ted



Geof Wyght said:
Ted,
I think I have it! Thanks to Access 2000 Developer's
Handbook by Getz, Litwin, Gilbert:

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
..Delete
Set fcd = .Add(acExpression, , "[outcome]=""pending"" And
[Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

Geof.
-----Original Message-----
hi,

in my case, i have a sub-form which the user views as a
datasheet. on it
there are three controls that are relevant.

a) Outcome
b) Date_on_List
c) Time_on_List

Time_on_List is computed on the fly for every record in
the underlying table.

what is desired is the following behavior, namely when
the value of Outcome
= "Pending" and the value of "Time_on_List" >= 30 we'd
like to see the value
of "Time_on_List' displayed with a red background using
bold yellow letters.

perhaps i ought to add the fact that using
the "Conditional Formatting"
feature of my A2K installation, i've formatted Outcome to
dispaly the value
in bold red letters whenever Outcome = "Pending".

the wall we seem to be running into is my understanding
that "Conditional
Formatting" seems engineered to work with just one
Control at a time.

anyone?

.
Click to expand...
.
Click to expand...
Click to expand...
 
G

Geof Wyght

Ted,
Intellisense will show you that you can use the FontColor
property. I guess that's what you mean by "foreground".
You're right about item b). I believe you can have up to 3
conditions for each control. Make them mutually exclusive,
otherwise Access will stop at the first true one. You're
cooking with gas!
Geof.
-----Original Message-----
by george, geoff, i think you got it that time :)

just a quick postmortem....

a) can i get it to give me yellow background when Time_on_List >= 30 in
addition to the red foreground

b) does your recent code have to co-exist with the On Current event property
code; my guess is probably so, but that only this part " Me.Time_on_List =
DateDiff("d", Date_on_List, Now())" needs to remain

c) if i start getting creative and want to display Time_on_List <30 with a
green foreground when Outcome = "Pending" can i toss that into the mix

d) looks like Getz' book would be a welcome addition to my one book Access
reference library (consisting of "Microsoft Access '97 Bible")

with best regards,

ted



Geof Wyght said:
Yeah. You commented out the Set statement!
Dump the If statement in the current event. The format
condition object is going to replace it.
Geof.
-----Original Message-----
hi geoff,

well....

here's the code i have running for "Screening Log (DS View)" which is the
subFrm

Private Sub Form_Current()
Dim lngRed As Long
Dim lngYellow As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim Time_on_List As Integer

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

Me.Time_on_List = DateDiff("d", Date_on_List, Now ())

If Me.Time_on_List >= 30 And Me.Outcome
Click to expand...
= "Pending"
Then
Me.Time_on_List.ForeColor = lngYellow
Me.Time_on_List.BackColor = lngRed
Else
Me.Time_on_List.ForeColor = lngBlack
Me.Time_on_List.BackColor = lngWhite
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
' Set fcd = .Add(acExpression, , "[outcome]
Click to expand...
=""pending""
And
' [Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

on opening it, this results in 'Runtime Error 91'
Click to expand...
which
means 'Object
variable or With block variable not set'.

does this mean something to you?

regards,

ted



:

Ted,
I think I have it! Thanks to Access 2000 Developer's
Handbook by Getz, Litwin, Gilbert:

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
..Delete
Set fcd = .Add(acExpression, , "[outcome]
Click to expand...
=""pending""
And
[Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

Geof.
-----Original Message-----
hi,

in my case, i have a sub-form which the user views
Click to expand...
as
a
datasheet. on it
there are three controls that are relevant.

a) Outcome
b) Date_on_List
c) Time_on_List

Time_on_List is computed on the fly for every
Click to expand...
record
in
the underlying table.

what is desired is the following behavior, namely when
the value of Outcome
= "Pending" and the value of "Time_on_List" >= 30 we'd
like to see the value
of "Time_on_List' displayed with a red background using
bold yellow letters.

perhaps i ought to add the fact that using
the "Conditional Formatting"
feature of my A2K installation, i've formatted
Click to expand...
Outcome
to
dispaly the value
in bold red letters whenever Outcome = "Pending".

the wall we seem to be running into is my understanding
that "Conditional
Formatting" seems engineered to work with just one
Control at a time.

anyone?

.


.
Click to expand...
Click to expand...
.
Click to expand...
 
G

Guest

geoff, for the sake of posterity, i'll just quickly sum up: i have set a
Conditional Format on "Outcome" having its value displayed in Bold Yellow
with a Red Backgrnd when'er it's = "Pending".

as for the remainder, the VBA code/look i settled upon is driven with the
following:

Option Compare Database

Private Sub Form_Current()

Me.Time_on_List = DateDiff("d", Date_on_List, Now())

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
Set fcd = .Add(acExpression, , "[Outcome]=""Pending"" And [Time_on_list]>=30")
fcd.BackColor = vbRed
fcd.ForeColor = vbYellow
fcd.FontBold = True
' End With
Set fcd = .Add(acExpression, , "[Outcome]=""Pending"" And [Time_on_list]<30")
fcd.BackColor = vbGreen
fcd.ForeColor = vbBlack
fcd.FontBold = True
End With
End With
End Sub

and i couldnadoneitwithoutya!!

bravo, well-done, you're a brainiac :)

all the best,

ted


Geof Wyght said:
Ted,
Intellisense will show you that you can use the FontColor
property. I guess that's what you mean by "foreground".
You're right about item b). I believe you can have up to 3
conditions for each control. Make them mutually exclusive,
otherwise Access will stop at the first true one. You're
cooking with gas!
Geof.
-----Original Message-----
by george, geoff, i think you got it that time :)

just a quick postmortem....

a) can i get it to give me yellow background when Time_on_List >= 30 in
addition to the red foreground

b) does your recent code have to co-exist with the On Current event property
code; my guess is probably so, but that only this part " Me.Time_on_List =
DateDiff("d", Date_on_List, Now())" needs to remain

c) if i start getting creative and want to display Time_on_List <30 with a
green foreground when Outcome = "Pending" can i toss that into the mix

d) looks like Getz' book would be a welcome addition to my one book Access
reference library (consisting of "Microsoft Access '97 Bible")

with best regards,

ted



Geof Wyght said:
Yeah. You commented out the Set statement!
Dump the If statement in the current event. The format
condition object is going to replace it.
Geof.
-----Original Message-----
hi geoff,

well....

here's the code i have running for "Screening Log (DS
View)" which is the
subFrm

Private Sub Form_Current()
Dim lngRed As Long
Dim lngYellow As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim Time_on_List As Integer

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

Me.Time_on_List = DateDiff("d", Date_on_List, Now ())

If Me.Time_on_List >= 30 And Me.Outcome = "Pending"
Then
Me.Time_on_List.ForeColor = lngYellow
Me.Time_on_List.BackColor = lngRed
Else
Me.Time_on_List.ForeColor = lngBlack
Me.Time_on_List.BackColor = lngWhite
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
' Set fcd = .Add(acExpression, , "[outcome] =""pending""
And
' [Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

on opening it, this results in 'Runtime Error 91' which
means 'Object
variable or With block variable not set'.

does this mean something to you?

regards,

ted



:

Ted,
I think I have it! Thanks to Access 2000 Developer's
Handbook by Getz, Litwin, Gilbert:

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
..Delete
Set fcd = .Add(acExpression, , "[outcome] =""pending""
And
[Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

Geof.
-----Original Message-----
hi,

in my case, i have a sub-form which the user views as
a
datasheet. on it
there are three controls that are relevant.

a) Outcome
b) Date_on_List
c) Time_on_List

Time_on_List is computed on the fly for every record
in
the underlying table.

what is desired is the following behavior, namely when
the value of Outcome
= "Pending" and the value of "Time_on_List" >= 30 we'd
like to see the value
of "Time_on_List' displayed with a red background
using
bold yellow letters.

perhaps i ought to add the fact that using
the "Conditional Formatting"
feature of my A2K installation, i've formatted Outcome
to
dispaly the value
in bold red letters whenever Outcome = "Pending".

the wall we seem to be running into is my
understanding
that "Conditional
Formatting" seems engineered to work with just one
Control at a time.

anyone?

.


.
Click to expand...
.
Click to expand...
Click to expand...
 
G

Geof Wyght

Ted,
Looks good. I learned a lot myself.
Geof.
-----Original Message-----
geoff, for the sake of posterity, i'll just quickly sum up: i have set a
Conditional Format on "Outcome" having its value displayed in Bold Yellow
with a Red Backgrnd when'er it's = "Pending".

as for the remainder, the VBA code/look i settled upon is driven with the
following:

Option Compare Database

Private Sub Form_Current()

Me.Time_on_List = DateDiff("d", Date_on_List, Now())

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
Set fcd = .Add(acExpression, , "[Outcome]=""Pending"" And [Time_on_list]>=30")
fcd.BackColor = vbRed
fcd.ForeColor = vbYellow
fcd.FontBold = True
' End With
Set fcd = .Add(acExpression, , "[Outcome]=""Pending"" And [Time_on_list]<30")
fcd.BackColor = vbGreen
fcd.ForeColor = vbBlack
fcd.FontBold = True
End With
End With
End Sub

and i couldnadoneitwithoutya!!

bravo, well-done, you're a brainiac :)

all the best,

ted


Geof Wyght said:
Ted,
Intellisense will show you that you can use the FontColor
property. I guess that's what you mean by "foreground".
You're right about item b). I believe you can have up to 3
conditions for each control. Make them mutually exclusive,
otherwise Access will stop at the first true one. You're
cooking with gas!
Geof.
-----Original Message-----
by george, geoff, i think you got it that time :)

just a quick postmortem....

a) can i get it to give me yellow background when Time_on_List >= 30 in
addition to the red foreground

b) does your recent code have to co-exist with the On Current event property
code; my guess is probably so, but that only this
Click to expand...
part "
Me.Time_on_List =
DateDiff("d", Date_on_List, Now())" needs to remain

c) if i start getting creative and want to display Time_on_List <30 with a
green foreground when Outcome = "Pending" can i toss
Click to expand...
that
into the mix
d) looks like Getz' book would be a welcome addition
Click to expand...
to
my one book Access
reference library (consisting of "Microsoft Access '97 Bible")

with best regards,

ted



:

Yeah. You commented out the Set statement!
Dump the If statement in the current event. The format
condition object is going to replace it.
Geof.
-----Original Message-----
hi geoff,

well....

here's the code i have running for "Screening Log (DS
View)" which is the
subFrm

Private Sub Form_Current()
Dim lngRed As Long
Dim lngYellow As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim Time_on_List As Integer

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

Me.Time_on_List = DateDiff("d", Date_on_List,
Click to expand...
Now
())
If Me.Time_on_List >= 30 And Me.Outcome = "Pending"
Then
Me.Time_on_List.ForeColor = lngYellow
Me.Time_on_List.BackColor = lngRed
Else
Me.Time_on_List.ForeColor = lngBlack
Me.Time_on_List.BackColor = lngWhite
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
' Set fcd = .Add(acExpression, , "[outcome] =""pending""
And
' [Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

on opening it, this results in 'Runtime Error 91' which
means 'Object
variable or With block variable not set'.

does this mean something to you?

regards,

ted



:

Ted,
I think I have it! Thanks to Access 2000 Developer's
Handbook by Getz, Litwin, Gilbert:

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
..Delete
Set fcd = .Add(acExpression, , "[outcome] =""pending""
And
[Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

Geof.
-----Original Message-----
hi,

in my case, i have a sub-form which the user
Click to expand...
views
as
a
datasheet. on it
there are three controls that are relevant.

a) Outcome
b) Date_on_List
c) Time_on_List

Time_on_List is computed on the fly for every record
in
the underlying table.

what is desired is the following behavior,
Click to expand...
namely
when
the value of Outcome
= "Pending" and the value of "Time_on_List" >=
Click to expand...
30
we'd
like to see the value
of "Time_on_List' displayed with a red background
using
bold yellow letters.

perhaps i ought to add the fact that using
the "Conditional Formatting"
feature of my A2K installation, i've formatted Outcome
to
dispaly the value
in bold red letters whenever Outcome = "Pending".

the wall we seem to be running into is my
understanding
that "Conditional
Formatting" seems engineered to work with just one
Control at a time.

anyone?

.


.


.
Click to expand...
Click to expand...
.
Click to expand...
 
G

Guest

just when you thought it was safe to go into the switchboard...

i logged in the way a normal user and found when i clicked the appropriate
menu bar that in lieu of the sub-Form, there was a gaping white hole/space.
so i returned through a backdoor and invoked the sub-Form by itself and was
greeted with (i think) an error 7696 -- something about there being too many
conditions requested. scratching my head in the face of this aberration
(because it seemed that only moments earlier i was in cloud 9), i removed the
'On Open' code and opened the form again. it worked like a charm -- all the
colors arriving for the right reasons. hello, what's this: so i checked out
the conditional format from the menu bar for the Time_on_List control and
found a pair of them written as "Expression is"

for the yellow on red condition: [Outcome]="Pending" And [Time_on_list]>=30

and

the black on green condition: [Outcome]="Pending" And [Time_on_list]<30

SO, at this point, the only VBA code behind this form is:

Option Compare Database

Private Sub Form_Current()

Me.Time_on_List = DateDiff("d", Date_on_List, Now())

End Sub


and far be it for me to look in the proverbial gift horse's mouth.

with warm regards,

ted

Geof Wyght said:
Ted,
Looks good. I learned a lot myself.
Geof.
-----Original Message-----
geoff, for the sake of posterity, i'll just quickly sum up: i have set a
Conditional Format on "Outcome" having its value displayed in Bold Yellow
with a Red Backgrnd when'er it's = "Pending".

as for the remainder, the VBA code/look i settled upon is driven with the
following:

Option Compare Database

Private Sub Form_Current()

Me.Time_on_List = DateDiff("d", Date_on_List, Now())

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
Set fcd = .Add(acExpression, , "[Outcome]=""Pending"" And [Time_on_list]>=30")
fcd.BackColor = vbRed
fcd.ForeColor = vbYellow
fcd.FontBold = True
' End With
Set fcd = .Add(acExpression, , "[Outcome]=""Pending"" And [Time_on_list]<30")
fcd.BackColor = vbGreen
fcd.ForeColor = vbBlack
fcd.FontBold = True
End With
End With
End Sub

and i couldnadoneitwithoutya!!

bravo, well-done, you're a brainiac :)

all the best,

ted


Geof Wyght said:
Ted,
Intellisense will show you that you can use the FontColor
property. I guess that's what you mean by "foreground".
You're right about item b). I believe you can have up to 3
conditions for each control. Make them mutually exclusive,
otherwise Access will stop at the first true one. You're
cooking with gas!
Geof.
-----Original Message-----
by george, geoff, i think you got it that time :)

just a quick postmortem....

a) can i get it to give me yellow background when
Time_on_List >= 30 in
addition to the red foreground

b) does your recent code have to co-exist with the On
Current event property
code; my guess is probably so, but that only this part "
Me.Time_on_List =
DateDiff("d", Date_on_List, Now())" needs to remain

c) if i start getting creative and want to display
Time_on_List <30 with a
green foreground when Outcome = "Pending" can i toss that
into the mix

d) looks like Getz' book would be a welcome addition to
my one book Access
reference library (consisting of "Microsoft Access '97
Bible")

with best regards,

ted



:

Yeah. You commented out the Set statement!
Dump the If statement in the current event. The format
condition object is going to replace it.
Geof.
-----Original Message-----
hi geoff,

well....

here's the code i have running for "Screening Log (DS
View)" which is the
subFrm

Private Sub Form_Current()
Dim lngRed As Long
Dim lngYellow As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim Time_on_List As Integer

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

Me.Time_on_List = DateDiff("d", Date_on_List, Now
())

If Me.Time_on_List >= 30 And Me.Outcome
= "Pending"
Then
Me.Time_on_List.ForeColor = lngYellow
Me.Time_on_List.BackColor = lngRed
Else
Me.Time_on_List.ForeColor = lngBlack
Me.Time_on_List.BackColor = lngWhite
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
' Set fcd = .Add(acExpression, , "[outcome]
=""pending""
And
' [Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

on opening it, this results in 'Runtime Error 91'
which
means 'Object
variable or With block variable not set'.

does this mean something to you?

regards,

ted



:

Ted,
I think I have it! Thanks to Access 2000 Developer's
Handbook by Getz, Litwin, Gilbert:

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
..Delete
Set fcd = .Add(acExpression, , "[outcome]
=""pending""
And
[Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

Geof.
-----Original Message-----
hi,

in my case, i have a sub-form which the user views
as
a
datasheet. on it
there are three controls that are relevant.

a) Outcome
b) Date_on_List
c) Time_on_List

Time_on_List is computed on the fly for every
record
in
the underlying table.

what is desired is the following behavior, namely
when
the value of Outcome
= "Pending" and the value of "Time_on_List" >= 30
we'd
like to see the value
of "Time_on_List' displayed with a red background
using
bold yellow letters.

perhaps i ought to add the fact that using
the "Conditional Formatting"
feature of my A2K installation, i've formatted
Outcome
to
dispaly the value
in bold red letters whenever Outcome = "Pending".

the wall we seem to be running into is my
understanding
that "Conditional
Formatting" seems engineered to work with just one
Control at a time.

anyone?

.


.


.
Click to expand...
.
Click to expand...
Click to expand...
 
G

Geof Wyght

Ted,
Maybe you have to delete all conditions upon closing the
form. And if you don't, the next time the form opens it
adds a 3rd and tries to add a fourth. Now that you have an
expression that works, and since you don't have conditions
that are dynamically changing, you can use the dialog box.
Interesting.
Geof.
-----Original Message-----
just when you thought it was safe to go into the switchboard...

i logged in the way a normal user and found when i clicked the appropriate
menu bar that in lieu of the sub-Form, there was a gaping white hole/space.
so i returned through a backdoor and invoked the sub-Form by itself and was
greeted with (i think) an error 7696 -- something about there being too many
conditions requested. scratching my head in the face of this aberration
(because it seemed that only moments earlier i was in cloud 9), i removed the
'On Open' code and opened the form again. it worked like a charm -- all the
colors arriving for the right reasons. hello, what's this: so i checked out
the conditional format from the menu bar for the Time_on_List control and
found a pair of them written as "Expression is"

for the yellow on red condition: [Outcome]="Pending" And [Time_on_list]>=30

and

the black on green condition: [Outcome]="Pending" And [Time_on_list]<30

SO, at this point, the only VBA code behind this form is:

Option Compare Database

Private Sub Form_Current()

Me.Time_on_List = DateDiff("d", Date_on_List, Now())

End Sub


and far be it for me to look in the proverbial gift horse's mouth.

with warm regards,

ted

Geof Wyght said:
Ted,
Looks good. I learned a lot myself.
Geof.
-----Original Message-----
geoff, for the sake of posterity, i'll just quickly
Click to expand...
sum
up: i have set a
Conditional Format on "Outcome" having its value displayed in Bold Yellow
with a Red Backgrnd when'er it's = "Pending".

as for the remainder, the VBA code/look i settled upon
Click to expand...
is
driven with the
following:

Option Compare Database

Private Sub Form_Current()

Me.Time_on_List = DateDiff("d", Date_on_List, Now ())

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
Set fcd = .Add(acExpression, , "[Outcome]=""Pending""
Click to expand...
And
[Time_on_list]>=30")
fcd.BackColor = vbRed
fcd.ForeColor = vbYellow
fcd.FontBold = True
' End With
Set fcd = .Add(acExpression, , "[Outcome]=""Pending""
Click to expand...
And
[Time_on_list]<30")
fcd.BackColor = vbGreen
fcd.ForeColor = vbBlack
fcd.FontBold = True
End With
End With
End Sub

and i couldnadoneitwithoutya!!

bravo, well-done, you're a brainiac :)

all the best,

ted


:

Ted,
Intellisense will show you that you can use the FontColor
property. I guess that's what you mean by "foreground".
You're right about item b). I believe you can have
Click to expand...
up
to 3
conditions for each control. Make them mutually exclusive,
otherwise Access will stop at the first true one. You're
cooking with gas!
Geof.
-----Original Message-----
by george, geoff, i think you got it that time :)

just a quick postmortem....

a) can i get it to give me yellow background when
Time_on_List >= 30 in
addition to the red foreground

b) does your recent code have to co-exist with the On
Current event property
code; my guess is probably so, but that only this part "
Me.Time_on_List =
DateDiff("d", Date_on_List, Now())" needs to remain

c) if i start getting creative and want to display
Time_on_List <30 with a
green foreground when Outcome = "Pending" can i
Click to expand...
toss
that
into the mix

d) looks like Getz' book would be a welcome
Click to expand...
addition
to
my one book Access
reference library (consisting of "Microsoft Access '97
Bible")

with best regards,

ted



:

Yeah. You commented out the Set statement!
Dump the If statement in the current event. The format
condition object is going to replace it.
Geof.
-----Original Message-----
hi geoff,

well....

here's the code i have running for "Screening
Click to expand...
Log
(DS
View)" which is the
subFrm

Private Sub Form_Current()
Dim lngRed As Long
Dim lngYellow As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim Time_on_List As Integer

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

Me.Time_on_List = DateDiff("d",
Click to expand...
Date_on_List,
Now
())

If Me.Time_on_List >= 30 And Me.Outcome
= "Pending"
Then
Me.Time_on_List.ForeColor = lngYellow
Me.Time_on_List.BackColor = lngRed
Else
Me.Time_on_List.ForeColor = lngBlack
Me.Time_on_List.BackColor = lngWhite
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
' Set fcd = .Add(acExpression, , "[outcome]
=""pending""
And
' [Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

on opening it, this results in 'Runtime Error 91'
which
means 'Object
variable or With block variable not set'.

does this mean something to you?

regards,

ted



:

Ted,
I think I have it! Thanks to Access 2000 Developer's
Handbook by Getz, Litwin, Gilbert:

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
..Delete
Set fcd = .Add(acExpression, , "[outcome]
=""pending""
And
[Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

Geof.
-----Original Message-----
hi,

in my case, i have a sub-form which the user views
as
a
datasheet. on it
there are three controls that are relevant.

a) Outcome
b) Date_on_List
c) Time_on_List

Time_on_List is computed on the fly for every
record
in
the underlying table.

what is desired is the following behavior, namely
when
the value of Outcome
= "Pending" and the value of "Time_on_List"
Click to expand...
=
30
we'd
like to see the value
of "Time_on_List' displayed with a red background
using
bold yellow letters.

perhaps i ought to add the fact that using
the "Conditional Formatting"
feature of my A2K installation, i've formatted
Outcome
to
dispaly the value
in bold red letters whenever Outcome = "Pending".

the wall we seem to be running into is my
understanding
that "Conditional
Formatting" seems engineered to work with
Click to expand...
just
one
Control at a time.

anyone?

.


.


.


.
Click to expand...
Click to expand...
.
Click to expand...
 
G

Guest

that has the ring of truth geoff; perhaps there's a 'delete' or 'remove'
command akin to the 'Add' in your/getz' code that adds an exponential
functionality when things are changing according to some complex algorithm.

i won't argue with what we have, though. it works and demonstrates the power
of the user interface.

best wishes,

ted

Ted said:
just when you thought it was safe to go into the switchboard...

i logged in the way a normal user and found when i clicked the appropriate
menu bar that in lieu of the sub-Form, there was a gaping white hole/space.
so i returned through a backdoor and invoked the sub-Form by itself and was
greeted with (i think) an error 7696 -- something about there being too many
conditions requested. scratching my head in the face of this aberration
(because it seemed that only moments earlier i was in cloud 9), i removed the
'On Open' code and opened the form again. it worked like a charm -- all the
colors arriving for the right reasons. hello, what's this: so i checked out
the conditional format from the menu bar for the Time_on_List control and
found a pair of them written as "Expression is"

for the yellow on red condition: [Outcome]="Pending" And [Time_on_list]>=30

and

the black on green condition: [Outcome]="Pending" And [Time_on_list]<30

SO, at this point, the only VBA code behind this form is:

Option Compare Database

Private Sub Form_Current()

Me.Time_on_List = DateDiff("d", Date_on_List, Now())

End Sub


and far be it for me to look in the proverbial gift horse's mouth.

with warm regards,

ted

Geof Wyght said:
Ted,
Looks good. I learned a lot myself.
Geof.
-----Original Message-----
geoff, for the sake of posterity, i'll just quickly sum up: i have set a
Conditional Format on "Outcome" having its value displayed in Bold Yellow
with a Red Backgrnd when'er it's = "Pending".

as for the remainder, the VBA code/look i settled upon is driven with the
following:

Option Compare Database

Private Sub Form_Current()

Me.Time_on_List = DateDiff("d", Date_on_List, Now())

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
Set fcd = .Add(acExpression, , "[Outcome]=""Pending"" And [Time_on_list]>=30")
fcd.BackColor = vbRed
fcd.ForeColor = vbYellow
fcd.FontBold = True
' End With
Set fcd = .Add(acExpression, , "[Outcome]=""Pending"" And [Time_on_list]<30")
fcd.BackColor = vbGreen
fcd.ForeColor = vbBlack
fcd.FontBold = True
End With
End With
End Sub

and i couldnadoneitwithoutya!!

bravo, well-done, you're a brainiac :)

all the best,

ted


:

Ted,
Intellisense will show you that you can use the FontColor
property. I guess that's what you mean by "foreground".
You're right about item b). I believe you can have up to 3
conditions for each control. Make them mutually exclusive,
otherwise Access will stop at the first true one. You're
cooking with gas!
Geof.
-----Original Message-----
by george, geoff, i think you got it that time :)

just a quick postmortem....

a) can i get it to give me yellow background when
Time_on_List >= 30 in
addition to the red foreground

b) does your recent code have to co-exist with the On
Current event property
code; my guess is probably so, but that only this part "
Me.Time_on_List =
DateDiff("d", Date_on_List, Now())" needs to remain

c) if i start getting creative and want to display
Time_on_List <30 with a
green foreground when Outcome = "Pending" can i toss that
into the mix

d) looks like Getz' book would be a welcome addition to
my one book Access
reference library (consisting of "Microsoft Access '97
Bible")

with best regards,

ted



:

Yeah. You commented out the Set statement!
Dump the If statement in the current event. The format
condition object is going to replace it.
Geof.
-----Original Message-----
hi geoff,

well....

here's the code i have running for "Screening Log (DS
View)" which is the
subFrm

Private Sub Form_Current()
Dim lngRed As Long
Dim lngYellow As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim Time_on_List As Integer

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

Me.Time_on_List = DateDiff("d", Date_on_List, Now
())

If Me.Time_on_List >= 30 And Me.Outcome
= "Pending"
Then
Me.Time_on_List.ForeColor = lngYellow
Me.Time_on_List.BackColor = lngRed
Else
Me.Time_on_List.ForeColor = lngBlack
Me.Time_on_List.BackColor = lngWhite
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
' ..Delete
' Set fcd = .Add(acExpression, , "[outcome]
=""pending""
And
' [Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

on opening it, this results in 'Runtime Error 91'
which
means 'Object
variable or With block variable not set'.

does this mean something to you?

regards,

ted



:

Ted,
I think I have it! Thanks to Access 2000 Developer's
Handbook by Getz, Litwin, Gilbert:

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
With Me.Time_on_List
With .FormatConditions
..Delete
Set fcd = .Add(acExpression, , "[outcome]
=""pending""
And
[Time_on_list]>30")
fcd.BackColor = vbRed
End With
End With
End Sub

Geof.
-----Original Message-----
hi,

in my case, i have a sub-form which the user views
as
a
datasheet. on it
there are three controls that are relevant.

a) Outcome
b) Date_on_List
c) Time_on_List

Time_on_List is computed on the fly for every
record
in
the underlying table.

what is desired is the following behavior, namely
when
the value of Outcome
= "Pending" and the value of "Time_on_List" >= 30
we'd
like to see the value
of "Time_on_List' displayed with a red background
using
bold yellow letters.

perhaps i ought to add the fact that using
the "Conditional Formatting"
feature of my A2K installation, i've formatted
Outcome
to
dispaly the value
in bold red letters whenever Outcome = "Pending".

the wall we seem to be running into is my
understanding
that "Conditional
Formatting" seems engineered to work with just one
Control at a time.

anyone?

.


.


.


.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

alter properties of subForm control (KBase Article 209099] 8
Conditional Formatting using Unbound column of Combo Box 1
Using Conditional formatting to compare two strings.... 2
Using VBA coding to count color Occurrences 4
Trouble w/ conditional formatting and dates 5
Conditional Formatting Headache 2
Not enough conditional formatting 2
Conditional Formatting 2

Top