Conditional format

[Niklas Östergren]

Hi!

Is it possible to have conditional format in a continious form?

I have a subform (subform No 2 = Continious form) in a subform (subform No 1
= Singel form) . In subform No 2 I´d like to perform a conditional formating
on a value.

I´d like to format a membership fee bold and red if the value <> from the
value correct value for that type of membership (we have 4 different types
of memberships.

This is what I have so far but I havn´t got it to work the way I want. If I
put the code in the form´s Open_Event it format ALL value red which isn´t
true.

It´s late here so I´ll be back tomorrow morning!

TIA!
// Niklas


'===========================================
With Me.txtEntryFeePayed
If .Value <> DLookup("MemberShipPrice",
"tblLookUpMemberShipType", "MemberShipTypeID = " & Me.cboMemberShipType)
Then
.ForeColor = 255
.FontBold = True
.FontSize = 8
.Height = 300
Else
.ForeColor = -2147483640
.FontBold = False
.FontSize = 8
.Height = 300
End If
End With
End With
'==========================================

 

[Marshall Barton]

Is it possible to have conditional format in a continious form?

I have a subform (subform No 2 = Continious form) in a subform (subform No 1
= Singel form) . In subform No 2 I´d like to perform a conditional formating
on a value.

I´d like to format a membership fee bold and red if the value <> from the
value correct value for that type of membership (we have 4 different types
of memberships.

This is what I have so far but I havn´t got it to work the way I want. If I
put the code in the form´s Open_Event it format ALL value red which isn´t
true.
'===========================================
With Me.txtEntryFeePayed
If .Value <> DLookup("MemberShipPrice",
"tblLookUpMemberShipType", "MemberShipTypeID = " & Me.cboMemberShipType)
Then
.ForeColor = 255
.FontBold = True
.FontSize = 8
.Height = 300
Else
.ForeColor = -2147483640
.FontBold = False
.FontSize = 8
.Height = 300
End If
End With
End With

You can not do it a continuous form using code. If you are
using A2K or later, try using Conditional Formatting (Format
menu). I think you can set the Value Is to the DLookup in
your code.

 

[Niklas Östergren]

Hi Marshall!

Thank´s a lot for the information, that was what I was afraid of though.

I have tryed using Conditional Formatting (Format menu) as you suggested but
it gives me the same result. Which is that it formattes all values in that
field not only the once I´d like. If I´m using this feauter correct that is.

So this means that my users have to manage without this which they will.

But this shouldn´t be any major problem for them because the continous form
in which I´d like to use conditional formatting in is just a form where the
data is displayed (edit isn´t possible). And in the guide in which the user
edit the value and add new records is of single type and there I allready
have this formattin to show the user that the value is not corresponding to
the registrated value for this type of membership in the LookUpTabel.


Thank´s anyway for helping out!

// Niklas

 

[Marshall Barton]

I have tryed using Conditional Formatting (Format menu) as you suggested but
it gives me the same result. Which is that it formattes all values in that
field not only the once I´d like. If I´m using this feauter correct that is.

So this means that my users have to manage without this which they will.

But this shouldn´t be any major problem for them because the continous form
in which I´d like to use conditional formatting in is just a form where the
data is displayed (edit isn´t possible). And in the guide in which the user
edit the value and add new records is of single type and there I allready
have this formattin to show the user that the value is not corresponding to
the registrated value for this type of membership in the LookUpTabel.

Hold on there, don't give up so easily ;-)

I'm pretty sure you can get this to work once we figure out
the details. If all records are being formatted the same,
that probably means the value you're comparing to is wrong.
Post back with the setting you have in the CF window.

I'm thinking you might need to use the Expression Is instead
of Value Is because the comparison you want is not equals
(or you could make this the default format and make the case
where they are equal the conditional case.

I do have reservations about using a heavy duty function
such as DLookup in a CF expression though. Since this is a
"read only" form, how about Joining the lookup table to the
current data table in the form's record source query? This
would mean each record would already have the compare value
and the DLookup wouldn't be needed. If that's confusing,
post back with an explanation of the two tables and their
relationship.

 

[Niklas Östergren]

Hi again Marshall!

No, that´s not confusing to me. To change teh recordsource and only selecte
the values which I´d like to format. That´s what you mean or?

But, ther´s allways a but you know ;-) In this read only form I´d like the
user to se ALL records for selected member. But only the records which
correspond to the condition needs to be formatted with bold and red.

I have got it to wotk with conditional formatting. But only halfe the way (I
guess I havn´t told you everything yet so here I go:

The form (read only) is showing memberships that a member have payed for.
And a member can, most likely actualy, pay membership fee for several year
of memberships. And the user want to see membershipfee for all period at the
same time.

Sometimes the member pay an amount of money that does NOT correspond to the
current fee. Sometimes they pay to less so the user have to remind them
sometimes thay pay to much so the user have to pay back. It´s this record
that the user would like to have formatted in bold and red so i´t´s easy to
spot.

But we only store the current value of each type of membership in the db so
we can only compare with the value for current year. Therfore would I like
to format ONLY these record that meet following condition:

1.) tblMemberValidation.EntryFeePayed)=DLookUp _
("MemberShipPrice","tblLookUpMemberShipType","MemberShipTypeID = " & _
[tblMemberValidation].[fkmemberShipTypeID])

AND

2.) DatePart("yyyy",[MemberShipEndDate])=DatePart("yyyy",Date())

The first condition works (thanks to you pushing me ;-)) by using "Field
value is NOT = DLookUp ....... (see condition no. 1 abowe)

But the second one doesn´t and I have tryed but can´t get any result. Either
will all record be formatted. Which happens if I put the both condition in
same condition. Which is logical but not what I want.

And since I realy would like to use both condition on the same like with AND
between I don´t know how to solve this, jet!

Any idéas?

Mean while I´ll go home from work and connect to this newsgroup within one
hour.

By the way, thanks for pushing me to try a little bit more. And I´m sure
this will be possible to solve, now when I have seen that I can get one part
working.

// Niklas

 

[Marshall Barton]

No, that´s not confusing to me. To change teh recordsource and only selecte
the values which I´d like to format. That´s what you mean or?

But, ther´s allways a but you know ;-) In this read only form I´d like the
user to se ALL records for selected member. But only the records which
correspond to the condition needs to be formatted with bold and red.

I have got it to wotk with conditional formatting. But only halfe the way (I
guess I havn´t told you everything yet so here I go:

The form (read only) is showing memberships that a member have payed for.
And a member can, most likely actualy, pay membership fee for several year
of memberships. And the user want to see membershipfee for all period at the
same time.

Sometimes the member pay an amount of money that does NOT correspond to the
current fee. Sometimes they pay to less so the user have to remind them
sometimes thay pay to much so the user have to pay back. It´s this record
that the user would like to have formatted in bold and red so i´t´s easy to
spot.

But we only store the current value of each type of membership in the db so
we can only compare with the value for current year. Therfore would I like
to format ONLY these record that meet following condition:

1.) tblMemberValidation.EntryFeePayed)=DLookUp _
("MemberShipPrice","tblLookUpMemberShipType","MemberShipTypeID = " & _
[tblMemberValidation].[fkmemberShipTypeID])

AND

2.) DatePart("yyyy",[MemberShipEndDate])=DatePart("yyyy",Date())

The first condition works (thanks to you pushing me ;-)) by using "Field
value is NOT = DLookUp ....... (see condition no. 1 abowe)

But the second one doesn´t and I have tryed but can´t get any result. Either
will all record be formatted. Which happens if I put the both condition in
same condition. Which is logical but not what I want.

And since I realy would like to use both condition on the same like with AND
between I don´t know how to solve this, jet!

I'm confused by the names you're using in your expression.
Is tblMemberValidation the name of a table or the name of
your form. Is EntryFeePayed the name of a field in the
form's record source table or is it the name of a text box
control on the form or both?

Just to keep them straight in my mind I will prefix text box
names with "txt". I think(?) the CF Expression Is condition
would be:

[txtEntryFeePayed] = DLookup(. . ., . . ., . . .)
AND Year([txtMemberShipEndDate])=Year(Date())

I still want to try to get rid of the DLookup here. Try
setting the form's RecordSource query to something like:

SELECT tblMemberValidation.EntryFeePayed,
tblMemberValidation.MemberShipEndDate,
. . .
tblLookUpMemberShipType.MemberShipPrice
FROM tblMemberValidation
INNER JOIN tblLookUpMemberShipType
ON tblMemberValidation.fkmemberShipTypeID
= tblLookUpMemberShipType.MemberShipTypeID
so that you can have a hidden text box bound to the
MemberShipPrice field. Then the CF expression would be
greatly simplified and much faster:

[txtEntryFeePayed] = [txtMemberShipTypeID] AND
Year([txtMemberShipEndDate])=Year(Date())

 

[Niklas Östergren]

Hi Marshall!

Well maby I was a little bit to fast when I copied and paste the strings in
my msg. Because I copied them from a query which I have created just to
verifie the conditions. In the conditional formatting I can´t use english
language since I run Swedish Access 2002 so it automaticly convert the
english Dlookup to DSlåUpp, so I thought that it would be easyer for you to
read the SQL-statment from the query. Sorry if that confused you more than
neccesary.

Which menas that I DO use prefix like tbl for tables, txt for textboxes. So
tblMemberValidation.EntryFeePayed means that <EntryFeePayed > is a field in
table <tblMemberValidation>.

Anyway, I´m not verry good at SQL so I have to take a closer look into the
SQL-statment you posted. I´ll try it out tomorrow asap. and get back to you
which how I´m doing.

I realy appreciate the time and effort you put in to trying to help me.
Thanks!

// Niklas

No, that´s not confusing to me. To change teh recordsource and only
selecte
the values which I´d like to format. That´s what you mean or?

But, ther´s allways a but you know ;-) In this read only form I´d like the
user to se ALL records for selected member. But only the records which
correspond to the condition needs to be formatted with bold and red.

I have got it to wotk with conditional formatting. But only halfe the way
(I
guess I havn´t told you everything yet so here I go:

The form (read only) is showing memberships that a member have payed for.
And a member can, most likely actualy, pay membership fee for several year
of memberships. And the user want to see membershipfee for all period at
the
same time.

Sometimes the member pay an amount of money that does NOT correspond to
the
current fee. Sometimes they pay to less so the user have to remind them
sometimes thay pay to much so the user have to pay back. It´s this record
that the user would like to have formatted in bold and red so i´t´s easy
to
spot.

But we only store the current value of each type of membership in the db
so
we can only compare with the value for current year. Therfore would I like
to format ONLY these record that meet following condition:

1.) tblMemberValidation.EntryFeePayed)=DLookUp _
("MemberShipPrice","tblLookUpMemberShipType","MemberShipTypeID = " &
_
[tblMemberValidation].[fkmemberShipTypeID])

AND

2.) DatePart("yyyy",[MemberShipEndDate])=DatePart("yyyy",Date())

The first condition works (thanks to you pushing me ;-)) by using "Field
value is NOT = DLookUp ....... (see condition no. 1 abowe)

But the second one doesn´t and I have tryed but can´t get any result.
Either
will all record be formatted. Which happens if I put the both condition in
same condition. Which is logical but not what I want.

And since I realy would like to use both condition on the same like with
AND
between I don´t know how to solve this, jet!

I'm confused by the names you're using in your expression.
Is tblMemberValidation the name of a table or the name of
your form. Is EntryFeePayed the name of a field in the
form's record source table or is it the name of a text box
control on the form or both?

Just to keep them straight in my mind I will prefix text box
names with "txt". I think(?) the CF Expression Is condition
would be:

[txtEntryFeePayed] = DLookup(. . ., . . ., . . .)
AND Year([txtMemberShipEndDate])=Year(Date())

I still want to try to get rid of the DLookup here. Try
setting the form's RecordSource query to something like:

SELECT tblMemberValidation.EntryFeePayed,
tblMemberValidation.MemberShipEndDate,
. . .
tblLookUpMemberShipType.MemberShipPrice
FROM tblMemberValidation
INNER JOIN tblLookUpMemberShipType
ON tblMemberValidation.fkmemberShipTypeID
= tblLookUpMemberShipType.MemberShipTypeID
so that you can have a hidden text box bound to the
MemberShipPrice field. Then the CF expression would be
greatly simplified and much faster:

[txtEntryFeePayed] = [txtMemberShipTypeID] AND
Year([txtMemberShipEndDate])=Year(Date())

 

[Niklas Östergren]

Good morning Marshall!

It was a little bit late yesterday and I have had a tough day at work so my
mind wasn´t quit switched on. That´s why I didn´t realy got the
SQL-statment. ´Now this morning I see that it´s verry straight forward and
nothing strange and also familiar to me.

I have followed your recommendation and changed the recordsource to a saved
INNER JOIN query so now everything works when I use this CF:

Expression is: Year([txtMemberShipEndDate])=Year(Datum())
AND[txtEntryFeePayed]<>[txtMemberShipPrice]

Where txtMemberShipPrice is a hidden texbox control on the form bound to the
field current memberfee and txtEntryFeePayed is the visible texbox control
where I make the CF.

Thank´s a lot for all your help Marshall I was a little bit to tired
yesterday evning so I guess I shall wait asking Q until after I have get som
sleep

Thanks!

// Niklas

No, that´s not confusing to me. To change teh recordsource and only selecte
the values which I´d like to format. That´s what you mean or?

But, ther´s allways a but you know ;-) In this read only form I´d like the
user to se ALL records for selected member. But only the records which
correspond to the condition needs to be formatted with bold and red.

I have got it to wotk with conditional formatting. But only halfe the way (I
guess I havn´t told you everything yet so here I go:

The form (read only) is showing memberships that a member have payed for.
And a member can, most likely actualy, pay membership fee for several year
of memberships. And the user want to see membershipfee for all period at the
same time.

Sometimes the member pay an amount of money that does NOT correspond to the
current fee. Sometimes they pay to less so the user have to remind them
sometimes thay pay to much so the user have to pay back. It´s this record
that the user would like to have formatted in bold and red so i´t´s easy to
spot.

But we only store the current value of each type of membership in the db so
we can only compare with the value for current year. Therfore would I like
to format ONLY these record that meet following condition:

1.) tblMemberValidation.EntryFeePayed)=DLookUp _
("MemberShipPrice","tblLookUpMemberShipType","MemberShipTypeID = " & _
[tblMemberValidation].[fkmemberShipTypeID])

AND

2.) DatePart("yyyy",[MemberShipEndDate])=DatePart("yyyy",Date())

The first condition works (thanks to you pushing me ;-)) by using "Field
value is NOT = DLookUp ....... (see condition no. 1 abowe)

But the second one doesn´t and I have tryed but can´t get any result. Either
will all record be formatted. Which happens if I put the both condition in
same condition. Which is logical but not what I want.

And since I realy would like to use both condition on the same like with AND
between I don´t know how to solve this, jet!

I'm confused by the names you're using in your expression.
Is tblMemberValidation the name of a table or the name of
your form. Is EntryFeePayed the name of a field in the
form's record source table or is it the name of a text box
control on the form or both?

Just to keep them straight in my mind I will prefix text box
names with "txt". I think(?) the CF Expression Is condition
would be:

[txtEntryFeePayed] = DLookup(. . ., . . ., . . .)
AND Year([txtMemberShipEndDate])=Year(Date())

I still want to try to get rid of the DLookup here. Try
setting the form's RecordSource query to something like:

SELECT tblMemberValidation.EntryFeePayed,
tblMemberValidation.MemberShipEndDate,
. . .
tblLookUpMemberShipType.MemberShipPrice
FROM tblMemberValidation
INNER JOIN tblLookUpMemberShipType
ON tblMemberValidation.fkmemberShipTypeID
= tblLookUpMemberShipType.MemberShipTypeID
so that you can have a hidden text box bound to the
MemberShipPrice field. Then the CF expression would be
greatly simplified and much faster:

[txtEntryFeePayed] = [txtMemberShipTypeID] AND
Year([txtMemberShipEndDate])=Year(Date())

 

[Niklas Östergren]

One more Q Marshall!

I have been thinking a little bit about this approach with CF and having the
form unlocked so that the user can edit the values, even if it´s quit rare
so have I come to that conclution that it sometimes is nessecary to do and I
don´t think that it´s nessesary to create another form just for this.

But if a user change the value in the bound textbox [txtEntryFeePayed] so it
will meet the CF expression it´might be quit odd and strange to a user not
familiar with the program why the value suddenlu get´s red and bold so I´d
like to display a MsgBox with a short exeplanation and a warning msg to the
user so I tryed this in the AfterUpdateEvent of [txtEntryFeePayed] (see code
below). And when I break the code and examin the value of
Me.txtEntryFeePayed.ForColor it´s allways = 0 which is not true.

Is there a way to check if the CF have came true and if so then display my
Msg?

TIA!

// Niklas

'============================================================
' If MemberShipType is selected and MemberShipFee have a value
' AND forecolor is red then display a warning.
If Not Me.cboMemberShipType & "" = "" Then
If Not Me.txtMemberShipPrice & "" = "" Then

curCurrentMemberShipFee = Me.txtMemberShipPrice

With Me.txtEntryFeePayed
If .ForeColor = 255 Then

strMemberShipType = Me.txtMemberType

MsgBox "Medlemsavgiften stämmer inte med den
registrerade avgiften:" & vbCrLf & vbCrLf _
& "Vald medlemstyp: " & strMemberShipType & vbCrLf _
& "Registrerat pris: " & curCurrentMemberShipFee &
" kr", vbInformation + vbOKOnly, "Medlemsavgift stämmer ej"
End If
End With
End If
End If
'==========================================================================

No, that´s not confusing to me. To change teh recordsource and only selecte
the values which I´d like to format. That´s what you mean or?

But, ther´s allways a but you know ;-) In this read only form I´d like the
user to se ALL records for selected member. But only the records which
correspond to the condition needs to be formatted with bold and red.

I have got it to wotk with conditional formatting. But only halfe the way (I
guess I havn´t told you everything yet so here I go:

The form (read only) is showing memberships that a member have payed for.
And a member can, most likely actualy, pay membership fee for several year
of memberships. And the user want to see membershipfee for all period at the
same time.

Sometimes the member pay an amount of money that does NOT correspond to the
current fee. Sometimes they pay to less so the user have to remind them
sometimes thay pay to much so the user have to pay back. It´s this record
that the user would like to have formatted in bold and red so i´t´s easy to
spot.

But we only store the current value of each type of membership in the db so
we can only compare with the value for current year. Therfore would I like
to format ONLY these record that meet following condition:

1.) tblMemberValidation.EntryFeePayed)=DLookUp _
("MemberShipPrice","tblLookUpMemberShipType","MemberShipTypeID = " & _
[tblMemberValidation].[fkmemberShipTypeID])

AND

2.) DatePart("yyyy",[MemberShipEndDate])=DatePart("yyyy",Date())

The first condition works (thanks to you pushing me ;-)) by using "Field
value is NOT = DLookUp ....... (see condition no. 1 abowe)

But the second one doesn´t and I have tryed but can´t get any result. Either
will all record be formatted. Which happens if I put the both condition in
same condition. Which is logical but not what I want.

And since I realy would like to use both condition on the same like with AND
between I don´t know how to solve this, jet!

I'm confused by the names you're using in your expression.
Is tblMemberValidation the name of a table or the name of
your form. Is EntryFeePayed the name of a field in the
form's record source table or is it the name of a text box
control on the form or both?

Just to keep them straight in my mind I will prefix text box
names with "txt". I think(?) the CF Expression Is condition
would be:

[txtEntryFeePayed] = DLookup(. . ., . . ., . . .)
AND Year([txtMemberShipEndDate])=Year(Date())

I still want to try to get rid of the DLookup here. Try
setting the form's RecordSource query to something like:

SELECT tblMemberValidation.EntryFeePayed,
tblMemberValidation.MemberShipEndDate,
. . .
tblLookUpMemberShipType.MemberShipPrice
FROM tblMemberValidation
INNER JOIN tblLookUpMemberShipType
ON tblMemberValidation.fkmemberShipTypeID
= tblLookUpMemberShipType.MemberShipTypeID
so that you can have a hidden text box bound to the
MemberShipPrice field. Then the CF expression would be
greatly simplified and much faster:

[txtEntryFeePayed] = [txtMemberShipTypeID] AND
Year([txtMemberShipEndDate])=Year(Date())

 

[Marshall Barton]

One more Q Marshall!

I have been thinking a little bit about this approach with CF and having the
form unlocked so that the user can edit the values, even if it´s quit rare
so have I come to that conclution that it sometimes is nessecary to do and I
don´t think that it´s nessesary to create another form just for this.

But if a user change the value in the bound textbox [txtEntryFeePayed] so it
will meet the CF expression it´might be quit odd and strange to a user not
familiar with the program why the value suddenlu get´s red and bold so I´d
like to display a MsgBox with a short exeplanation and a warning msg to the
user so I tryed this in the AfterUpdateEvent of [txtEntryFeePayed] (see code
below). And when I break the code and examin the value of
Me.txtEntryFeePayed.ForColor it´s allways = 0 which is not true.

Is there a way to check if the CF have came true and if so then display my
Msg?

TIA!

// Niklas

'============================================================
' If MemberShipType is selected and MemberShipFee have a value
' AND forecolor is red then display a warning.
If Not Me.cboMemberShipType & "" = "" Then
If Not Me.txtMemberShipPrice & "" = "" Then

curCurrentMemberShipFee = Me.txtMemberShipPrice

With Me.txtEntryFeePayed
If .ForeColor = 255 Then

strMemberShipType = Me.txtMemberType

MsgBox "Medlemsavgiften stämmer inte med den
registrerade avgiften:" & vbCrLf & vbCrLf _
& "Vald medlemstyp: " & strMemberShipType & vbCrLf _
& "Registrerat pris: " & curCurrentMemberShipFee &
" kr", vbInformation + vbOKOnly, "Medlemsavgift stämmer ej"
End If
End With
End If
End If

Hmmm, I don't think you can look at what CF has done. Never
thought to try it because, even if you could, there are sure
to be timing issues between CF formatting the screen and the
execution of your code.

I suggest that the AfterUpdate event just check the same
conditions that you used in CF.

If Year([txtMemberShipEndDate])=Year(Datum()) _
AND [txtEntryFeePayed] <> [txtMemberShipPrice] Then
MsgBox "Medlemsavgiften stämme . . .

 

[Niklas Östergren]

Well, sometimes I make it more dificult then it have to be. This was one of
those ocations. What you suggest is straight forward and simple. I´ll go for
that!

Thanks for helping me out Marshall!

// Niklas

One more Q Marshall!

I have been thinking a little bit about this approach with CF and having
the
form unlocked so that the user can edit the values, even if it´s quit rare
so have I come to that conclution that it sometimes is nessecary to do and
I
don´t think that it´s nessesary to create another form just for this.

But if a user change the value in the bound textbox [txtEntryFeePayed] so
it
will meet the CF expression it´might be quit odd and strange to a user not
familiar with the program why the value suddenlu get´s red and bold so I´d
like to display a MsgBox with a short exeplanation and a warning msg to
the
user so I tryed this in the AfterUpdateEvent of [txtEntryFeePayed] (see
code
below). And when I break the code and examin the value of
Me.txtEntryFeePayed.ForColor it´s allways = 0 which is not true.

Is there a way to check if the CF have came true and if so then display my
Msg?

TIA!

// Niklas

'============================================================
' If MemberShipType is selected and MemberShipFee have a value
' AND forecolor is red then display a warning.
If Not Me.cboMemberShipType & "" = "" Then
If Not Me.txtMemberShipPrice & "" = "" Then

curCurrentMemberShipFee = Me.txtMemberShipPrice

With Me.txtEntryFeePayed
If .ForeColor = 255 Then

strMemberShipType = Me.txtMemberType

MsgBox "Medlemsavgiften stämmer inte med den
registrerade avgiften:" & vbCrLf & vbCrLf _
& "Vald medlemstyp: " & strMemberShipType & vbCrLf
_
& "Registrerat pris: " & curCurrentMemberShipFee
&
" kr", vbInformation + vbOKOnly, "Medlemsavgift stämmer ej"
End If
End With
End If
End If

Hmmm, I don't think you can look at what CF has done. Never
thought to try it because, even if you could, there are sure
to be timing issues between CF formatting the screen and the
execution of your code.

I suggest that the AfterUpdate event just check the same
conditions that you used in CF.

If Year([txtMemberShipEndDate])=Year(Datum()) _
AND [txtEntryFeePayed] <> [txtMemberShipPrice] Then
MsgBox "Medlemsavgiften stämme . . .