Error 3265(item not found in collection) in Audit Trail code

[zandiT]

hello
i'm using the microsoft audit trail example ACC2000: How to Create an Audit Trail of Record Changes in a Form and im having a problem with my recordset.

in the example they are using a bound form so the text box called tbAuditTrail has a control source of the AuditTrail field in the table. My form is unbound. i tried to conncet my tbAuditTrail text box to the AuditTrail field in the table, but when i run the form i get the item not found in collection error. and it highlights the following line of code:Me.tbAuditTrail = rstMain("AuditTrail")

Dim dbs as DAO database
Dim rstMain as Recordset
 
sub FillFields
 
Me.tbAuditTrail = rstMain("AuditTrail")
End Sub
 

i know the problem is with the way im trying to conncet to the table and i can't use a bound form like the example. all my other controls in the form work properly, but i don't know why this text box is not connecting. is there a way i can rephrase my code to make this work?
thankyou all

[Stewart Ross]

Hi. The error message is saying that there is no field called "AuditTrail" in the recordset specified. 'Item not found in this collection' means the field name as given is not contained in the fields collection of that recordset.

You should check the exact spelling of the field concerned - if it has a space between the word Audit and the word Trail you would need to refer to it slightly differently, as

rstmain("[Audit Trail]")
or
rstmain![Audit Trail]

for example.

You are giving yourself a lot of work by using an unbound form, however...

-Stewart

[Stewart Ross]

I also skimmed the MS Knowledge base article you mentioned. It does not use any fields called 'audit trail' at all - the article relates to a custom function which sets up various other field values. I wonder if you are simply mistaken in how you are approaching implementation of the MS function?

In any event, I can't second guess your table structures or how you choose to implement a third-party function, but I have the feeling that misunderstanding of the MS solution is the root cause of your lack of success with implementation.

-Stewart

[zandiT]

Hello stewart
thanks so much for the quick reply. you were totally right, i had mispelled the table name and im no longer getting the error but i do still have a problem. there is no audit information being written in the text box tbAuditTrail and the table.

the reason im using an unbound form is because im going to have to split the database and place it on the server. i had finished (after a long struggle) and the audit trail requirement came up last minute. this is my first time using access and i researched alot on the audit trail issue and found numerous examples that didn't work this Microsoft example seems to be the last option i have. here is a sample of my code.

Public Function Audit_Trail() On Error GoTo Err_Audit_Trail 
Dim MyForm As Form
 Dim ctl As Control
 Dim sUser As String
 Set MyForm = Screen.ActiveForm 
sUser = "SELECT tblMain.Username FROM tblMain"
 sUser = CurrentUser
  If MyForm.NewRecord = True Then 
MyForm!audit = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";" 
Exit Function
 End If 
 
MyForm!audit = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"
 For Each ctl In MyForm.Controls
 
Select Case ctl.ControlType Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
 If ctl.Name = "tbAuditTrail" Then
 GoTo TryNextControl 'Skip AuditTrail field
 
If ctl.Value <> ctl.OldValue Then
 MyForm!audit = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value 
 ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
 MyForm!audit = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value 'If new value is Null and old value is not Null ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then MyForm!audit = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null" 
End If 
End Select 
TryNextControl: Next ctl Exit_Audit_Trail: 
Exit Function Err_Audit_Trail:
 If Err.Number = 64535 Then
 'Operation is not supported for this type of object.
 Exit Function
 ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window Beep MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
 Else Beep MsgBox Err.Number & " - " & Err.Description
 End If
 Resume 
Exit_Audit_Trail
 End Function

Private Sub Form_BeforeUpdate(Cancel As Integer)
 On Error GoTo Form_BeforeUpdate_Err 
Call Audit_Trail 
Form_BeforeUpdate_Exit:
 Exit Sub 
Form_BeforeUpdate_Err: MsgBox Err.Number & " - " & Err.Description Resume Form_BeforeUpdate_Exit
 End Sub

in my table tblMain, i have a field called Audit which is the recordset for the textbox tbAuditTrail. for some reason in the Function Audit_Trail, when i type MyForm!Audit, it automaticaly changes it to lower case MyForm!audit. could this be part of the problem?
thankyou for the help

[zandiT]

I also skimmed the MS Knowledge base article you mentioned. It does not use any fields called 'audit trail' at all - the article relates to a custom function which sets up various other field values. I wonder if you are simply mistaken in how you are approaching implementation of the MS function?

In any event, I can't second guess your table structures or how you choose to implement a third-party function, but I have the feeling that misunderstanding of the MS solution is the root cause of your lack of success with implementation.

-Stewart

hi sorry about that. im using an example that is based on the ms knowledge base. is it okay if i send you the zip file of the database example im using?

[NeoPa]

Although it is always up to the individual expert whether or not they want to deal with a member directly via email, or even by attaching your project to a thread, that should always be left up to the expert to request from you, and not the other way around.

As a general rule it is discouraged, as it simply makes the member lazy about expressing their problem clearly, and puts extra work onto the experts.

[zandiT]

Although it is always up to the individual expert whether or not they want to deal with a member directly via email, or even by attaching your project to a thread, that should always be left up to the expert to request from you, and not the other way around.

As a general rule it is discouraged, as it simply makes the member lazy about expressing their problem clearly, and puts extra work onto the experts.

I had posted my code and explained the problem in post #4 as clearly as possible. but in the middle of posting #4 he replied with post #3 and i explained , in post #5 that it was an example i was using, a sample database, not my project. i just thought he might want to look at the example cause its pretty good. is there a problem with the post i did in #4 or is that allowed? if its a problem, i'm sorry, it won't happen again. And if its not a problem, please may you help me out with my question. Sorry again

[Stewart Ross]

Hi ZandiT. There is no problem with assisting you with the code as far as is possible; NeoPa is right that where it is right and appropriate we will suggest you attach a zip copy of the DB for us to check. I am not in a position at the moment where I could look at an attachment quickly, so NeoPa's caution on this is fully justified! Like many contributors to this site, I would have to look at the attachment at home - and I have other pressing commitments that mean this would not be a priority for me at present.

Clearly, we do not know your DB the way you do. Could you advise what checks you have done to narrow down the audit trail function problems that you are experiencing? Have you set breakpoints and stepped through your code, checking the values of the local variables as you go for example? This would help us to know where to focus - which is difficult otherwise when faced with a function written by Microsoft which you have tailored in ways that we don't know to fit your circumstances.

If you are not very familiar with debugging in VBA, you may want to read NeoPa's introductory article on Debugging in VBA in our HowTo section.

By the way, the case change you mention in post #4 (capitalisation changed to lower case in word 'Audit') is not significant. It is done by the VBA interpreter replacing what you typed with the name of the field as stored in the fields collection - and it tells you that the name was defined in lower case. Otherwise, VBA is not case sensitive when it comes to names of variable, fields etc.

-Stewart

[zandiT]

thankyou stewart
i will debug it and let you know how it went on thrusday. thank you neopa for the debugging article.
Happy Eid



















h

[NeoPa]

Is Eid a Jewish New Year by any chance?

Happy Eid whether or no ;)

[zandiT]

Is Eid a Jewish New Year by any chance?

Happy Eid whether or no ;)

No it isnt Jewish. its a muslim holiday which marks the end of Ramadhan, which is the 30 day period of fasting and prayer. i was happy to get a day off work! speaking of work, you're going to think im a complete scatter brain but i spent the day debugging and fixing my audit trail and realised it was going to be more of a challenge getting the audit informaton in a report, and it doesn't log deletions!!. So ive now decided to go back to the original audit trail example(Allen Browne: Creating an AuditTrail) i had used before and make it work this time. at least that way when it does work, generating audit reports won't be a problem. I'll use your debugging tips and i hope for the best this time around.
thanks

[NeoPa]

Ah. My mistake. I live in London and one of my colleagues (who is Jewish) brought cakes around to celebrate Jewish New Year that same day so I put two and two together and made five.

Please post any more questions you have.

Just as a last FYI (for Your Information) logging deletions always requires a before and after design. Additions, modifications and deletions can all be handled with this approach, but it typically takes more space than a simple after-image logging design.

[zandiT]

Ah. My mistake. I live in London and one of my colleagues (who is Jewish) brought cakes around to celebrate Jewish New Year that same day so I put two and two together and made five.

Please post any more questions you have.

Just as a last FYI (for Your Information) logging deletions always requires a before and after design. Additions, modifications and deletions can all be handled with this approach, but it typically takes more space than a simple after-image logging design.

Hi..
i realised yesterday after a day of endless debugging with no success that the allen browne audit trail example is for bound forms only. my form is unbound so i have to re-strategise. please tell me if this plan makes sense. i have a main table called tblMain, and a main unbound form called frmMain, where all the deletion editions and new records are added and its recordset is tblMain. I created a new table called tblAudit with the same fields as tblMainTable, as well as extra fields for AuditType (deletion, addition or edit) AuditDate and Username of the user who made the changes. here is an illustration...

tblMain
Field1 = MainID
Field2 = Issues
Field3= Status

tblAudit
Field1 = AudID
Field2 = MainID
Field3 = Issues
Field4 = Status
Field5 = AuditDate
Field6 = Username
Field 7= AuditType

frmMain[u]
txtMainID
txtIssues
txtStatus
cmdAdd
cmdDelete
cmdEdit

So when a user Adds a new record a copy should be saved in tblMain and tblAudit, like this......

private sub cmdAdd()
Dim db As Database
Dim rstMain As Recordset
Dim rstAudit As Recordset
 
Set rstMain = CurrentDb.OpenRecordset("SELECT * FROM tblMain")
Set rstAudit = CurrentDb.OpenRecordset("SELECT * FROM tblAudit")
rstAudit.AddNew
 
rstAudit("Issues") = Me.txtIssues
rstAudit("Status") = Me.txtStatus
rstAudit("MainID") = Me.txtMainID
rstAudit.update
 
rstMain.AddNew
rstMain("Issues") = Me.txtIssues
rstMain("Status") = Me.txtStatus
rstMain("MainID") = Me.txtMainID
rstMain.update
 
end sub
 
Private sub cmdDelete()
Dim db As Database
Dim rstMain As Recordset
Dim rstAudit As Recordset
 
Set rstMain = CurrentDb.OpenRecordset("SELECT * FROM tblMain")
Set rstAudit = CurrentDb.OpenRecordset("SELECT * FROM tblAudit")
rstAudit.AddNew
rstAudit("AuditType") = "Deletion"
rstAudit.update
rstMain.Delete
 
end sub
 
Private sub Edit()
Dim db As Database
Dim rstMain As Recordset
Dim rstAudit As Recordset
 
Set rstMain = CurrentDb.OpenRecordset("SELECT * FROM tblMain")
Set rstAudit = CurrentDb.OpenRecordset("SELECT * FROM tblAudit")
 
rstAudit.AddNew
rstAudit("AuditType") = "Edit"
rstAudit.update
 
rstAudit.Edit
rstAudit("Issues") = Me.txtIssues
rstAudit("Status") = Me.txtCurrentStatus
rstAudit("MainID") =Me.txtMainID
rstAudit.Update
 
rstMain("Status") = Me.Status
rstMain("Issues") = Me.txtIssues
rstMain("MainID") = Me.txtMainID
rstMain.Update
End sub

Thats my plan. i tried it out and it didnt work and i know its because my code isn't correct at all. i know when i edit or delete i need to save the current image in the audit table before any modifications but i don't know how to do that exactly.
i hope i managed to explain clearly what my problem is. i have learnt that in future i will avoid unbound forms if i can get way with it. but in this project(my first one) i had to use unbound forms. thankyou for the help i know its a lot to ask for. i would want to implement the before and after event you had mentions but to be honest i don't know how as yet and time is not on my side

[zandiT]

Hey its working now. now im done just error handling and thats it.

[NeoPa]

I'm very pleased to see you've made such progress :)

I would just mention, that before and after logging would require two records for an edit, so simply marking a log as an edit will probably not do you in that case.

It sounds like you've really got a hold of this though and know where you're going.

[zandiT]

I'm very pleased to see you've made such progress :)

I would just mention, that before and after logging would require two records for an edit, so simply marking a log as an edit will probably not do you in that case.

It sounds like you've really got a hold of this though and know where you're going.

yes im really happy. i was always really sceptical about any kind of programming and databases but after this i am going to take it seriously and really understand what it involves. thankyou again for the forum. by the way i know i can't make it executable but what do you think is the best way to finish it off.

[NeoPa]

by the way i know i can't make it executable but what do you think is the best way to finish it off.

I'm very pleased to hear how you're getting on with Access now :)

As for releasing the final version (I assume this is what you're asking here), I would recommend posting a new thread for this.

My understanding of that area is very limited as I don't normally have to worry about it (I simply use MDBs everywhere). We also have a number of experts (not involved in this thread so might not otherwise notice the question) who I believe would have a lot to say on the matter. It will be a good thread for searching on later I expect.