How to 'flag' a field has changed

[Dj]

Here's what I'm trying to do...

I have a report weekly report that is sent out company wide everyday. Every
Monday, it's refreshed with new data and some of the data may be modified
throughout the week.

When a field is changed, I want that field to be highlighted on the report.
I tried using the ONCHANGE, [field].TAG = "changed", and then using
conditional formatting based on my TAG, but TAG doesn't seem to be record
specific.

Is what I'm trying to do possible? Is there a hidden field part that I can
used to track whether or not that filed had changed that I can also reference
in Reports?

Thanks! Dozzee

 

[Klatuu]

First, the Change event would not be the place to do this. The Change event
fires on all changes. Than means, after every keystroke because the value in
the control has changed.

The Tag is specific to the control, not the data in the control. There is
one instance of the control and will present multiple records as you navigate
through your recordset.

What you want to do is difficult because it changed this week, so you have
set a flag to show it as changed, but next week it doesn't change again, so
the flag may still show it as changed even though it has not. Also, using
the After Update event of the control to mark a flag instead of the Change
event would be unreliable, because you could change the value in the control,
but still not update the record, so the flag would show as changed, but the
value did not change.

Get's sticky, doesn't it?

So, a short description of what you might consider would be to create an
additional table to show changes. You would need to carry the primary key
value of the main table, the name of the field, the old value, the new value,
and the date and time of the change.

Then in the Before Update event of the form you can capture the OldValue
property of the control and the Value of the control in a form level module.
You don't want to actually create the record because the change has not yet
been applied. Then in the form After Update, append a record to the change
table.

 

[Dj]

First of all, thank you for the quick and detailed response. I understood
'most' of what you said.

As far as the example you gave of data changing this week, but not next I
don't believe is a problem because each week I import a whole new set of
records that we only reference and need to know what fields change during
that first week. After Friday, we no longer really refeence that week's
records nor does it matter if a field is changed after that.

I do have a question about the "after change" you mentioned. You said that
I could change a field but not update a record. How is that possible. I
thought once you changed a field in a record, that record was automatically
updated? Could you further explain that?

My report has about 20 fields (and on averge 30 records weekly) that can
change and they are of different types (text, date and number) so I'm
thinking it may be more trouble than it's worth. If I did decide to give
this a try, I'm still a little confused on how I would reference that change
table in conjunction with my data table to set my formatting.

Thanks again!

First, the Change event would not be the place to do this. The Change event
fires on all changes. Than means, after every keystroke because the value in
the control has changed.

The Tag is specific to the control, not the data in the control. There is
one instance of the control and will present multiple records as you navigate
through your recordset.

What you want to do is difficult because it changed this week, so you have
set a flag to show it as changed, but next week it doesn't change again, so
the flag may still show it as changed even though it has not. Also, using
the After Update event of the control to mark a flag instead of the Change
event would be unreliable, because you could change the value in the control,
but still not update the record, so the flag would show as changed, but the
value did not change.

Get's sticky, doesn't it?

So, a short description of what you might consider would be to create an
additional table to show changes. You would need to carry the primary key
value of the main table, the name of the field, the old value, the new value,
and the date and time of the change.

Then in the Before Update event of the form you can capture the OldValue
property of the control and the Value of the control in a form level module.
You don't want to actually create the record because the change has not yet
been applied. Then in the form After Update, append a record to the change
table.
--
Dave Hargis, Microsoft Access MVP

Here's what I'm trying to do...

I have a report weekly report that is sent out company wide everyday. Every
Monday, it's refreshed with new data and some of the data may be modified
throughout the week.

When a field is changed, I want that field to be highlighted on the report.
I tried using the ONCHANGE, [field].TAG = "changed", and then using
conditional formatting based on my TAG, but TAG doesn't seem to be record
specific.

Is what I'm trying to do possible? Is there a hidden field part that I can
used to track whether or not that filed had changed that I can also reference
in Reports?

Thanks! Dozzee

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP

First of all, thank you for the quick and detailed response. I understood
'most' of what you said.

As far as the example you gave of data changing this week, but not next I
don't believe is a problem because each week I import a whole new set of
records that we only reference and need to know what fields change during
that first week. After Friday, we no longer really refeence that week's
records nor does it matter if a field is changed after that.

I do have a question about the "after change" you mentioned. You said that
I could change a field but not update a record. How is that possible. I
thought once you changed a field in a record, that record was automatically
updated? Could you further explain that?

No. A record is not updated until you explictly update it, navigate to a
different record, create a new record, or close the form. Even if you change
values in controls, you can undo the changes. Pressing Escape once will undo
the active control. Pressing it twice will undo the entire record. You can
cancel an update using the form's Before Update event by setting Cancel =
True.

My report has about 20 fields (and on averge 30 records weekly) that can
change and they are of different types (text, date and number) so I'm
thinking it may be more trouble than it's worth. If I did decide to give
this a try, I'm still a little confused on how I would reference that change
table in conjunction with my data table to set my formatting.

Referencing a change record to your your main record would be like relating
any other two tables. You would carry the main record's primary key as a
foreign key in the change table.

Given your circumstances, it would not be that difficult. Just include a
field in the Change table to relate the primary key field of the main table.
Then add a field for each field you want to track that has the same data type
and length as the field in the main table.

Now, in the Before Update event of the form, create an SQL string (dimmed at
the module level) using the OldValue property of each field. It would be an
Append query. Then in the After Update event of the form, execute the query
to add a record to the Change table.

Now, in the query for the record source of your report, join the tables and
include in the query a calculated field for each field you want to track that
signifies whether a change has been made. When you load new data for your
main table, just delete all the records in the change table.

Thanks again!

First, the Change event would not be the place to do this. The Change event
fires on all changes. Than means, after every keystroke because the value in
the control has changed.

The Tag is specific to the control, not the data in the control. There is
one instance of the control and will present multiple records as you navigate
through your recordset.

What you want to do is difficult because it changed this week, so you have
set a flag to show it as changed, but next week it doesn't change again, so
the flag may still show it as changed even though it has not. Also, using
the After Update event of the control to mark a flag instead of the Change
event would be unreliable, because you could change the value in the control,
but still not update the record, so the flag would show as changed, but the
value did not change.

Get's sticky, doesn't it?

So, a short description of what you might consider would be to create an
additional table to show changes. You would need to carry the primary key
value of the main table, the name of the field, the old value, the new value,
and the date and time of the change.

Then in the Before Update event of the form you can capture the OldValue
property of the control and the Value of the control in a form level module.
You don't want to actually create the record because the change has not yet
been applied. Then in the form After Update, append a record to the change
table.
--
Dave Hargis, Microsoft Access MVP

Here's what I'm trying to do...

I have a report weekly report that is sent out company wide everyday. Every
Monday, it's refreshed with new data and some of the data may be modified
throughout the week.

When a field is changed, I want that field to be highlighted on the report.
I tried using the ONCHANGE, [field].TAG = "changed", and then using
conditional formatting based on my TAG, but TAG doesn't seem to be record
specific.

Is what I'm trying to do possible? Is there a hidden field part that I can
used to track whether or not that filed had changed that I can also reference
in Reports?

Thanks! Dozzee

 

[Dj]

That makes it a bit more clear to me now. I think I'll give it a try.
Thanks bunches.

--
Dave Hargis, Microsoft Access MVP

First of all, thank you for the quick and detailed response. I understood
'most' of what you said.

As far as the example you gave of data changing this week, but not next I
don't believe is a problem because each week I import a whole new set of
records that we only reference and need to know what fields change during
that first week. After Friday, we no longer really refeence that week's
records nor does it matter if a field is changed after that.

I do have a question about the "after change" you mentioned. You said that
I could change a field but not update a record. How is that possible. I
thought once you changed a field in a record, that record was automatically
updated? Could you further explain that?

No. A record is not updated until you explictly update it, navigate to a
different record, create a new record, or close the form. Even if you change
values in controls, you can undo the changes. Pressing Escape once will undo
the active control. Pressing it twice will undo the entire record. You can
cancel an update using the form's Before Update event by setting Cancel =
True.

My report has about 20 fields (and on averge 30 records weekly) that can
change and they are of different types (text, date and number) so I'm
thinking it may be more trouble than it's worth. If I did decide to give
this a try, I'm still a little confused on how I would reference that change
table in conjunction with my data table to set my formatting.

Referencing a change record to your your main record would be like relating
any other two tables. You would carry the main record's primary key as a
foreign key in the change table.

Given your circumstances, it would not be that difficult. Just include a
field in the Change table to relate the primary key field of the main table.
Then add a field for each field you want to track that has the same data type
and length as the field in the main table.

Now, in the Before Update event of the form, create an SQL string (dimmed at
the module level) using the OldValue property of each field. It would be an
Append query. Then in the After Update event of the form, execute the query
to add a record to the Change table.

Now, in the query for the record source of your report, join the tables and
include in the query a calculated field for each field you want to track that
signifies whether a change has been made. When you load new data for your
main table, just delete all the records in the change table.

Thanks again!

First, the Change event would not be the place to do this. The Change event
fires on all changes. Than means, after every keystroke because the value in
the control has changed.

The Tag is specific to the control, not the data in the control. There is
one instance of the control and will present multiple records as you navigate
through your recordset.

What you want to do is difficult because it changed this week, so you have
set a flag to show it as changed, but next week it doesn't change again, so
the flag may still show it as changed even though it has not. Also, using
the After Update event of the control to mark a flag instead of the Change
event would be unreliable, because you could change the value in the control,
but still not update the record, so the flag would show as changed, but the
value did not change.

Get's sticky, doesn't it?

So, a short description of what you might consider would be to create an
additional table to show changes. You would need to carry the primary key
value of the main table, the name of the field, the old value, the new value,
and the date and time of the change.

Then in the Before Update event of the form you can capture the OldValue
property of the control and the Value of the control in a form level module.
You don't want to actually create the record because the change has not yet
been applied. Then in the form After Update, append a record to the change
table.
--
Dave Hargis, Microsoft Access MVP


:

Here's what I'm trying to do...

I have a report weekly report that is sent out company wide everyday. Every
Monday, it's refreshed with new data and some of the data may be modified
throughout the week.

When a field is changed, I want that field to be highlighted on the report.
I tried using the ONCHANGE, [field].TAG = "changed", and then using
conditional formatting based on my TAG, but TAG doesn't seem to be record
specific.

Is what I'm trying to do possible? Is there a hidden field part that I can
used to track whether or not that filed had changed that I can also reference
in Reports?

Thanks! Dozzee

 

[Klatuu]

Okay, post back if you have any questions setting it up.
--
Dave Hargis, Microsoft Access MVP

That makes it a bit more clear to me now. I think I'll give it a try.
Thanks bunches.

--
Dave Hargis, Microsoft Access MVP

First of all, thank you for the quick and detailed response. I understood
'most' of what you said.

As far as the example you gave of data changing this week, but not next I
don't believe is a problem because each week I import a whole new set of
records that we only reference and need to know what fields change during
that first week. After Friday, we no longer really refeence that week's
records nor does it matter if a field is changed after that.

I do have a question about the "after change" you mentioned. You said that
I could change a field but not update a record. How is that possible. I
thought once you changed a field in a record, that record was automatically
updated? Could you further explain that?

No. A record is not updated until you explictly update it, navigate to a
different record, create a new record, or close the form. Even if you change
values in controls, you can undo the changes. Pressing Escape once will undo
the active control. Pressing it twice will undo the entire record. You can
cancel an update using the form's Before Update event by setting Cancel =
True.

My report has about 20 fields (and on averge 30 records weekly) that can
change and they are of different types (text, date and number) so I'm
thinking it may be more trouble than it's worth. If I did decide to give
this a try, I'm still a little confused on how I would reference that change
table in conjunction with my data table to set my formatting.

Referencing a change record to your your main record would be like relating
any other two tables. You would carry the main record's primary key as a
foreign key in the change table.

Given your circumstances, it would not be that difficult. Just include a
field in the Change table to relate the primary key field of the main table.
Then add a field for each field you want to track that has the same data type
and length as the field in the main table.

Now, in the Before Update event of the form, create an SQL string (dimmed at
the module level) using the OldValue property of each field. It would be an
Append query. Then in the After Update event of the form, execute the query
to add a record to the Change table.

Now, in the query for the record source of your report, join the tables and
include in the query a calculated field for each field you want to track that
signifies whether a change has been made. When you load new data for your
main table, just delete all the records in the change table.

Thanks again!

:

First, the Change event would not be the place to do this. The Change event
fires on all changes. Than means, after every keystroke because the value in
the control has changed.

The Tag is specific to the control, not the data in the control. There is
one instance of the control and will present multiple records as you navigate
through your recordset.

What you want to do is difficult because it changed this week, so you have
set a flag to show it as changed, but next week it doesn't change again, so
the flag may still show it as changed even though it has not. Also, using
the After Update event of the control to mark a flag instead of the Change
event would be unreliable, because you could change the value in the control,
but still not update the record, so the flag would show as changed, but the
value did not change.

Get's sticky, doesn't it?

So, a short description of what you might consider would be to create an
additional table to show changes. You would need to carry the primary key
value of the main table, the name of the field, the old value, the new value,
and the date and time of the change.

Then in the Before Update event of the form you can capture the OldValue
property of the control and the Value of the control in a form level module.
You don't want to actually create the record because the change has not yet
been applied. Then in the form After Update, append a record to the change
table.
--
Dave Hargis, Microsoft Access MVP


:

Here's what I'm trying to do...

I have a report weekly report that is sent out company wide everyday. Every
Monday, it's refreshed with new data and some of the data may be modified
throughout the week.

When a field is changed, I want that field to be highlighted on the report.
I tried using the ONCHANGE, [field].TAG = "changed", and then using
conditional formatting based on my TAG, but TAG doesn't seem to be record
specific.

Is what I'm trying to do possible? Is there a hidden field part that I can
used to track whether or not that filed had changed that I can also reference
in Reports?

Thanks! Dozzee