Change 'Font' colour depending on condition

[scouser]

Guys, help me out as I lack the mind of a coder!! I have had a go, feel free to laugh at my attempt. So what do I want. When my order form opens I have a control that displays the current order status (either 'Open' or 'Closed'). If the order is closed I want the font to be 'Red' else I want it to be another colour.......My attempt as follows:

Private Sub Form_Open(Cancel As Integer)

DoCmd.Maximize

If Me.OrderClosed = -1 Then
Me.OrderStatus.ForeColor = 255
Else
Me.OrderStatus.ForeColor = 16711680
End If
End Sub

Basically open or closed the above displays all text in RED!!
Thanks in advance,
Phil.

 

[___]

What control is it? If it's a text box with the words Closed or open then...

Private Sub Form_Open(Cancel As Integer)

     DoCmd.Maximize

          If Me.OrderClosed = "Closed" Then
              Me.OrderStatus.ForeColor = 255
          Else
              Me.OrderStatus.ForeColor = 16711680
          End If

End Sub

or if it's a check box try...

Private Sub Form_Open(Cancel As Integer)

     DoCmd.Maximize

          If Me.OrderClosed = True Then
              Me.OrderStatus.ForeColor = 255
          Else
              Me.OrderStatus.ForeColor = 16711680
          End If

End Sub

HTH

 

[Rich]

Is this a continuous form? In A2k and higher you can use conditional formatting

 

[scouser]

Where to start

The form is a single form not continuous. It is a tabbed form. Page 1 = Order Form / Page 2 = Payments Form. On page 1 I have an unbound text box called OrderStatus which is populated as follows:

=IIf([OrderClosed]=-1,"Order Closed","Order Open")

So the text box value is dependent on the value of the check box.
I tried both of your examples and the code appeared the same colour!!
See attached jpegs..........................
Any thoughts, many thanks,
Phil.

 

[Mile-O]

Is the Open event the event you really want? I'd say the Current event was more apprpriate.

 

[Fizzio]

Also this will only format the control on the first record that you show in this form as you have placed the code on the Form_Open event.
If this is a pop-up form that will only display one record then fine.
However if the form can show many records by scrolling / navigating through. The Form will show the formatting that was applied when the form was opened.
A better method would be:

Private Sub Form_Current

DoCmd.Maximize
With Me
If .OrderClosed = True Then
.OrderStatus.ForeColor = 255
Else
.OrderStatus.ForeColor = 16711680
End If
End With
End Sub

 

[Fizzio]

Is the Open event the event you really want? I'd say the Current event was more apprpriate.

See those fingers have not aged then Mile-O (git)

 

[scouser]

Wow

Cheers guys, I see the error. On current makes much more sense. I will give that a whirl!!
Cheers,
Phil.

 

[scouser]

One More!!

I have current event already, how would I add the suggested code to this and not get errors?

Private Sub Form_Current()
Dim ctlControl As Control

If Me.TotalGross >= "0.00" And Me.OrderBalance <= "0.00" Then
Me.OrderClosed = -1
For Each ctlControl In Me.Controls
On Error Resume Next
ctlControl.Locked = True
Next
Me.OrderClosed.Locked = False
Else
Me.OrderClosed = 0
For Each ctlControl In Me.Controls
On Error Resume Next
ctlControl.Locked = False
Next
End If
End Sub
[CODE] 
Cheers,
Phil.

 

[scouser]

Success

I have muddled through........with all your help!!

Private Sub Form_Current()
DoCmd.Maximize
With Me
If .OrderClosed = True Then
.OrderStatus.ForeColor = 255
Else
.OrderStatus.ForeColor = 16711680

Dim ctlControl As Control

If Me.TotalGross >= "0.00" And Me.OrderBalance <= "0.00" Then
Me.OrderClosed = -1
For Each ctlControl In Me.Controls
On Error Resume Next
ctlControl.Locked = True
Next
Me.OrderClosed.Locked = False
Else
Me.OrderClosed = 0
For Each ctlControl In Me.Controls
On Error Resume Next
ctlControl.Locked = False
Next
End If
End If
End With
End Sub
CODE] 

Many thanks,
Phil.

 

[___]

Where there's a will...

 

[scouser]

Spoke too soon!!

When I change the order status the font colour does not update although the staus does. If I click to another order then click back the colour changes. How do I get it font colour to update along with the status?
Last one I promise!!!

 

[___]

     Me.Form.Refresh

 

[Fizzio]

Tidied up a bit

Private Sub Form_Current()
Dim ctlControl As Control

DoCmd.Maximize

With Me
     If .TotalGross >= "0.00" And .OrderBalance <= "0.00" Then
          .OrderClosed = True
          .OrderStatus.ForeColor = 255
          For Each ctlControl In .Controls
               On Error Resume Next
               ctlControl.Locked = True
          Next
     .OrderClosed.Locked = False
     Else
     .OrderClosed = False
     .OrderStatus.ForeColor = 16711680
     For Each ctlControl In .Controls
          On Error Resume Next
          ctlControl.Locked = False
          Next
     End If

End With
End Sub

Took out the superfluous me. in the code. This combines the two if..then loops to apply the formatting as the conditional code runs. hth

 

[Mile-O]

See those fingers have not aged then Mile-O (git)

...but of course!

 

[scouser]

Clever People

Oh to be a coder.........................Guys your advice has been much appreciated. I still can't get the bl**dy colour to change on the text box after checking / un-checking?
By this I mean:
I am in an open order in the order form and the text box displays code 'Order Open' in blue font as the check box is un-checked in the tabbed form 'Payments'. Good
I then click the cmdPayements button from the main order form to open the 'Payments form and enter a payment so order balance = 0. I then check the box top close the order.
I then select the cmdOrders button. The 'OrderStatus' amends to 'Order Closed' but the font is still blue. It only changes to RED after I navigate away from the current order and back again.

I have tried adding

Me.Form.Refresh

on EVERYTHING!! Ha!
Any thoughts (I thought this would be simple!!!!).
Phil.

 

[Rich]

You need to Call the Form_Current event on the after update event of the checkbox

 

[scouser]

Forgive My Ignorance

Rich I currently have the following on the afterUpdate of the check box. How would I call the onCurrent event?

' Displays appropiate message dependent on check box value
Private Sub OrderClosed_AfterUpdate()

Dim ctlCurrent As Control

If Me.OrderClosed = 0 Then
MsgBox "This Order is now open"

For Each ctlControl In Me.Controls
On Error Resume Next
ctlControl.Locked = False
Next
Else
MsgBox "This Order in now closed"
For Each ctlControl In Me.Controls
On Error Resume Next
ctlControl.Locked = True
Next
Me.OrderClosed.Locked = False
End If

End Sub

Cheers,
Phil

 

[Mile-O]

Simply put the following line before your End Sub line

Call Form_Current

 

[scouser]

That worked.......but

Mile-O, that worked but I then couldn't un-check the box or edit the record! On clicking the check box the message 'This order is now open' was displayed but the check box remained checked!!
Am I a pain in the a**e or what...............and to cap my day Liverpool have lost!!
Humbug....................
Phil.