General :: Audit Trail - Capture Occurrences When Multiple Records Selected To Be Deleted
Jan 15, 2013
I've always used an audit trail for tracking changes of records that already exist but I have had a situation come up where I now need to track records that have been deleted. For example, if I delete a record in the datasheet view.
I've seen several examples how to do this, with the most common seeming to store the data into a temporary table while waiting for the delete confirmation. If I am reading the notes correctly, it's done this way to capture occurrences when multiple records are selected to be deleted. Is this the best approach for this type of task?
I have a form with a few tabs and 3 subforms. I used the following Audit Trail [URL] .... to complete what I have so far and it's been really great. Simple, easy to use and works well. The only issue that I have is it doesn't read the edits from the sub forms. It tells me when a new record is created or deleted but I can't see if anything was changed or what was input into the new record.
I believe it's something to do with Screen.ActiveForm but I can't be certain.
Below is the code that I use in a module and the code on the event section of the forms and the module that I use. Also all the controls I want audited have Audit in the tag of the Other menu.
BadAudit Module
Sub AuditChanges(IDField As String, UserAction As String) On Error GoTo AuditChanges_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim ctl As Control
[Code] ....
The After Del and Before Up are also on the forms SuspensionsSubFrm, ReviewsSubFrm and Framework which are all subforms.
I have been using Access for quite some time now and I am able to usually work around many of the issues that come with being a small timer, like me, taking his best shot at Access; however one issue I have had absolutely no luck with is auditing a new and/or change made to an "Attachment Field". I have successfully been able to audit every type of field with the exception of the "Attachment Field".
I am trying to create a simple audit trail (which I got directions online) in my Database (and have posted a copy here) but am getting an error on the second form (and this will be a subform eventually) -The Audit Trail instructions say to put four fields in my table which feeds the form - CreateBy=Text, CreateDate=Date/Time and default to =now(), ModBy=text, and ModDate=Date/Time.
Then to add code to the Current and BeforeUpdate events on the form as you will see in my forms attached. I created a test database with very little data (called AuditTrackingTest2DBLp-attached). I created the first table (tblCustomers) and then created the form (FormCustomerInput) and then I added code to this form on the Current and BeforeUpdate as I was instructed in my notes and it recorded my changes perfectly in the tblCustomers.
I then created a new table called tblCustomerOrders and then a form (FormCustomerSubOrders) based on this table and added the same code to this form and it is giving me an error message in the code Compile Error: Variable not defined.
I have searched this subject out and tried some of the solutions but have more questions.
All I really need is a table with the username, date/timeIn and date/timeOut of the "database" forms, reports and all that don't matter...yet.
I am able to get the user info to show on a form (that comes up on startup) but can't get the info from the form into a table. I have tried an append query but no luck. If I see it on a form shouldn't I be able to "grab" it? I have used all kinds of code found here to "get" the info and same problem, I can see but I can't touch.
I have created the following code to put the date and username into a table to track any changes made to a record. All I am interested is when the change was made and by who.
When I change a record the Now() command works fine and puts the date and time but the Username = User_FX does not grab the logged in to win nt domain user any Ideas
Private Sub Form_BeforeUpdate(Cancel As Integer) ' Log the user details to the table Me!SystemUsername = User_FX Me!RecordChanged = Now() End Sub
My audit trail is doing fine, but now my boss needed the "memo" to be highlighted or numbered so its easier to see whether the record was updated. (And the date wasn't enough:mad: ) So is there any possible way to have it highlighted/numbered after edited??
Hi, I am trying to use Audit Trail in Access2000. I tried to look at this snippet from Microsoft. But I am getting an error when I save the record. Actually I have a form with 8 tab pages. It tracks that the change is made, even though it gives error message, but does not track which field was changed. I would appreciate if someone can help me. Regards K
Function AuditTrail() On Error GoTo Err_Handler
Dim MyForm As Form, C As Control, xName As String Set MyForm = Screen.ActiveForm
'Set date and current user if form has been updated. MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _ "Changes made on " & Date & " by " & CurrentUser() & ";"
'If new record, record it in audit trail and exit sub. If MyForm.NewRecord = True Then MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _ "New Record """ End If
'Check each data entry control for change and record 'old value of Control. For Each C In MyForm.Controls
'Only check data entry type controls. Select Case C.ControlType Case acTextBox, acComboBox, acListBox, acOptionGroup ' Skip Updates field. If C.Name <> "Updates" Then
' If control was previously Null, record "previous ' value was blank." If IsNull(C.OldValue) Or C.OldValue = "" Then MyForm!Updates = MyForm!Updates & Chr(13) & _ Chr(10) & C.Name & "--previous value was blank"
' If control had previous value, record previous value. ElseIf C.Value <> C.OldValue Then MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _ C.Name & "==previous value was " & C.OldValue End If End If End Select Next C
TryNextC: Exit Function
Err_Handler: If Err.Number <> 64535 Then MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description End If Resume TryNextC End Function
I know this has been asked many times and I have tried following all the threads but I have been struggling and cannot get the audit trail to work on a subform. Please, does anyone have a working example, or can point me to one, of the audit trail working on a subform ? I've been tearing my hair out trying to get it to work. :confused:
Got a problem with that audit trail that has been posted on here.
Basically I can get it to work on all put one subform, and I don't know why.
I have a main form with a tab control placed on it containing all of the subforms in my db. On the form it doesn't work on it seems to be inputting into the updates control incorrectly. With all the others it works fine, and displays the changes as follows in the updates control:
Record added on 26/10/05
Changes made on 26/10/05 10:23:55 by johns; Date: was previously Null, New Value: 20/03/05
But using the subform that is not working, it shows something like:
Changes made on 26/10/05 10:23:55 by johns;Date: was previously Null, New Value: 20/03/05
Can anyone help with this, I'm not sure if it is because it is getting confused between the tab control and the main form.
Anyone any ideas, let me know if you want me to post the code. I can come back and post one that works, and the one that doesn't.
i created an audit trail in my DB... i do need to submit reports reflecting the relevant changes made.... however, i notice that the report can be very lengthy if not renewed every month.... can i archive these trails.. on a mthly basis? if yes.. how ? if not... is there anything else i can do?
Hi - This is what I am trying to do, any help greatly appreciated... i.e. Customer Table Customer History Table Import Table
1 Insert New Cust from Import Table into Cust table - Yes can do 2 Insert New Cust History from Import Table into Cust History Table - Yes can do 3 Update changes in customer table from Import table - yes can do
Now comes the tricky part
4 Next step is to insert a record into the customer history table where customer changes are happening - yes can do for the first time
HOWEVER this will keep updating same changes!!! If I then run the query again - see below
I want to take the SQL into vb and put all together and click button to run import as the import table will come from another database every day
Once I have run step 4 i don't want it to update the customer history table again next time that I run above said button...
There are other issues but this is my first stuck step and having been banging my head for two days...
I have search the forum for a solution but I having a little difficulty understand some of soultions provided.
Getting the Audit trail, created g G Hudson work magnificently on a main form, to work on my subform called subformMovements is giving me a little grief.
I dont really know how to fix my problem. I have attached a copy of my DB is someone who like to give me a hand to figure it out.
The Audit Trail function (GHudson) on my form is still working wonderfully. But I was wondering, will it add alot to the overall size of the DB? It seems to record a lot of information (which is what it is made for lol). Has anyone had problems with it growing too large?
Hi Ive downloaded and installed the AuditTrail zip file as mentioned in a different thread. It works fine on my main form, but I also have a subform on that form. I am trying to pass "ME" as a parameter to a second function ((Audit_TrailSub(Frm as form)) on the Before Update command ie =Audit_TrailSub(ME) However when I attempt to change a record in the subform I get a message . . . "The object doesn't contain the automation object ME"
I hate to bring another Audit Trail question to the table, but I am scratching my head for thelast day trying to get to get this work.
Link to original post: http://www.access-programmers.co.uk/forums/showthread.php?t=44231&highlight=audit+trail
Problem: I have a main form/subform. The mainform is based on a query of 2 tables with a lookup field. The subform is based on a query referencing the PK for one of the main form's tables.
I have gotten the audit trail to work on the subform, no problems there.
But I get an Error 3251 - Operation is not supported for this type of object.
When I press ok, the record is changed and saved. The memo field logs that a change has been made, but does not list what was changed or what it was changed from.
I have tried the following: Changing the lookup fields names as Lookup1, Lookup2...etc the changing the line of code to skip those fields along with tbAuditTrail.
I have tried adding a second module named different/changed function name and calling it...no luck
Creating a just a new main form and tring it...zilch
I read something about recordsetclone, but do not know exactly how to add this in the module to try.
Does anyone have any other suggestions/or solutions that I may try?
I have discovered a problem with my database and wonder if anyone here can point me to the best solution...
The problem is this... when I took our company database over about 3 years ago it was around 45MB... now it is almost 350MB and growing steadily.
I have today discovered the main cause of this... the audit trails on a couple of the tables. If I remove these 2 fields only from the database it cuts it down to under 100MB. The Audit trail is quite important for us and so I'd like to keep it if I can. It is stored in a memo data type (the data is well over the 255 char limit of a text box). Is there any other better way to store this data or is there anything I can do to reduce the size of the memo data type?
Hi All, i need some more help. I have an access application which has 6 different forms. The application will be accessed by 10-12 people. some can have an access to to all the forms and some may have an access to a few of them. How can I set up the password security for the users at application and form level? I also want to capture the audit trail when the user users edit form and makes changes to a record. I would apprecaite help on this one. Regards K
Through years of hard work I have created a fully functioning double-entry accounts system with epos and inventory management capabilities for my retail business.
Each transaction is stored once - either in tblPurchaseInvoices, tblSalesOrders, tblPayments, tblReceipts or tblJournalEntries.
The double entry part is created automatically by queries as is all the vat/sales tax information. Producing: qryPI_VAT, qrySO_Vat, qryPaymentVat, qryReceiptVat, qryTradeDetors, qryUnpaidInvoices.
The audit trail then combines all of these queries (a total of 11!) in a union query. This is obviously a very slow process and produces 63,354 records currently. Searching for information in this list is a nightmare as are calculations!
Can anyone point me in the right direction of creating a more efficient audit trail. I would have thought one transaction table would be the way to go but I can't see how it could be done.
Could someone please help. I am in a serious time crunch and cannot figure out what I have done wrong. I have copied the module attached for the audit trail and have implemented it into my database. For some reason when I go to run the BeforeUpdate event I get a 13-Type Mismatch error. I have found a fix that will allow me to use the code without flagging an error, but I am not sure it is working properly now. Here is the fix.
1. Pass the form as an argument of the function Function AuditTrail(frm as Form)
2. Comment out these lines in the code 'Dim frm as Form 'Set frm=Screen.ActiveForm
3. Call the function in the BeforeUpdate event of the form and or subform as follows: Call AuditTrail(Me)
I am using an audit trail on my database that works perfectly. My question is, can I run an update query and still log any updates through the audit process?
I suppose the update query is not a necessity, but it would be nice to retain it if possible.
I am having a bit of trouble with one part of it. The DB I am putting together has a login form with a combo box for the user name, and a textbox for password entry. A small macro tied to a button compares the password input to a column in the combo box that pulls from a User table. If the password matches, a main nav form opens, and the login form goes to hidden. What I am trying to do is grab the User Name from the combo box, and store it in a field in the audit trail table when a user is in one of the data entry forms.
i have an application with an audit trail module, i do read the table name , field name,old value, new value. but the lables of the fields at my forms are changed to arabic language
for example
lets say i do have a table the name of one field is Name on the form the label of this field is Lawyer Name
now the client is asking me to show the ( Lawyer Name ) not the field name ( Name ) in the audit trail to know the changes were made.
I thought to go to the tables and on each field 's caption i shall add in arabic the same as in my form
I am trying to read the caption of the field from the tables
i hope you understood what i mean
if you show me the code to do that , it would be highly appreciated
I am in the process of creating a database with multiple tables. Multiple users access through network with access data back end on server and Different Forms with user and group access of MS Access. The file is in mdb format and current version of MS Access used by all my users is Access 2007.
Requirements :
1. User Login (Access user login preferred as, one can set permission for read only, write permission for specific field etc.) But whether if any user who has readonly rights create his own db, wont be able to edit their own because of the rights that i have set rights in their pc??
2. need to fetch this user id with time stamp in each table to find who has altered the data, though audit trail table with ip address will be ideal.
3. Hide the navigation pane and ribbon for anyone other than administrator, including office button.
4. User level security is it required to set in every pc where the front end sits or one pc where the data is located.
After a lot of reading and consolidating VBA codes for audit trail.How My Audit Trail Works..A module was made for a function named as "Changes" .Then inserted into before update event of a form where I will do the editing of the records.Then I made a table named as Audit.Inside this table I made all the fields I needed such as:
*AuditRecordId[autonumbered] *FormName[The name of the form for editing] *Index[The record ID of the record being edited] *ControlName[The Field being edited] *DateChanged[Date change was done] *TimeChanged[Time change was done] *PriorInfo[for the old value of data being changed] *NewInfo[For the new value of data changed] *CurrentUser[The user base on log in form that was set to Global into another module] *Reason[The reason for changing for future references]
And Here is the Function Code:
Code:
Option Compare Database Option Explicit Function Changes() Dim db As Database Dim rs As Recordset Dim strSQL As String Dim strCtl As String Dim strReason As String
[code]....
This audit trail function is valid only for one(1) form, due to the limitation of
Code: Screen.ActiveForm.Controls("SUP ID").Value
where "SUP ID" is the primary key of the record being updated/Change, so if there are Five(5) tables that needs audit trail, there will be also Five(5) forms, as well as Five(5) Function Changes namely; Changes(), Changes1(),Changes2(),etc... because all the table do have their own sets of primary Key.
Is there a shortcut, in such a way that the "rs!Index" will automatically return a value, equivalent to the Primary Key/Record Id of the record being updated/change, given that there are different updating forms for each table to be updated?
