Object Doesn't Support Property or Method Error

[andigirlsc]

I found some code online that creates an Audit Trail for my database. This code captures all changes (new records, edits and deletions) to my database and writes the changes (old and new values) to a table. It works, but it also gives an error message, “Object Doesn't Support Property or Method Error”.

I have seen other posts with this error, but not a solution specific to my needs. How do I get rid of this error? Thanks in advance!

Here is the actual code that I used in the form. This code was saved as a Module and is called in each form.

Audit Trail Code
http://www.fontstuff.com/access/acctut21.htm

 [COLOR=black][COLOR=navy]Sub[/COLOR] AuditChanges(IDField [COLOR=navy]As String[/COLOR], UserAction [COLOR=navy]As String[/COLOR])
[COLOR=navy]  On Error GoTo[/COLOR] AuditChanges_Err
[COLOR=navy]  Dim[/COLOR] cnn [COLOR=navy]As[/COLOR] ADODB.Connection
[COLOR=navy]  Dim[/COLOR] rst [COLOR=navy]As[/COLOR] ADODB.Recordset
[COLOR=navy]  Dim[/COLOR] ctl [COLOR=navy]As[/COLOR] Control
[COLOR=navy]  Dim[/COLOR] datTimeCheck [/COLOR][COLOR=black][COLOR=navy]As Date
    Dim [/COLOR]strUserID[COLOR=navy] As String[/COLOR]
[COLOR=navy]  Set[/COLOR] cnn = CurrentProject.Connection
[COLOR=navy]  Set[/COLOR] rst = [COLOR=navy]New[/COLOR] ADODB.Recordset
rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
    strUserID = Environ("USERNAME")
[COLOR=navy]  Select Case[/COLOR] UserAction
[COLOR=navy]      Case[/COLOR] "EDIT"
[COLOR=navy]          For[/COLOR] Each ctl [COLOR=navy]In[/COLOR] Screen.ActiveForm.Controls
[COLOR=navy]              If[/COLOR] ctl.Tag = "Audit" [COLOR=navy]Then[/COLOR]
[COLOR=navy]                  If[/COLOR] Nz(ctl.Value) <> Nz(ctl.OldValue) [COLOR=navy]Then[/COLOR]
[COLOR=navy]                      With[/COLOR] rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
[COLOR=navy]                      End With[/COLOR]
[COLOR=navy]                  End If[/COLOR]
[COLOR=navy]              End If[/COLOR]
[COLOR=navy]          Next[/COLOR] ctl
[COLOR=navy]      Case Else[/COLOR]
[COLOR=navy]          With[/COLOR] rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
.Update
[COLOR=navy]          End With[/COLOR]
[COLOR=navy]  End Select[/COLOR]
AuditChanges_Exit:
[COLOR=navy]  On Error Resume Next[/COLOR]
rst.Close
cnn.Close
[COLOR=navy]  Set[/COLOR] rst = [COLOR=navy]Nothing[/COLOR]
[COLOR=navy]  Set[/COLOR] cnn = [COLOR=navy]Nothing[/COLOR]
[COLOR=navy]  Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
[COLOR=navy]  Resume[/COLOR] AuditChanges_Exit
[COLOR=navy]End Sub[/COLOR][/COLOR]

Code to Call AuditChanges function for New or Edited Records

 [COLOR=black][COLOR=navy]Private Sub[/COLOR] Form_BeforeUpdate(Cancel [COLOR=navy]As Integer[/COLOR])
[COLOR=navy]  If[/COLOR] Me.NewRecord [COLOR=navy]Then[/COLOR]
[COLOR=navy]      Call[/COLOR] AuditChanges("EmployeeID", "NEW")
[COLOR=navy]  Else[/COLOR]
[COLOR=navy]      Call[/COLOR] AuditChanges("EmployeeID", "EDIT")
[COLOR=navy]  End If[/COLOR]
[COLOR=navy]End Sub[/COLOR][/COLOR]

Here is the code to trigger AuditChanges upon a deletion. This code doesn’t trigger the error message. I isolated it and it ran fine.

 Code to Call Audit Changes for Deletions
 [COLOR=navy]Private Sub[/COLOR] Form_AfterDelConfirm(Status [COLOR=navy]As Integer[/COLOR])
[COLOR=navy]  If[/COLOR] Status = acDeleteOK [COLOR=navy]Then Call[/COLOR] AuditChanges("EmployeeID", "DELETE")
[COLOR=navy]End Sub[/COLOR]

 

[vbaInet]

There's an Audit Trail db on this site which I believe most people have used with success.

but it also gives an error message, “Object Doesn't Support Property or Method Error”.

You've shown a page of code but not mentioned where it errors.

 

[andigirlsc]

There's an Audit Trail db on this site which I believe most people have used with success.

You've shown a page of code but not mentioned where it errors.

This is not a compile error, so I don't know which line of code is triggering it. It is an error message that pops up when I edit a record using the form that the code is running behind.

I will try the Audit Trail code you recommended and let you know what happens. Which Audit Trail code were you referring to?

 

[pbaldy]

Temporarily comment out this line and run the code, and you should be able to debug and find out what line is causing the error. You might note what control it's on too. One guess is that you have "Audit" in the tag property of an inappropriate control.

Oops forgot the line to comment out:

On Error GoTo AuditChanges_Err

 

[vbaInet]

No idea but I know that there's one on here because I've seen people ask about it. It should be within the "Microsoft Access Reference" unless pbaldy knows its exact whereabouts?

 

[pbaldy]

Don't look at me. I don't post recommendations to code without knowing where it is.

Perhaps you meant ghudson's though:

http://www.access-programmers.co.uk/forums/showthread.php?t=44231

 

[vbaInet]

That's the ticket! I knew I could rely on you

 

[andigirlsc]

Temporarily comment out this line and run the code, and you should be able to debug and find out what line is causing the error. You might note what control it's on too. One guess is that you have "Audit" in the tag property of an inappropriate control.

Oops forgot the line to comment out:

On Error GoTo AuditChanges_Err

I commented out the line:

On Error GoTo AuditChanges_Err

Then, I compiled the code. I did not get another error until I used the form live and tried to save the changes. Here is the line of code the debugger flagged:

If Nz(ctl.Value) <> Nz(ctl.OldValue) Then

And yes, I do have "Audit" in the Tag section of the properties menu for the fields that I want to capture changes for. The instructions I used from my link in Post #1 indicated that I should do this. All of your help is greatly appreciated!

 

[pbaldy]

What control was the loop on at the point it broke (what kind of control)?

 

[andigirlsc]

What control was the loop on at the point it broke (what kind of control)?

The function is written such that I can't tell which control is causing it to break. But, this error is triggered on all of the controls in the form. It doesn't matter which field I use to make the edit, the error gets triggered.

The controls on the form are are as follows:
Academy ID
Last Name
First Name
MI
Suffix
Instructor
SCEIS Username
Work Location
Job Status
Job Status Start Date
Projected Return Date
Notes

 

[pbaldy]

The error would be triggered because it loops all controls. While it's in debug mode you can type:

?ctl.Name

into the Immediate window and that should tell you the name of the control it's stuck on. I'm still guessing it's a button or label or something without a Value/OldValue property.

 

[andigirlsc]

The error would be triggered because it loops all controls. While it's in debug mode you can type:

?ctl.Name

into the Immediate window and that should tell you the name of the control it's stuck on. I'm still guessing it's a button or label or something without a Value/OldValue property.

I followed your instructions and the Immediate Window says that one of my tab controls (which I didn't list earlier because there are 26 fields in the control) is triggering the error. So, I removed the "Audit" tag from all of the fields in the tab control, but I am still getting the error. I also tried making the tab control invisible and I still get the error.

 

[pbaldy]

Does the tag property of the tab control have Audit in it? It shouldn't, just the actual text/combo boxes.

 

[vbaInet]

...like Paul said only put Audit in the controls that are bound to the fields you want to audit. A tab control isn't bound to a field.

 

[andigirlsc]

Does the tag property of the tab control have Audit in it? It shouldn't, just the actual text/combo boxes.

No, the tab control does not have the Audit tag in it. In fact, I removed the Audit tag from the individual text boxes within the tab control and I am still receiving the error.

 

[vbaInet]

On the same code line? You probably have Audit in the tag property of a label.

 

[pbaldy]

Can you post the db here?

 

[andigirlsc]

Ok. I removed all sensitive data and replaced it with sample data. Unfortunately, I can't save the DB in an earlier Access format (2003-2007) because it contains elements that require the 2010 version. I'm not sure which elements to remove to save it as an earlier version of Access. Thank you for your help!

 

[pbaldy]

I'll fire up a VM with 2010 on it after lunch.

 

[andigirlsc]

@pbaldy,

Please check your PM box for instructions. Thanks!