Highlight Changes in Form

[MHamid]

Hello,

Would it be possible to highlight any/all updated fields in a form inAccess?
I’m looking to highlight the first update instance in Red with theupdated date.
Then I need to highlight any second updates in Blue.
Is this possible?

Thank you

 

[xenou]

It is possible to use highlighting and even conditional formatting in MSAccess. I have used it very infrequently so I can't personally give advice but I'm sure you can easily find examples online. As far as deciding what is "first", "second", etc. there is no way to know that automatically in MSAccess. You need to be able to define what is first, second, third, and so on from the data you have stored. For instance, if you have a "DateModified" field in your database, then you can use that to tell what is first or second. I would prefer to simply list the values in order, so there's really no need for special colors.

 

[MHamid]

Hello,

In your opinion … If I were to use a “DateModified” field, do you thinkit would be best to have this field be a user input field instead of acalculated formula (or vba code field) that will automatically enter themodified date once the data has beenmodified?

Thank you

 

[xenou]

I would always prefer that field be automatically populated. Otherwise it simply won't be reliable but in any case you wouldn't want users to have to incur the extra work unnecessarily.

 

[MHamid]

Hello,

I tried using the following code for to highlight any fields edited toblue and add a modified date, but it is not working properly. What am I doingwrong?

[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub Form_AfterUpdate()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim clt As Control[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    For Each clt InScreen.ActiveForm.Controls[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        If (clt.ControlType =acComboBox _[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]            Or clt.ControlType =acTextBox _[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]            Or clt.ControlType =acListBox) Then[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                If Nz(clt.Value)<> Nz(clt.OldValue) Then[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                   clt.Value.ForeColor = vbBlue[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                   Me.ModifiedDate = Now()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                End If[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        End If[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Next[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]

Thank you

 

[xenou]

Did you base this on an example from a web page? If so what is the source? Also when you say doesn't work properly does that mean it errors, does nothing, only does part of what you want, or does extra things you don't want?

 

[MHamid]

Hello,

This is my own code. I am getting an error code (Run-time error ‘424’:Object required) with the highlighted section below when I click on DEBUG.

[FONT=Calibri][SIZE=3][COLOR=#000000]                   clt.Value.ForeColor = vbBlue[/COLOR][/SIZE][/FONT]

Thank you

 

[xenou]

You should be able to narrow that down to which control clt refers to (as you loop through the controls, which one is it on when this happens). Not sure why it would be a missing object but that's debugging.

Also I don't really consider the use of Screen.ActiveForm necessary. You shouldn't be relying on whatever happens to be active. The code should probably be in the form and using a syntax such as

PSEUDOCODE:

For Each Control in ME.Controls ...

 

[xenou]

Also I'm not sure why you would use a Form_AfterUpdate event here. That's after the data is committed so what's the reasoning here? Not entirely sure why users wouldn't know what they literally just changed anyway. It's generally pretty clear that if you change something in a form then that's something that has changed.

 

[MHamid]

My thought process is that the user will be updating any field. So it would be hard to narrow down which field the user will update. That is why I was thinking if this was a good way to highlight the changes as it occurs. The code is already on the Form. Do I need to specify what field exactly i will need changed to blue font when I will not know what field will be updated?

Thank you