none
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

    Tuesday, June 26, 2012 12:21 AM

Answers

  • 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.

    Wednesday, June 27, 2012 10:54 PM

All replies

  • 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.

    Tuesday, June 26, 2012 12:39 AM
  • 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

    Tuesday, June 26, 2012 12:54 AM
  • 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.

    Tuesday, June 26, 2012 1:01 AM
  • 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

    Wednesday, June 27, 2012 6:25 PM
  • 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.

    Wednesday, June 27, 2012 10:54 PM
  • 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

    Monday, July 09, 2012 3:01 AM
    Moderator