none
Use BEFORE UPDATE & VBA to copy current field value into another field

    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.

    Thanks!

    =====


    A. Wolf

    Tuesday, June 19, 2012 7:28 PM

All replies

  • me.controlName.oldValue


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Tuesday, June 19, 2012 8:36 PM
  • Thanks.  What is the whole code line?

    Ex:  me.FieldName.Value = me.NewDummyField.Value

    -------------------------------------------------------------------

    Is this correct?

    Thanks!


    A. Wolf

    Thursday, June 21, 2012 8:16 PM
  • You can continue to use the procedure handling the After update event (of the control, not of the form) to get the original value that the field was holding.

    Me.FieldNameForPreviousValue.Value = Me.FieldNameChanged.OldValue

    The fields have OldValue and Value properties. the difference is that OldValue holds the last commited value into the database. After a successfull record save, that OldValue becomes the actual value. UNDOing the field push the OldValue back into the Value. Since Value is the default property for a control, you can use:

    Me.FieldNameForPreviousValue.Value = Me.FieldNameChanged.OldValue

    as well as

    Me.FieldNameForPreviousValue = Me.FieldNameChanged.OldValue

    Thursday, June 21, 2012 8:25 PM
  • Vanderghast:

    I am using Access 2010, and there is no '.OldValue' -- Access rejects this.

    Could you repeat the above code so that it reflects usable VBA code?

    Thanks!

    ====


    A. Wolf

    Friday, June 22, 2012 1:26 AM
  • Vanderghast:

    I am using Access 2010, and there is no '.OldValue' -- Access rejects this.

    Could you repeat the above code so that it reflects usable VBA code?

    Thanks!

    ====


    A. Wolf

    2010 does.  I just did it  here is an example

    Private Sub Command2_Click()
        Me.prevVal = Me.Field1.OldValue
    End Sub


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Friday, June 22, 2012 1:34 AM
  • I am using Access 2010, and there is no '.OldValue' -- Access rejects this.

    why not post your code so we can take a look.  What does Access do?  What type of controsl are you using it on?  Are the controls bound or unbound?

    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Friday, June 22, 2012 1:56 AM
  • Indeed, it does in 2010, but be sure that you use a bound form.

    Friday, June 22, 2012 1:56 PM
  • Sure: 

    I am using a bound form in Access 2010.  Here is my code for After Update on the field whose value will change [CURRENT DATA SOURCE]:

    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


    • Edited by amywolfie Friday, June 22, 2012 5:17 PM
    Friday, June 22, 2012 5:16 PM
  • is [current data source] a bound text box?

    is the spelling correct?


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Friday, June 22, 2012 5:33 PM
  • Yes, it is.  Thanks.

    A. Wolf

    Friday, June 22, 2012 6:27 PM
  • CAN ANYONE OUT THERE HELP?

    Thanks.

    ====


    A. Wolf

    Monday, June 25, 2012 4:20 PM
  • This question thread as been marked as answered and .OldValue is the answer, but if it's still not working for you, you can unmark the question as answered or start a new thread, because this thread is unlikely to be seen by anyone new as it is already marked as resolved.  I don't know why you are getting a compile error on .OldValue maybe Vanderghast has a suggestion, but more people will see your post if it is not marked as answered.  You can try a decompile, compact/repair, and then compile to see if that resolves the issue.

    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.


    • Edited by -suzyQ Monday, June 25, 2012 4:53 PM
    Monday, June 25, 2012 4:52 PM