By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

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

P: 48
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")

Expand|Select|Wrap|Line Numbers
  1. Dim dbs as DAO database
  2. Dim rstMain as Recordset
  3.  
  4. sub FillFields
  5.  
  6. Me.tbAuditTrail = rstMain("AuditTrail")
  7. End Sub
  8.  
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
Sep 30 '08 #1
Share this Question
Share on Google+
16 Replies


Expert Mod 2.5K+
P: 2,545
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
Sep 30 '08 #2

Expert Mod 2.5K+
P: 2,545
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
Sep 30 '08 #3

P: 48
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function Audit_Trail() On Error GoTo Err_Audit_Trail 
  2. Dim MyForm As Form
  3.  Dim ctl As Control
  4.  Dim sUser As String
  5.  Set MyForm = Screen.ActiveForm 
  6. sUser = "SELECT tblMain.Username FROM tblMain"
  7.  sUser = CurrentUser
  8.   If MyForm.NewRecord = True Then 
  9. MyForm!audit = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";" 
  10. Exit Function
  11.  End If 
  12.  
  13. MyForm!audit = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"
  14.  For Each ctl In MyForm.Controls
  15.  
  16. Select Case ctl.ControlType Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
  17.  If ctl.Name = "tbAuditTrail" Then
  18.  GoTo TryNextControl 'Skip AuditTrail field
  19.  
  20. If ctl.Value <> ctl.OldValue Then
  21.  MyForm!audit = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value 
  22.  ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
  23.  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" 
  24. End If 
  25. End Select 
  26. TryNextControl: Next ctl Exit_Audit_Trail: 
  27. Exit Function Err_Audit_Trail:
  28.  If Err.Number = 64535 Then
  29.  'Operation is not supported for this type of object.
  30.  Exit Function
  31.  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"
  32.  Else Beep MsgBox Err.Number & " - " & Err.Description
  33.  End If
  34.  Resume 
  35. Exit_Audit_Trail
  36.  End Function
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  On Error GoTo Form_BeforeUpdate_Err 
  3. Call Audit_Trail 
  4. Form_BeforeUpdate_Exit:
  5.  Exit Sub 
  6. Form_BeforeUpdate_Err: MsgBox Err.Number & " - " & Err.Description Resume Form_BeforeUpdate_Exit
  7.  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
Sep 30 '08 #4

P: 48
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?
Sep 30 '08 #5

NeoPa
Expert Mod 15k+
P: 31,709
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.
Sep 30 '08 #6

P: 48
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
Sep 30 '08 #7

Expert Mod 2.5K+
P: 2,545
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
Sep 30 '08 #8

P: 48
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
Sep 30 '08 #9

NeoPa
Expert Mod 15k+
P: 31,709
Is Eid a Jewish New Year by any chance?

Happy Eid whether or no ;)
Sep 30 '08 #10

P: 48
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
Oct 2 '08 #11

NeoPa
Expert Mod 15k+
P: 31,709
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.
Oct 2 '08 #12

P: 48
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......

Expand|Select|Wrap|Line Numbers
  1. private sub cmdAdd()
  2. Dim db As Database
  3. Dim rstMain As Recordset
  4. Dim rstAudit As Recordset
  5.  
  6. Set rstMain = CurrentDb.OpenRecordset("SELECT * FROM tblMain")
  7. Set rstAudit = CurrentDb.OpenRecordset("SELECT * FROM tblAudit")
  8. rstAudit.AddNew
  9.  
  10. rstAudit("Issues") = Me.txtIssues
  11. rstAudit("Status") = Me.txtStatus
  12. rstAudit("MainID") = Me.txtMainID
  13. rstAudit.update
  14.  
  15. rstMain.AddNew
  16. rstMain("Issues") = Me.txtIssues
  17. rstMain("Status") = Me.txtStatus
  18. rstMain("MainID") = Me.txtMainID
  19. rstMain.update
  20.  
  21. end sub
  22.  
  23. Private sub cmdDelete()
  24. Dim db As Database
  25. Dim rstMain As Recordset
  26. Dim rstAudit As Recordset
  27.  
  28. Set rstMain = CurrentDb.OpenRecordset("SELECT * FROM tblMain")
  29. Set rstAudit = CurrentDb.OpenRecordset("SELECT * FROM tblAudit")
  30. rstAudit.AddNew
  31. rstAudit("AuditType") = "Deletion"
  32. rstAudit.update
  33. rstMain.Delete
  34.  
  35. end sub
  36.  
  37. Private sub Edit()
  38. Dim db As Database
  39. Dim rstMain As Recordset
  40. Dim rstAudit As Recordset
  41.  
  42. Set rstMain = CurrentDb.OpenRecordset("SELECT * FROM tblMain")
  43. Set rstAudit = CurrentDb.OpenRecordset("SELECT * FROM tblAudit")
  44.  
  45. rstAudit.AddNew
  46. rstAudit("AuditType") = "Edit"
  47. rstAudit.update
  48.  
  49. rstAudit.Edit
  50. rstAudit("Issues") = Me.txtIssues
  51. rstAudit("Status") = Me.txtCurrentStatus
  52. rstAudit("MainID") =Me.txtMainID
  53. rstAudit.Update
  54.  
  55. rstMain("Status") = Me.Status
  56. rstMain("Issues") = Me.txtIssues
  57. rstMain("MainID") = Me.txtMainID
  58. rstMain.Update
  59. 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
Oct 3 '08 #13

P: 48
Hey its working now. now im done just error handling and thats it.
Oct 3 '08 #14

NeoPa
Expert Mod 15k+
P: 31,709
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.
Oct 3 '08 #15

P: 48
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.
Oct 3 '08 #16

NeoPa
Expert Mod 15k+
P: 31,709
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.
Oct 3 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.