Conditional formatting based on seperate Yes/No tick box

[Bird_FAT]

Hi all,

I'm still new to Access and am enjoying the chance to practice at every opportunity

I currently have a project where I have a form that has a date box that I want to format based on the following:

• If the Date box is empty = format one
• If there is a date in the box AND the Yes/No tick box next to it is UNTICKED = format two
• If there is a date in the box AND the Yes/No tick box next to it is TICKED = format three

To do this I tried the following Conditional formatting using 'Expression is'

[ReviewLetter] = Date box
[Review_YN] = Tick box


[LIST=1]
[*][ReviewLetter]>0 And [Review_YN]=False
[*][ReviewLetter]>0 And [Review_YN]=True
[/LIST]
 
[LIST=1]
[*][ReviewLetter]>0 And [Review_YN]=No
[*][ReviewLetter]>0 And [Review_YN]=Yes

[/LIST]
So - what noob mistake have I made here - because it sure aint working! LOL.

Any help most appreciated

 

[jsv2002]

Hi there you need to do it in VB either in OnCurrent or afterupdate event of form or control teh following is not tested but should look something like:

If Not IsNull ([ReviewLetter]) And Me![Review_YN] = False Then

'Do something here...

Elseif Not IsNull ([ReviewLetter]) And Me![Review_YN] = True Then

Do something here...

Else

'No of the above do something here....

End if


As said just off the top of my head but should point you in the right direction

good luck John

 

[Bird_FAT]

As said just off the top of my head but should point you in the right direction

good luck John

Thanks John - off to work now - will give it a go and post result later! Oh - and thanks for just posting the basics - I always prefer to work out answers like this, it makes me feel that I have really done some learning!

.

 

[missinglinq]

Actually you probably are better off using Conditional Formatting from the menu , especially with only three conditions. Using code in the OnCurrent event works if you're using a Single View form, but will not work with Datasheet View or Continuous View forms. Conditional Formatting, on the other hand, works for all form views.

Using Expression Is

Date field is empty
IsNull([ReviewLetter])

Date field populated and checkbox unticked
Not IsNull([ReviewLetter]) and [Review_YN] =0

Date field populated and checkbox ticked
Not IsNull([ReviewLetter]) and [Review_YN] = -1

Linq ;0)>

 

[jsv2002]

Yep sorry mis-read the post that you were trying to achieve formatting of a particular control thought you wanted an event condition my badddd....

Thanx missingling for correcting my error just not concentrating of late

John

 

[Bird_FAT]

OK - seems that I have it!

And - that i HAD it!!

The Expression I added in the first post actually works fine ...

"SPOKE TOO SOON"

- BUT, ONLY in Access 2003!! I have 2007 at home, and 2003 at work, so, while the expression works at work, I created it at home. Anyone got any suggestions as to what I could do to change the expression so that BOTH systems will work?

"AGAIN - SPOKE TOO SOON"

Just tried Missinglinq's code and now discover that both of us are right - it's just that 2007 Access seems to refuse to update the condition when you tick the box - but it works if you close and reopen the form. Any ideas of how to get it to update the condition after a box is ticked?

.

 

[missinglinq]

Should update when the record is saved, which is to say when you move to another record, close the form or explicitly save the record thru code. But experimenting with 2003 (don't run 2007) the formatting changes on ticking the box without the record being saved. Don't know why it wouldn't do this with 2007, but as you know, many things in 2007 aren't what they should be!

 

[jsv2002]

Would help to see the DB but what type of formating are you trying to invoke? You could use the "IF" statements to determine the form controls state then sue conditional formating to do what you want did something similar in 2003 no idea if it woks in 2007 but looked like:


Private Sub Form_Current()

'Calculates the score from severity and likelihood figures entered in form.
'(used in Form On current, Severity and Likelihood afterupdates as either field could be updated)

Dim varScore As Variant

varScore = [Severity] * [Likelihood]
Me.Score = varScore

Dim strlow As String
Dim strMed As String
Dim strHigh As String

strlow = "low"
strMed = "Medium"
strHigh = "High"

If [Score] <= 7 Then
Me.RANK = strlow
ElseIf [Score] >= 8 And [Score] <= 15 Then
Me.RANK = strMed
ElseIf [Score] > 15 And [Score] <= 25 Then
Me.RANK = strHigh
Else
MsgBox ("Please ensure the values you have entered are correct")

Me.Severity.Undo
Me.Likelihood.Undo
Me.Score.Undo

End If

End Sub

Then set the conditional formating of the control to equate to "Low", "Medium" or "High"

This would set "RANK" to my chosen colour.

No idea if it relates to what you are trying to achieve but was a work around for me...

good luck John