Audit trail question

[allyn44]

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.ActiveFo rm
'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.NewRecor d = 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.OldVal ue) 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

[pietlinden]

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 LogOldAndNewVal ues( 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).openrecordse t("LogTable",db opentable)
rs.addnew
rs.fields("PID" )=lngPID
rs.fields("Form Name")=strForm
rs.fields("OldV alue")=varOldVa lue
rs.Fields("NewV alue")=varNewVa lue
rs.update
rs.close
set rs=nothing

end function

[allyn44]

Thanks--will try it at work Monday

bob
On 15 Apr 2005 20:37:55 -0700, pi********@hotm ail.com wrote:

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 LogOldAndNewVal ues( 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).openrecordse t("LogTable",db opentable)
rs.addnew
rs.fields("PID ")=lngPID
rs.fields("For mName")=strForm
rs.fields("Old Value")=varOldV alue
rs.Fields("New Value")=varNewV alue
rs.update
rs.close
set rs=nothing

end function

[allyn44]

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(strformna me = myform.name for
example)--thanks again for your help

On 15 Apr 2005 20:37:55 -0700, pi********@hotm ail.com wrote:

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

Function AuditTrail()
On Error GoTo Err_Handler
Dim MyForm As Form, C As control, xName As String
lngPID As Long
strForm as string
strField As String
varOldValue as Variant
varNewValue as Variant
Set MyForm = Screen.ActiveFo rm

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

If MyForm.NewRecor d = 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.OldVal ue) 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
lngpid = MyForm!PatId
strformname = MyForm.name
strfieldname = C.name
varoldval = C.OldValue
varnewval = C.Value dim rs as dao.recordset
set rs=dbengine(0)( 0).openrecordse t("LogTable",db opentable)
rs.addnew
rs.fields("PID ")=lngPID
rs.fields("For mName")=strForm
rs.fields("Old Value")=varOldV alue
rs.Fields("New Value")=varNewV alue
rs.update
rs.close
set rs=nothing

End If
End If
End Select
Next C

TryNextC:
Exit Function

[allyn44]

On 15 Apr 2005 20:37:55 -0700, pi********@hotm ail.com wrote:

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 LogOldAndNewVal ues( 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).openrecordse t("LogTable",db opentable)
rs.addnew
rs.fields("PID ")=lngPID
rs.fields("For mName")=strForm
rs.fields("Old Value")=varOldV alue
rs.Fields("New Value")=varNewV alue
rs.update
rs.close
set rs=nothing

end function

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.ActiveFo rm

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.OldVa lue) 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.OldVa lue) 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.OldVal ue)) 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("PatI d") = strPatID
SNIPPED OTHER FIELDS ADDED
rs.Update
rs.Close
Set rs = Nothing

End If
End Select

Next C

End Function
THANKS
bob

[allyn44]

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.OldVal ue) 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("PatI d") = strPatID
rs.Fields("staf f") = CurrentUser
rs.Fields("form name") = strForm
rs.Fields("fiel dname") = strField
rs.Fields("OldV alue") = varOldValue
rs.Fields("newv alue") = varNewValue
rs.Update
rs.Close
Set rs = Nothing
End If

[Douglas J. Steele]

What is the data type: text or numeric?

If text, try:

If Len(Trim$(C.Old Value & "")) = 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!)

<al*****@cox.ne t> wrote in message
news:tb******** *************** *********@4ax.c om...

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.OldVal ue) 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("PatI d") = strPatID
rs.Fields("staf f") = CurrentUser
rs.Fields("form name") = strForm
rs.Fields("fiel dname") = strField
rs.Fields("OldV alue") = varOldValue
rs.Fields("newv alue") = varNewValue
rs.Update
rs.Close
Set rs = Nothing
End If

[allyn44]

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_djsteel e@NOSPAM_canada .com> wrote:

What is the data type: text or numeric?

If text, try:

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

If numeric, try:

varOldValue = Nz(C.OldValue, 888)

[allyn44]

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.ActiveFo rm

For Each C In MyForm.Controls

If IsNull(C.OldVal ue) 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("PatI d") = 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_djsteel e@NOSPAM_canada .com> wrote:

What is the data type: text or numeric?

If text, try:

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

If numeric, try:

varOldValue = Nz(C.OldValue, 888)