Accessing Original Value information in an Access sub-form.

[weightinwildcat]

I am presently working on updating a form used to change requisition requests. When a change is made an email message is sent that gives information on the requisition and line items within the requisition. My supervisor wants future emails to show not only what values are entered, but what the old values are so people can see where a change has been made. What sort of syntax should my code have to make this possible?

Here is the code at present:

Dim rs As Recordset
Dim subRs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

Set subRs = Me.[REQITEM_TBL subform].Form.Recordset

EmailBody = "<div style='font-size:14px;'><h3>Req Info</h3>" & _
"<table border='1' padding='2px 2px 2px 2px;'><tr><td>ReqID:" & _
"</td><td>" & Me.ReqID & _
"</td></tr><tr><td>User:" & _
"</td><td>" & Me.ReqUser & _
"</td></tr><tr><td>Req Type:" & _
"</td><td>" & Me.ReqTypeName & _
"</td></tr><tr><td>Active:" & _
"</td><td>" & Active & _
"</td></tr><tr><td>Req Date:" & _
"</td><td>" & Me.ReqDate & _
"</td></tr><tr><td>Need Date:" & _
"</td><td>" & Me.ReqNeedDate & "</td></tr></table></div>"

Do Until (subRs.EOF)
Dim count As Integer
count = 0 'index place holder used for display Header

'Loop through RS depending on amount of fields in RS
'Displays all fields and corresponding values
For Each field In subRs.fields
'FOV = field.OriginalValue

If (count = 0) Then
EmailBody = EmailBody & "<br/><h3>Req Items</h3><br/>"
count = count + 1
End If
'Displaying Req Item(s) details
'EmailBody = EmailBody & "<br/>" & field.Name & ": " & field.Value & vbCrLf
'EmailBody = EmailBody & "<div style='font-size:14px;'>" & _
"<table border='1' padding='2px 2px 2px 2px;'><tr><td>" & _
field.Name & "</td><td>" & field.value & "</td></tr></table>"
'EmailBody = EmailBody & "<div style='font-size:14px;'>" & _
"<table border='1' padding='2px 2px 2px 2px;'><tr><td>" & _
field.Name & "</td><td>" & field.value & "</td>" & _
"<td>" & FOV & "</td></tr></table></div>"
EmailBody = EmailBody & "<div style='font-size:14px;'>" & _
"<table border='1' padding='2px 2px 2px 2px;'><tr><td>" & _
field.Name & "</td><td>" & field.value & "</td></tr></table></div>"
'EmailBody = EmailBody & "</table></div>"
Next

'Next record
subRs.MoveNext
Loop

Call SendEmail(emailTo, emailCC, EmailSubject, Null, EmailBody)

 

[MajP]

If you were sending an email for a single record you could use the controls .oldvalue property. You could loop your controls and get the old an current value. But once the current event fires (move to next record) you would loose the oldvalue. So since you are looping all records and firing the code after editing multiple records this will not work. There is no oldvalue property at the recordset field level. The form has a cache between the control and underlying recordset so you can undo a change by hitting escape or using the undo method. You are likely going to have to code some logging to log changes. There are lots of examples out there. Here is a simple one.

http://www.techrepublic.com/article/a-simple-solution-for-tracking-changes-to-access-data/

so every time you change a record you would time stamp it, list the field name, and both the new and old values. Now you could just loop the change table filtered to the current date, to add to your email.