Showing Changes with Font Color

[RichB]

I did some searching on changing the color of a font if you change the data. I found a few posts but they really don't fit the need.

I am using a continuous form (which makes this even more difficult) and when someone changes data in a field I want that forecolor to change from black to red.

Is there an easy way (I know not everything is easy) in the conditional formatting to create an event? I am thinking not but thought I would ask.

My GUESS is that I need some sort of temp table to verify the data against then return with the forecolor change.

I am using A2k.

Thanks RichB

 

[godofhell]

Note: The code velow compares the original value with the new value entered, if they match no change is done, if they don't match it changes the color of the field modified. The problem I am having is telling the database that I want only the field of the modified record to change. Currently the change appears on all the fields on the continues form.

Private Sub Data1_AfterUpdate()
CheckValues
End Sub

Private Sub Data2_AfterUpdate()
CheckValues
End Sub

Sub CheckValues()
Dim OriginalData1 As String
Dim OriginalData2 As String

OriginalData1 = Me.Data1.OldValue
OriginalData2 = Me.Data2.OldValue

If OriginalData1 = Data1.Value Then
Else
Data1.BackColor = "255"
End If

If OriginalData2 = Data2.Value Then
Else
Data2.BackColor = "16744448"
End If
End Sub

 

[RichB]

Thanks, I will give it a go this week. I will post back when I either get it going or not.

 

[RichB]

I started to play with this but I can't call the sub. It keeps rejecting with "Expected variable or procedure, not module. I looked in help and Access 2000 Bible and can't determine why this will not call the sub?

 

[RichB]

Never mind I was not thinking and named the module the same as the sub. I also changed the sub to a function.

As far as all of the fields updating . . . that is the nature of continuous forms. No real way around that as far as I know. There are some pretty good explainations on this if you search this site.

 

[godofhell]

As I was playing with the database last night, I came accross something that looked promissing. It involved counting the records, then assigning the value of the count to a control that would change color when the record was being edited. Basically what it did was turn the color on on the control but only for the record that matched the number in the control. It seemed promissing since I was able to click the fields and have the control change the background of the selected field. I was not able to figure out how to keep the color. Will try playing with it as time permits.

 

[RichB]

If you think about it, keep me posted. I will post also if I can do something along these lines too. Other posts that I have seached on are pretty negative for what we are trying to do. But . . . I am suborn and will continue to go for it. Nothing but time, right? I am almost bald anyway so what's a few more hairs

 

[gizzu]

Are multiple users going to use this form at the same time?

If not, have a try with what you suggested yourself in your first post:

make a new field in the table, let's say it's named "fc", make it boolean and set the standard to false.
Then, in the form's On_Load Event, update the table to set the value of fc to false for all record. In the On_Change (or On_Dirty, play around with some different events), set the value of fc for the current record to true. Then add a format condition to the text box and make a new condition with the expression =fc.value=true. If this won't work, play around with some different events, or try a thetextbox.repaint after you set the fc.value to true. However, the .RePaint method only repaints the object if Access indicates there is a repaint needed. To force a repaint of all objects on screen, do this instead:

Application.Echo False:Application.Echo True

 

[gizzu]

You don't have to play around with any RePaint's. I've attached a db that do what you want. Have a nice day!

 

[RichB]

Outstanding, Thank You. It works great for records that are already there but when you add records they automatically change color. I am still playing so that they initially go in with a forecolor of black and then go red after they are changed.

Any ideas, I'm still pluggin'

RichB

 

[RichB]

The only way that I can see getting this to work was to add a check box on the form header named Change_Data.

I want to keep the changes so I got rid of the On_Format DoCmd from your example.

When the Change_Data checkbox is true then fc will go to true else it will stay false.

This works and could make my users aware that they are making changes. Atleast that's what I will tell them because most of the time they don't know what they want anyway

 

[godofhell]

Ok, I have downloaded the attached database and made a few changes to get the new record not to change color, once the record is added and if changed, the color will change. I can not see where the yellow color and red text are coming from. Can you tell me where those are pulled from? In order to prevent an error from raising when setting the fc.value =False on the Form_AfterInsert() event, I added the fc field to the right of the value field and set its properties to Display = No. I have attached the modified database. Please let me know where you are referencing the yellow color. Below is the New code.

'Loads the form and removes the Update records warning message.
Private Sub Form_Load()
DoCmd.SetWarnings False
DoCmd.RunSQL "update tmptbl set fc=false"
End Sub

'This portion makes sure that the color is not changed on new records
Private Sub Form_AfterInsert()
fc.Value = False
End Sub

'Does the same as the on change procedure that was being used
Private Sub thevalue_AfterUpdate()
fc.Value = True
End Sub

 

[RichB]

AfterInsert does the trick without another field like I did. Good job. The yellow and red are done with conditional formating on the thevalue field. In design view right click on thevalue and select conditional formatting and you will see the condition. Change to three conditions.

 

[gizzu]

You use the thefield_AfterUpdate() Event (was it?). This causes the red/yellow colors not to show up until the user leaves the field. In my original version, I used the _Change() event. But you didn't want the red/yellow to show up at new records, just changed. This is easiest done by changing the line in the thevalue_Change() event to

If Not Me.NewRecord Then fc.Value = True

I've attached the modified db to show the red/yellow colors when the user begins to change the value, but still not show the red/yellow for new records.

 

[RichB]

gizzu, thought I tried If Not Me.NewRecord. . . but . . . obviously I did not because it works fine. Thanks for the help

 

[godofhell]

RichB, thank you for letting me know where to find that. That gives me more toys for play. As long as I have been programming I have never found the need to that feature, go figure, it does something. Thank you.