INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Accessing Original Value information in an Access sub-form.

Accessing Original Value information in an Access sub-form.

(OP)
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)

RE: Accessing Original Value information in an Access sub-form.

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close