Making a history table, how to capture field name

[DSburgh]

I have two forms that are viewed as a Datasheet to allow users to change data in the tables. I want to create a record in the history table each time a field in a record is changed. If a user changes five fields on the same record, I want there to be five different entries in the history table.

The [History] table has the following fields:
Hist_ID
Hist_Material_Code (primary key of table being changed)
Hist_UserID
Hist_Date
Hist_Time
Hist_Old_Value
Hist_New_Value
Hist_Field

The thing I can't figure out is how to capture the name of the field that is being changed. Capturing the new value is pretty straightforward, but I’m not sure which event should I use to populate a variable with the Old value? TIA
Brian

 

[Mike555]

Based on the description of your process, you may be able to...

1. Create a Public Variable at the top of your code page.

Option Compare Database
Option Explicit
[b]Public str as String[/b]

2. Create an On Got Focus event for each form control used in the process, and on the event set your Public Variable (in this example str) equal to the value of the control.

3. Once the user changes the value of the control, the old value will still exist in str. You'll then need to simply INSERT that value into a table via an UPDATE or INSERT query.

 

[DSburgh]

I figured i would have to code for each form object. Oh well, just curious if anyone know an easier way. Thanks Mike555

 

[hwkranger]

Don't use that PUBLIC str as String. publics/globals shouldn't be used like that.

DSburgh,

You do have to code for each form object, unless you handle your data using a class for each dataset. Then in the dataset handling you can call a class for logging changes in data.

If you're not a OO programmer, this may be a little tricky. I suggest that you stick to your approach for coding for each object. At least that will work, and won't leave you with an exposed public variable.


Randall Vollen
National City Bank Corp.

 

[jebry]

Hi Brian!

Use this in the Before Update Event procedure:

Dim cntl As Control
Dim rst As DAO.RecordSet

Set rst = CurrentDb.OpenRecordset("History", dbOpenDynaset)
For Each cntl In Me.Controls
If cntl.ControlType = acTextBox Or (any other controls
that you need to
check) Then
If cntl.OldValue <> cntl.Value Then
rst.AddNew
rst!Hist_Old_Value = cntl.OldValue
rst!Hist_Field = cntl.ControlSource
rst! Add your other fields
rst.Update
End If
End If
Next cntl

Set rst = Nothing

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[MichaelRed]

faq181-291

MichaelRed