Audit trail question

Helllo--I have implemented the audit trail from the Microsoft KB
article that records changes on a data entry form to a memo filed in
the fieeld's table record. What I would like to do is pull certain
info from that module and put it in a separate table--is this
possible?
tHANKS,
bOB


Explanations in caps in code

Function AuditTrail()
On Error GoTo Err_Handler
Dim MyForm As Form, C As control, xName As String

THESE ARE THE VARIABLES i WOULD LIKE TO TRANSFER
Dim pid As String
Dim fieldname As String
Dim oldval As String
Dim newval As String
Dim formname As String

Set MyForm = Screen.ActiveForm
'Set date and current user if form has been updated.
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"Changes made on " & Date & " by " & CurrentUser() & ";"

'If new record, record it in audit trail and exit.
If MyForm.NewRecord = True Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"New Record """
Exit Function
End If
'Check each data entry control for change and record old value of
Control.

For Each C In MyForm.Controls
'Only check data entry type controls.
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
' Skip Updates field.
If C.name <> "Updates" Then
' If control was previously Null, record "previous
' value was blank." NOT RECORDING THIS BUT LEFT CODE IN IN
CASE I CHANGE MIND
If IsNull(C.OldValue) Or C.OldValue = "" Then
End If
' MyForm!Updates = MyForm!Updates & Chr(13) & _
' Chr(10) & C.name & "--previous value was blank"

' If control had previous value, record previous value.
' Else
If C.Value <> C.OldValue Then
MyForm!Updates = MyForm!Updates & Chr(13) &
Chr(10) & _
C.name & "==previous value was " & C.OldValue

i WANT TO CAPTURE THESE VALUES BEFORE GOING TO THE NEXT CONTROL AND
TRANSFER THEM TO A SEPARATE TABLE HERE--CAN i DO THIS???
pid = MyForm!PatId
formname = MyForm.name
fieldname = C.name
oldval = C.OldValue
newval = C.Value

End If
End If
End Select
Next C

TryNextC:
Exit Function

ERROR HANDLING OMITTED FOR THIS MESSAGE

sure. pass all the data you want to another function that writes it to
another table by opening a recordset and appending the data...

public function LogOldAndNewValues( lngPID As Long, strForm as string,
strField As String, varOldValue as Variant, varNewValue as Variant)
dim rs as dao.recordset
set rs=dbengine(0)(0).openrecordset("LogTable",dbopent able)
rs.addnew
rs.fields("PID")=lngPID
rs.fields("FormName")=strForm
rs.fields("OldValue")=varOldValue
rs.Fields("NewValue")=varNewValue
rs.update
rs.close
set rs=nothing

end function

Thanks--will try it at work Monday

bob
On 15 Apr 2005 20:37:55 -0700, pietlinden@hotmail.com wrote:
[color=blue]
>sure. pass all the data you want to another function that writes it to
>another table by opening a recordset and appending the data...
>
>public function LogOldAndNewValues( lngPID As Long, strForm as string,
>strField As String, varOldValue as Variant, varNewValue as Variant)
>dim rs as dao.recordset
>set rs=dbengine(0)(0).openrecordset("LogTable",dbopent able)
>rs.addnew
>rs.fields("PID")=lngPID
>rs.fields("FormName")=strForm
>rs.fields("OldValue")=varOldValue
>rs.Fields("NewValue")=varNewValue
>rs.update
>rs.close
>set rs=nothing
>
>end function[/color]

HI--and thanks for the code--I got it to work with a few changes---I
included your code within the audit trail modul--when I tried to call
it as a separate function it was not passing the values to the audit
log table so I declared the variables in the original module and then
assigned them the desired value(strformname = myform.name for
example)--thanks again for your help

On 15 Apr 2005 20:37:55 -0700, pietlinden@hotmail.com wrote:
[color=blue]
>sure. pass all the data you want to another function that writes it to
>another table by opening a recordset and appending the data...
>[/color]

Function AuditTrail()
On Error GoTo Err_Handler
Dim MyForm As Form, C As control, xName As String
[color=blue]
>lngPID As Long
>strForm as string
>strField As String
>varOldValue as Variant
>varNewValue as Variant[/color]

Set MyForm = Screen.ActiveForm

MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"Changes made on " & Date & " by " & CurrentUser() & ";"

If MyForm.NewRecord = True Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"New Record """
Exit Function
End If

For Each C In MyForm.Controls
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup

If C.name <> "Updates" Then
If IsNull(C.OldValue) Or C.OldValue = "" Then
End If
' MyForm!Updates = MyForm!Updates & Chr(13) & _
If C.Value <> C.OldValue Then
MyForm!Updates = MyForm!Updates & Chr(13) &
Chr(10) & _ C.name & "==previous value was " & C.OldValue
[color=blue]
>lngpid = MyForm!PatId
>strformname = MyForm.name
>strfieldname = C.name
>varoldval = C.OldValue
>varnewval = C.Value[/color]
[color=blue]
>dim rs as dao.recordset
>set rs=dbengine(0)(0).openrecordset("LogTable",dbopent able)
>rs.addnew
>rs.fields("PID")=lngPID
>rs.fields("FormName")=strForm
>rs.fields("OldValue")=varOldValue
>rs.Fields("NewValue")=varNewValue
>rs.update
>rs.close
>set rs=nothing
>[/color]


End If
End If
End Select
Next C

TryNextC:
Exit Function

On 15 Apr 2005 20:37:55 -0700, pietlinden@hotmail.com wrote:
[color=blue]
>sure. pass all the data you want to another function that writes it to
>another table by opening a recordset and appending the data...
>
>public function LogOldAndNewValues( lngPID As Long, strForm as string,
>strField As String, varOldValue as Variant, varNewValue as Variant)
>dim rs as dao.recordset
>set rs=dbengine(0)(0).openrecordset("LogTable",dbopent able)
>rs.addnew
>rs.fields("PID")=lngPID
>rs.fields("FormName")=strForm
>rs.fields("OldValue")=varOldValue
>rs.Fields("NewValue")=varNewValue
>rs.update
>rs.close
>set rs=nothing
>
>end function[/color]

Hi--i took your function above and included it in the first function.
It works quite well but am haviung one problem. If the old value or
new value is null then a record is not being created. With the code
below it is creating an aurdit record for each control on my form--If
the oldvalue and new value are both null then I want to move to the
next control without creating an audit record for that control I can't
figure out where to put apropiate code--see below:


Dim MyForm As Form, C As control, xName As String
Dim strPatID As String
Dim strForm As String
Dim strField As String
Dim varOldValue As Variant
Dim varNewValue As Variant
Set MyForm = Screen.ActiveForm

SNIPPED OTHER STUFF
For Each C In MyForm.Controls
'Only check data entry type controls.
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup,
acCheckBox

' If control was previously Null, do something
HERE IS WHERE I WANT TO CHECK FOR NULLS AND AND STILL CREATE RECORD
FOR CHANGES
If (IsNull(C.OldValue) Or C.OldValue = "") And (C.Value = ""
Or IsNull(C.Value)) Then

WANT TO MOVE TO NEXT CONTROL WITHOUT CREATING AUDIT RECORD

Else
If (IsNull(C.OldValue) Or C.OldValue = "") And (C.Value <> ""
Or Not IsNull(C.Value)) Then
strForm = MyForm.name
strField = C.name
strPatID = MyForm!patid
varOldValue = 888
varNewValue = C.Value

ElseIf C.Value <> C.OldValue And (C.OldValue <> "" Or
Not IsNull(C.OldValue)) Then
strForm = MyForm.name
strField = C.name
strPatID = MyForm!patid
varOldValue = C.OldValue
varNewValue = C.Value
End If



Dim rs As DAO.Recordset
Set dbs = CurrentDb
Set rs = DBEngine(0)(0).OpenRecordset("test", dbOpenTable)


rs.AddNew
rs.Fields("PatId") = strPatID
SNIPPED OTHER FIELDS ADDED
rs.Update
rs.Close
Set rs = Nothing

End If
End Select

Next C

End Function


THANKS
bob

I am trying to pass a value (888( to my audit trail table when either
the old value or new value is null. Below is what I came up with but
it does not create a record in the audit table for the nulls--only for
the non-null recs. Can anyone help me out with this issue? What am I
doing Wrong?

thanks
bob


If C.Value <> C.OldValue Then
strForm = MyForm.name
strField = C.name
strPatID = MyForm!patid
THESE TRANSFER INTO TABLE FINE

THESE DO NOT
If IsNull(C.OldValue) Or C.OldValue = "" Then varOldValue = 888 Else
varOldValue = C.OldValue
If IsNull(C.Value) Or C.Value = "" Then varNewValue = 888 Else
varNewValue = C.Value

Dim rs As DAO.Recordset
Set dbs = CurrentDb
Set rs = DBEngine(0)(0).OpenRecordset("test", dbOpenTable)

rs.AddNew
rs.Fields("PatId") = strPatID
rs.Fields("staff") = CurrentUser
rs.Fields("formname") = strForm
rs.Fields("fieldname") = strField
rs.Fields("OldValue") = varOldValue
rs.Fields("newvalue") = varNewValue
rs.Update
rs.Close
Set rs = Nothing
End If

What is the data type: text or numeric?

If text, try:

If Len(Trim$(C.OldValue & "")) = 0 Then
varOldValue = "888"
Else
varOldValue = C.OldValue
End If

If numeric, try:

varOldValue = Nz(C.OldValue, 888)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



<allyn44@cox.net> wrote in message
news:tbla81lehq42pk5hm7lk4jdeba617ghucg@4ax.com...[color=blue]
>
> I am trying to pass a value (888( to my audit trail table when either
> the old value or new value is null. Below is what I came up with but
> it does not create a record in the audit table for the nulls--only for
> the non-null recs. Can anyone help me out with this issue? What am I
> doing Wrong?
>
> thanks
> bob
>
>
> If C.Value <> C.OldValue Then
> strForm = MyForm.name
> strField = C.name
> strPatID = MyForm!patid
> THESE TRANSFER INTO TABLE FINE
>
> THESE DO NOT
> If IsNull(C.OldValue) Or C.OldValue = "" Then varOldValue = 888 Else
> varOldValue = C.OldValue
> If IsNull(C.Value) Or C.Value = "" Then varNewValue = 888 Else
> varNewValue = C.Value
>
> Dim rs As DAO.Recordset
> Set dbs = CurrentDb
> Set rs = DBEngine(0)(0).OpenRecordset("test", dbOpenTable)
>
> rs.AddNew
> rs.Fields("PatId") = strPatID
> rs.Fields("staff") = CurrentUser
> rs.Fields("formname") = strForm
> rs.Fields("fieldname") = strField
> rs.Fields("OldValue") = varOldValue
> rs.Fields("newvalue") = varNewValue
> rs.Update
> rs.Close
> Set rs = Nothing
> End If
>
>[/color]

Thanks--the one problem though will be that the data type can
change--I am looping through each control on a form. I have the code
in an if-then staement but it is not transferring to the new variables
:(

bob

On Sat, 14 May 2005 11:33:49 -0400, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
[color=blue]
>What is the data type: text or numeric?
>
>If text, try:
>
>If Len(Trim$(C.OldValue & "")) = 0 Then
> varOldValue = "888"
>Else
> varOldValue = C.OldValue
>End If
>
>If numeric, try:
>
>varOldValue = Nz(C.OldValue, 888)[/color]

I found a way to asign the null regardless of type--created temp
variables to hold the old and new value, tested them for nulls and
thenchecked teh temp variables for inequality, then performed needed
function-- and now it creates a rec in my audit table inclduing for
nulls (nulls are coded as 888)- I needed to be able to record if data
was replaced with a null or empty string--below is the
code--suggestions or comments appreciated.


Option Compare Database
Function AuditTrail()
Dim MyForm As Form, C As control, xName As String
Dim strPatID As String
Dim strForm As String
Dim strField As String
Dim varOldValue As Variant
Dim varNewValue As Variant
Dim tempold As Variant
Dim tempnew As Variant

Set MyForm = Screen.ActiveForm

For Each C In MyForm.Controls

If IsNull(C.OldValue) Or C.OldValue = "" Then tempold = 888 Else
tempold = C.OldValue

If IsNull(C.Value) Or C.Value = "" Then tempnew = 888 Else tempnew =
C.Value

If tempnew <> tempold Then
strForm = MyForm.name
strField = C.name
strPatID = MyForm!patid
varOldValue = tempold
varNewValue = tempnew

Dim rs As DAO.Recordset
Set dbs = CurrentDb
Set rs = DBEngine(0)(0).OpenRecordset("test", dbOpenTable)

rs.AddNew
rs.Fields("PatId") = strPatID
ETC, ETC
rs.Update
rs.Close
Set rs = Nothing
End If

End Select
Next C

TryNextC:
Exit Function
End Function








On Sat, 14 May 2005 11:33:49 -0400, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
[color=blue]
>What is the data type: text or numeric?
>
>If text, try:
>
>If Len(Trim$(C.OldValue & "")) = 0 Then
> varOldValue = "888"
>Else
> varOldValue = C.OldValue
>End If
>
>If numeric, try:
>
>varOldValue = Nz(C.OldValue, 888)[/color]