Conditional Format on not focussed

[NeilT123]

Hi
I have the following code on a continuous form which gives various formatting if the record is selected.
I would really like to add conditional formatting if the record is not selected so say an option where NoLongerCropped = True and an option where FertRecStatus = -1.

Is this possible and if so where do I put the code?

Thanks

Private Sub Form_Current()
'varCondition must be numeric for this to work
Dim ctl As Control, strForm As String, varCondition As Variant
Dim fcs As FormatConditions, fc As FormatCondition
Dim lngColour As Long
Dim lngColour2 As Long
Dim Font As Long

On Error Resume Next

varCondition = Me!CroppingNumber

'This code overrides the conditional formatting appplied WHEN the records have focus using the Cropping Number
If Me!FertRecStatus = -1 Then
    lngColour = vbWhite 'white background
    lngColour2 = vbRed 'Red font
    Font = True 'Font is bold
ElseIf Me!NoLongerCropped = True Then
    lngColour = 12040119 'Gray background
    lngColour2 = vbBlack 'Black Font
    Font = False 'Font is normal
Else
    lngColour = vbWhite 'white background
    lngColour2 = vbBlack 'Black Font
    Font = True 'Font is bold
End If
    
For Each ctl In Me.Controls
    If ctl.Tag = "ConditionalFormat" Then
        Set fcs = Me.Controls(ctl.Name).FormatConditions
        fcs.Delete  'Deletes any other Conditional Formatting, limit is 3
        Set fc = fcs.Add(acExpression, , "CroppingNumber=" & varCondition)
        fc.BackColor = lngColour
        fc.ForeColor = lngColour2
        fc.FontBold = Font
    End If
Next ctl
Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err <> 0 Then
    GoTo Form_Current_Exit
Else
    On Error GoTo Form_Current_Err
End If

 

[NeilT123]

I sorted this by amending the code as follows:

If ctl.Tag = "ConditionalFormat" And Me!CroppingNumber = varCondition Then

so the formatting only happens when the record is selected and the underlying Access Conditional Formatting applies when the record is not selected

 

[NeilT123]

UPDATE:

I am still having problems trying to get this sorted.

I have a continuous form which I would like to have conditional formatting both when the record has focus and also when it doesn't.

I have set up the Access Conditional Formatting for when the record doesn't have focus and am using the above code for when the record does have focus.

When the record does have focus the above code applies the formatting I require but I think the above code overrides the built in Access Conditional Formatting and so the formatting when the record does not have focus doesn't work.

Can anyone suggest how I might amend the code so that it only applies the formatting when the record has focus and the default Conditional Formatting applies at all other times.

Thanks for any help provided.

 

[MajP]

I do not understand the term "selected record" in a continous form. There will always be a selected record so your terminology in not correct.


I am assuming you mean
FertRecStatus = -1

Your current cases ares
1. FertRecStatus = -1

2. NoLongerCropped = True and not FertRecStatus = -1

3. not NoLongerCropped = true and not FertRecStatus = -1

Does it not apply case 3 ?
lngColour = vbWhite 'white background
lngColour2 = vbBlack 'Black Font
Font = True 'Font is bold

If the default is not case 3 then your boolean logic is incorrect and you need another case to reset it back to the default.

 

[MajP]

OK I may have read this wrong
Is Me!CroppingNumber an unbound textbox? If it is a field on the form then I do not think this code does anything and all of this could be done in the forms conditional format. And if it is a field that changes on the on current event it still does nothing.

May also be expectation management. You can only set 3 conditions at a time. If you think that for the other records in the continous form the default set of conditions are in affect, you have a misunderstanding of how format conditions work.

 

[NeilT123]

Hi MajP, I guess it must be my understanding of format conditions in that I was hoping to apply 3 conditions via the inbuilt facility and a further 3 in the code above.

Looks like it is back to the drawing board.

 

[MajP]

This is a common misunderstanding. Even if you apply format conditions in code the max you can have at one time on a continous form is 3 (4 if you count the default).

On a single form you can make it appear as if you have more. For example. On a single form you have fldA, and fldB. FldA can be yes/no. And fldB can be 1,2,3.
If fldA is true
and fldB 1:yellow
and fldB 2:blue
and bldB 3:green

if fldA is false
and fldB 1: purple
and flbB 2: orange
and fldB 3: Red

This would appear as if there is six conditions (8 if you have a default). But you set the first set of conditions based on A being true, and a different set of conditions based on fldA being false. In a continous form though it does not work.

If you need more than 3/4 conditions on a continous form, you are stuck with building an unbound form.