HELP NEEDED WITH AUDIT TRAIL

[Guest]

i have a main form and 6 subforms. i have an audit trail code which works
fine with all six subforms and a main form except when i perform Del
operation on subform.let me give you a situation how it works.
On main Form i Have field Atype & Rtype., based on which one subform opens
in new window.
let say Atype = B & Rtype= S
On Rtype OnChange event Subform B_S opens
then on B-S i perform Del & on DelConfirm it closes automatically and n base
on what Rtype was changed to corresponding subform opens up but when i enter
value on the subform n move my focus back to main form its gives me MSG
"INVALID OBJECT OR OBJECT NO LONGER SET" but if i Close everthing and open
again it works fine but i Dont want that msg at all...because it will confuse
users.
i know its a long question but i shall appreciate any help.
Thank You.
Here is CODE which i am using


Public Function AuditTrail1(PsubformName, PLevel, Optional Psub1)
On Error GoTo err_AuditTrail1
Dim MyForm As Form, C As Control
Dim rs As Recordset
Dim db As Database
Dim bOK As Boolean

Set db = CurrentDb
Set rs = db.OpenRecordset("tblAudit")
bOK = False
DoCmd.SetWarnings False

Select Case PLevel
Case 1: Set MyForm = Screen.ActiveForm
Case 2: Set MyForm = Screen.ActiveForm(PsubformName).Form
Case 3: Set MyForm = Screen.ActiveForm(Psub1).Form(PsubformName).Form
End Select

For Each C In MyForm.Controls
If TypeOf C Is TextBox Or TypeOf C Is ComboBox Then
If C.Value <> C.OldValue Or IsNull(C.OldValue) Then
If Not IsNull(C.Value) Then
rs.AddNew
rs!Comments = "CHECKING"
rs!TableChanged = MyForm.RecordSource
rs!FieldChanged = C.Name
rs!FieldChangedFrom = C.OldValue
rs!FieldChangedTo = C.Value
rs!User = VBA.Interaction.Environ("USERNAME")
rs!DateofHit = Now
rs.Update
rs.Close
db.Close
End If
End If
End If
Next C
AuditTrail1 = bOK

exit_AuditTrail1:
DoCmd.SetWarnings True
Exit Function

err_AuditTrail1:
MsgBox Err.Description
Resume exit_AuditTrail1


End Function

 

[Allen Browne]

Instead of passing in arguments for the form name and PLevel, could you
simply pass a reference to the form like this:
Public Function AuditTrail1(MyForm As Form)
You would then call it from each form like this:
Call AuditTrail1(Me)

On a related note, the line:
If C.Value <> C.OldValue Or IsNull(C.OldValue) Then
copes with a change in value, or a new value, but deleting a value (i.e.
where the Value is now Null).

In a continuous form or datasheet, a user could select multiple records and
delete them. Is your code handling that case too?

More suggestions and example code in:
Audit Trail - Log changes at the record level
at:
http://www.allenbrowne.com/AppAudit.html

 

[Guest]

First of all thanks for the response.
my only convern is one of my table dont have Autonumber primary key because
in that table i have to have the primary key as one of the fields in that
table.
can i still use your code even if one of my tables to be audited soesnt
satisfy your condition of autonumber primary key.

 

[Guest]

and i did change my funtion to
Public Function AuditTrail1(MyForm As Form)
and called like this
Call AuditTrail1(Me)
but now i get the error msg "Object variable or with block variable not set"
same code...ahhhh confusing....

 

[Allen Browne]

What line generates the error?

Has the form been closed at this time?

 

[Guest]

it does not specify any line, i understand usually if there is an error it
will ask to debug but this error msg is just like a Msg box and yes the
subform where i perform del operation is closed at this time and i have other
subform opened with main form. at this point if i enter anything in subform
and move back to main form thats when it give me this error.
i changed the code but error changed back to the same old thing "INVALID
OBJECT OR NO LONGER SET".

 

[Allen Browne]

So if you are referring to a subform that is closed, the error makes sense?

 

[Guest]

i changed the code and now form doesnt close anymore but still same
situation.if i make audit trail code disable then the same procedure works
fi9 i can open subform perform deletion n open new subform add data and move
back and forth between main and subform.but all this starts as soon as i call
audit trail function.
since the msg box appear once and once you enter data in any field and
igonore error then it works fi9.do you think is that because of some
connection problem between main and subform or problem with using recordset.
i guess it does not recognize the object subform or vice versa..but after
one time of data entry it establish connection some how may be.....
sorry to bother with all this but i m stuck with this project at work n gota
do this...

 

[Guest]

just a quick question if it helps can i send u a part of my application
because of which this error keeps coming..
if yes i can send u my main form and related table n vba funtion as a zip
file.
i really appreciate all ur help.
Thanks.

 

[Allen Browne]

Thanks for asking, but please don't send the file. If we did that for
everyone, we would never get any paid work done.

There are a couple of other causes of this error. If you have 2 subforms in
a form, and you use the subforms' Open or Load or Currrent event to refer to
the other one, you will get this error. Access has to open one of the
subforms first, so it will not find the other one when its Load/Open event
runs. This does have the symptom of only occurring once and then working
fine (once both are open.)

The other case involves code that closes something it did not open, such as
the default workspace. This usually happens where people have been using
transactions.

If it's not those, I'm not sure what else to suggest. Perhaps someone else
can offer a suggestion.

 

[Guest]

hi there...yes you are right and i understand.
i will try to check both the cases which you mentioned and hope one of them
will help me find my problem...
but i really do appreciate your suggestions.
Thank you.