Conditional Formatting (AfterUpdate)

[lhooker]

Is it possible to change the color (to red) of text in a textbox after it's update. I don't see how to do this in the 'New Formatting Rule' in 'Conditional Formatting' of the 'Format' section.

 

[arnelgp]

Im not sure if fieldname.value <> fieldname.oldvalue will do the trick.

 

[Wayne]

In the AfterUpdate Event of that field, try this code:

Private Sub YourFieldName_AfterUpdate()
    
    Me.YourFieldName.ForeColor = 255
    
End Sub

That will change the font color to red, but it will remain that way. If you want it conditional to a value, try:

Private Sub YourFieldName_AfterUpdate()
    
If Me.YourFieldName > 0 Then   
        Me.YourFieldName.ForeColor = 255
Else
        Exit Sub
End If
    
End Sub

If it's a more complicated condition, try a Select Case statement.

Hope this helps.

Wayne

 

[lhooker]

Wayne,

This did not work (see attachment). I want the value in 'Bill Due Date' to change to red afterupdate (i.e. 'Add Record').

 

[JHB]

Is that what you're looking for?

Private Sub AddRecord_Click()
  ...
  Me.[New Bill_Due_Date] = ""
[B]  Me.[Bill_Due_Date].ForeColor = 255[/B]
  Requery
  ...

 

[Wayne]

This code won't work, because you are not referencing the forms and controls properly.:

Rem If Forms![Bill_Due_Date.Value] <> Form![Bill_Due_Date].OldValue Then MsgBox "Here"

Me.Bill_Due_Date.Value or [Forms]![Form_Bills]![Bill_Due_Date].Value are the proper ways.

Also, the new value will be greater or less than the old value, so it will always be red. If you want something that will turn it red if it's less than 10 days to go before the bill is due, you could use:

If ([Date] - Me.Bill_Due_Date.Value) < 10 Then
     Me.Bill_Due_Date.ForeColor = 255
Else
     Exit Sub
End If

If that's not what you are looking for, try and be a little more specific.

Wayne

 

[lhooker]

Wayne,

This did not work either. I want the value in 'Bill Due Date' to change to red after it's been updated to a new date. I'm trying to accomplish this is 'afterupdate'. The value is changed after clicking on the 'Add Record' button.

 

[arnelgp]

Your sample db is not updatable. What is going on.
Chech this updatable one. Change the date and add the record. See the color.

 

[Wayne]

Try adding the line in red below to your code. It works on your sample database.

Private Sub AddRecord_Click()

Dim Account As String
Dim LastPaid As Date
Dim NewPaymentDate As Date
Dim Last_Bill_Due_Date As Date
Dim Bill_Due_Date As Date

If IsNull(txtBillName) Then
   MsgBox "You have not selected an account.", vbOKCancel, "            No Account Selected"
   Exit Sub
End If

If IsNull(Me.[New Bill_Due_Date]) Then
   MsgBox "You have not selected a New Bill Due Date. ", vbOKCancel, "            No Bill Due Date"
   Exit Sub
End If

DoCmd.SetWarnings False

Account = [lstBillName].[Column](0)
NewPaymentDate = Me.[New Bill_Due_Date]
LastPaid = Me.[LastPaid]

Last_Bill_Due_Date = Me.[Bill_Due_Date]

On Error GoTo Err_AddRecord_Click

'create a new record

DoCmd.RunSQL "INSERT INTO Bills (Bill_Name, Last_Paid, NextPaymentDate) VALUES ('" & Account & "', '" & Last_Bill_Due_Date & "', '" & NewPaymentDate & "')"

MsgBox "The new bill due date (" & NewPaymentDate & ") for " & "'" & Account & "'" & " has been entered. This screen reflects the new bill due date.", vbOKCancel, "                                        New Bill Due Date Added"

Me.[New Bill_Due_Date] = ""
[COLOR="Red"]Me.Bill_Due_Date.ForeColor = 255
[/COLOR]
Requery

DoCmd.SetWarnings True

Exit_AddRecord_Click:
    Exit Sub

Err_AddRecord_Click:
    MsgBox Err.Description
    Resume Exit_AddRecord_Click
    
End Sub

 

[arnelgp]

you're approach will change the color to red yes even those other Cards that has not changed their date. Mine changes the color only on those cards that you change date.

 

[lhooker]

Wayne,

That worked . . . Thanks ! ! !

 

[lhooker]

Arnelgp,

Wayne solution worked. Did I miss something, what were you referring to ?

 

[arnelgp]

From waynes code Try selecting a card from the list that you did not change the date. red color? Why red when you did not change it.

Try my sample and change the date on any card from the list and press save button.
Now select from the list that you did not change.
Go back again and select from the list the one you changed.

 

[lhooker]

Arnelgp,

You're right . . . I fixed this by adding
Me.Bill_Due_Date.ForeColor = 0 in BeforeUpdate. Also, did you load the wrong version for your solution ? The textbox does not change the color to red.

 

[arnelgp]

Maybe i did not. I have lots of test db on my computer. Chk this one.

 

[lhooker]

Arnelgp,

You still uploaded the wrong database . . . Nothing changed.

 

[arnelgp]

I am using a2016. It worked for me. This is the accd version (a2016).

 

[lhooker]

Arnelgp,

Got it . . . Thanks ! ! !

 

[Wayne]

Hi Arnel,

I did warn him it would always be red in my earlier post if he didn't specify some condityion for it. See below:

This code won't work, because you are not referencing the forms and controls properly.:

Code:
Rem If Forms![Bill_Due_Date.Value] <> Form![Bill_Due_Date].OldValue Then MsgBox "Here"
Me.Bill_Due_Date.Value or [Forms]![Form_Bills]![Bill_Due_Date].Value are the proper ways.

Also, the new value will be greater or less than the old value, so it will always be red. If you want something that will turn it red if it's less than 10 days to go before the bill is due, you could use:

Code:
If ([Date] - Me.Bill_Due_Date.Value) < 10 Then
Me.Bill_Due_Date.ForeColor = 255
Else
Exit Sub
End If
If that's not what you are looking for, try and be a little more specific.

Wayne

 

[lhooker]

Wayne/Arnelgp,

It works . . . Thanks to the both of you ! ! !