Capture OLD VALUE of field based on Event

Question

For Access 2010:  I know how to use AFTER UPDATE to capture Modified By & Date Modified.

However, I would like to use BEFORE UPDATE to capture the current value of a specific field, [CURRENT DATA SOURCE], and to set or copy this value into another field, let's call it SourceCurVal

I would like to -avoid- creating a separate audit table.

I did receive replies suggesting that I use .OldValue in VBA, but Access is throwing up a compile error for this.

Thanks!

=====

---

A. Wolf

Answer 1

Any bound control on a form has an ".OldValue" property so you shouldn't have received an error.  Can you show us the code causing the error and the exact error message?

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for paraphrasing her words.

Answer 2

Sure:

Private Sub DATA_SOURCE_AfterUpdate(Cancel As Integer)

'Me.FieldNameForPreviousValue.Value = Me.FieldNameChanged.OldValue -- code from above'

Me.[Current Data Source Old Value].Value = Me.[CURRENT DATA SOURCE].OldValue

End Sub

Here's the error:

Compile error:  Method or data member not found

Method highlighted = .OldValue

Thanks!

---

A. Wolf

Answer 3

Hi Amy,

Is the name of the control/column "Current Data Source" or "Data Source"?  If it's "Data Source" then the code should be;

Me.[Current Data Source Old Value].Value = Me.[DATA SOURCE].OldValue

You should also avoid spaces in the names of controls and database columns, use camel case or an underscore character such as;

Me.[CurrentDataSourceOldValue].Value = Me.[CURRENT_DATA_SOURCE].OldValue

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for paraphrasing her words.

Answer 4

Yes, the name of the field/control is [Current Data Source].

This is a legacy database and I did not create the field names.

Any clues as to why .OldValue is throwing up a compile error?

Thanks!

Amy

====

---

A. Wolf

Answer 5

Hi Amy,

I think what is happening is the you have a control named "Data_Source" (which is why the event is called "DATA_SOURCE_AfterUpdate") and a column called "Current Data Source".  The control has the ".OldValue" but the underlying column doesn't hence the "Method or data member not found" error.

Check to make sure you are referencing the control not the column.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for paraphrasing her words.

Answer 6

Hi Wolf,

Welcome to the MSDN forum!

I temporarily marked the reply as answer and you can unmark it if it provides no help.

Thanks for your understanding and have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us