VBA Old Value > New Value (1 Viewer)

shamas21

Registered User.
Local time
Today, 13:13
Joined
May 27, 2008
Messages
162
Hi All

I have a table called "table 1". It has two column in it
1. ID
2. Name

All I want is if someone changes a field in "table1" then to bring back two message boxes stating the old value and new value.

I believe this can be done with the OriginalValues property?

Can anyone help?

Code:
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Table1")
    
' i.e. Msgbox old value
' i.e. Msgbox new value
 
'MsgBox fld.OriginalValue
 

ajetrumpet

Banned
Local time
Today, 08:13
Joined
Jun 22, 2007
Messages
5,638
Hopefully they're working inside of forms. If they are, you can then tell when the field values have been altered. if they're changing the data in the table itself, then you cannot tell because table's dont' have things like the OnDirty property and such to detect changes. But at any rate, IN A FORM, the Dirty property is what you want, but only if the fields are bound to the table, which they should be.

The only thing I can think of is to store the field values inside of variables with the OnCurrent property, then if the form (field values) are changed (Dirtied), display the message box with the variable value, then change the variable to the new field value that is present, then display another message. I'm guessing this would be the easiest way to do it...


EDIT

on second thought, the afterUpdate event is what you want. NOT the Dirty event. Sorry about that.
 

WayneRyan

AWF VIP
Local time
Today, 13:13
Joined
Nov 19, 2002
Messages
7,122
Shamas,

If it's on a form, with "bound" controls, you can use:

MsgBox "New Value = " & Me.YourControl
MsgBox "Old Value = " & Me.YourControl.OldValue

If it is JUST a DAO recordset, then you can look up

rst.Field.OriginalValue.

I haven't used this in a DAO context.

Wayne
 

boblarson

Smeghead
Local time
Today, 06:13
Joined
Jan 12, 2001
Messages
32,059
EDIT

on second thought, the afterUpdate event is what you want. NOT the Dirty event. Sorry about that.
Actually, it would be the Before Update event. If you do it on the After Update event, the Old Value has changed to the new value now.
 

shamas21

Registered User.
Local time
Today, 13:13
Joined
May 27, 2008
Messages
162
Thanks Bob. :)


Thats great, I understand the concept.

So now i have linked my form to table1.

But I have like 30 fields on this table. Surely i can do a beforeUpdate and AfterUpdate for every field.

What i really need is for the computer to know which field has been modified rather than writing if then statements for each field.

Any ideas? thanks
 

boblarson

Smeghead
Local time
Today, 06:13
Joined
Jan 12, 2001
Messages
32,059
Thats great, I understand the concept.

So now i have linked my form to table1.

But I have like 30 fields on this table. Surely i can do a beforeUpdate and AfterUpdate for every field.

What i really need is for the computer to know which field has been modified rather than writing if then statements for each field.

Any ideas? thanks

You don't need to do anything in the AFTER UPDATE event. And, the BEFORE UPDATE event I was talking about was the FORM'S before update event, not the individual controls. But, if you want to capture when something has changed, even if they change it back to the original then you would need to use each control's After Update Event (I believe).
 

WayneRyan

AWF VIP
Local time
Today, 13:13
Joined
Nov 19, 2002
Messages
7,122
Shamas,

I think you can use the form's BeforeUpdate event and loop through all of
your controls checking for --> Me.Control <> Me.Control.OldValue.

There are examples on this forum for looping through all controls.

You might isolate a check to see if the form has a ControlSource, THEN
check for the OldValue.

Wayne
 

Users who are viewing this thread

Top Bottom