Solved Module collecting audit data

[lacampeona]

Hello
i have a form and some memo field called Updates.
When i make changes on my form (called frmA ) the module audit data write all the audit data to the memo field called Updates. this is working perfectly.

now i want to make also audit data on my subform.
i try the same module but i notice that when the form is working as single form audit data is working...but when i put the form as subform then the module and audit data is not working.

i am sure that has something to do with the the code writen in the module.
the screen.active form. and the field uptades..maybe i have to put uptades on the main form...and then uptades2 on the subform?

can somebody help me
how i can use my module that will show me audit data in the field uptades2? also on my subform?

Option Compare Database

Public Function AuditData()
' Comments : This function is used to provide an audit trail of all changes to a record
' : entered on a secured form. After adding a locked memo field called 'Updates'
' : on the source form, add a reference to the Before Update event to this function.
' Requirement: A bound memo field named 'Updates' must exist on the active form
' Parameters : None
' Returns : Appends Audit Trail data as a string to the memo field on the active form
' Created By : Ofni Systems Inc.
' Modified : 04/07/01
' --------------------------------------------------------
Dim frmActive As Form
Dim ctlData As Control
Dim strEntry As String
Dim User As String

Dim Who As String



Who = TempVars!UserName

User = CreateObject("wscript.network").ComputerName

Set frmActive = Screen.ActiveForm

'Set date and current user if form has been updated.
frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & _
"----------------------------------------------------------------------------------------------" & Chr(13) & Chr(10) & _
"Changes made on " & Date & " " & Time & " by " & Who & ";"


'Determine who, when and where
strEntry = " by " & Who & " on " & Now


' If new record, record it in audit trail and exit sub
If frmActive.NewRecord = True Then
frmActive!Updates = frmActive!Updates & "New Record Added" & strEntry
'Uncomment the next line if you do not want to record the details of the initial entry
'Exit Function
End If
'Check each data entry control for change and record
For Each ctlData In frmActive.Controls
' Only check data entry type controls
Select Case ctlData.ControlType
Case acTextBox, acComboBox, acCheckBox, acOptionButton
' Skip Updates field
If ctlData.Name = "Updates" Then GoTo NextCtl
'Skip unbound controls (3 is ControlSource)

Select Case IsNull(ctlData.Value)
'Check for deleted data
Case True
If Not IsNull(ctlData.OldValue) Then
frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & " " & ctlData.Name & " Data Deleted: Old value was '" & ctlData.OldValue & "'" & strEntry
End If
'Check for new or changed data
Case False
If IsNull(ctlData.OldValue) And Not IsNull(ctlData.Value) Then
frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & " " & ctlData.Name & " Data Added: " & ctlData.Value & strEntry
'If control had previous value, record previous value
ElseIf ctlData.Value <> ctlData.OldValue Then
frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & " " & ctlData.Name & " changed from '" & ctlData.OldValue & "' to '" & ctlData.Value & "'" & strEntry
End If
End Select
End Select
NextCtl:
Next ctlData
End Function

 

[lacampeona]

i am sorry for the code..i think i didnt paste it good like to be in the toggle BB code... so is easier to read

 

[theDBguy]

Hi. I think that's because subforms require a different syntax than main forms. You'll have to modify your code to accommodate both forms.

 

[lacampeona]

Yes i know that
but i dont know how to modify
i was searching on the net to find how to say to the access that i have subform
i start with Dim...and then i was lost in strings..
i dont know what preffix i have to use to say that
hmmmm

do you have any suggestion?
thank you

 

[theDBguy]

It might be easier to show you. Can you post a sample db with test data?

 

[lacampeona]

Yes
my form that i want is frmAtest ( here i have two pages...page1 and page2..on the page2 - inside is the subform frmA which is making me problems

 

[theDBguy]

Muy bien. I'm not in front of a computer now, but I will take a look when I get home tonight.

 

[lacampeona]

ok thank you in advance

gracias senor theDBguy

 

[Pat Hartman]

The audit code for the main form has to go in the mainform's BeforeUpdate or AfterUpdate event depending on how you are doing the logging. I prefer the AfterUpdate but that is neither here nor there.

The audit code for the subform has to go in the subform's BeforeUpdate or AfterUpdate event. They cannot be done in the same event because the records are not saved at the same time.

The main form record is ALWAYS saved before focus moves to the subform. Focus stays in the subform ans as you move from record to record, each modified record is changed and so multiple audit records might need to be created. Then when the user moves focus back to the main form, the subform is saved if it is dirt. That means that the mainform and a subform can NEVER< EVER be both dirty at the same time.

 

[lacampeona]

hello
so how i have to make?
i have fields that must be entered on the main form and the subform will contains some link documents....do you mean that is better that i make a button that will open/show my subform?
so the access will first save the data to main form then user will click the button ( ( to add documents) that mean that my subform will open and then the audit data will work perfectly?

 

[Pat Hartman]

The audit data should be completely separate for each table/form. That was what I was trying to explain. Just because a form has a subform doesn't mean that the audit log contains data from both tables. You can't control when subform records are logged from the mainform because the mainform does not know when a subform record has been modified. ONLY the subform itself knows that it has a dirty record that needs to be saved and logged.

I did not suggest adding buttons to open the subform. I simply explained how/when Access saves records. The whole point of using bound forms which are the heart of any Access application is that Access does a lot of stuff for you. You can pirouette around the Access internals and add your own functionality to various form events such as the audit log you are creating. Audit logs are not built into Access so if you want one, you need to code it yourself and add that code to the correct form level events And, if your form has subforms, and you want to log changes to those also, you need to add code to the correct subflor form level events..

Audit logs are NEVE, EVER controlled by the user. so they are never controled via a button press They are always controlled by your code. Your code always makes an audit log entry when something changes in a table and that code runs in the form that the table is bound to.

 

[lacampeona]

ok thank you for your explanation
i will use your advice
thanks

 

[The_Doc_Man]

As a more practical suggestion...

1. Where you would call the audit routine, do this:

Change this: Public Function AuditData()

to this: Public Function AuditData( ByRef F as Access.Form )

NOTE: I'm a belt-and-suspenders man, so the ByRef is actually optional since it is the default for passing objects. But hey, better safe than sorry.

2. Inside the audit routine, do this:

Change this: Set frmActive = Screen.ActiveForm

to this: Set frmActive = F

3. When you are prepared to actually run your audit, do this:

Change this: AuditData

to this: AuditData( Me )

4. You have a simpler option. Inside that routine, do this:

Change this: For Each ctlData In frmActive.Controls

to this: For Each ctlData In F.Controls

and then don't bother with the Set frmActive ... that is item 2 above. Just use F as the form in question. This is an OPTION as an Either/Or with #2, not an And/Or.

5. Call this once per record save per form. If you have main and sub forms, call once from each. DO NOT attempt to touch any other form's controls when logging like this. Each form is responsible for its own logging trigger.

Per Pat Hartman's suggestion, call it either from the BeforeUpdate or AfterUpdate event. Like her, I prefer Form_AfterUpdate to do this kind of logging.

 

[lacampeona]

hello ok
thanks i will try it today

 

[lacampeona]

yeeeeeessss is workinggggg
thank you very much

 

[theDBguy]

yeeeeeessss is workinggggg
thank you very much

Hi. Glad to hear you got it sorted out. Good luck with your project.