Audit Trial
Nov 29, 2005Is there a way to keep track of the users and which form they updated and when? and to be able to view/print reports of it?
this is provided tt i use the group security feature with User ID and password.
Is there a way to keep track of the users and which form they updated and when? and to be able to view/print reports of it?
this is provided tt i use the group security feature with User ID and password.
I have several tables and queries based on tables and cannot get them to generate the results I want when querying them (just one record shows when many should be showing), so this leads me to believe there's a problem with the way I have set my relationships;
tblStaff (Holds lists of all staff and their contact details)
queryCoordinator (Filters list of staff from all staff list based on whether they are a projectmanager)
queryLeader (Filters project leaders from list)
queryMinion (Filters minions from list)
tblProject Contains all details from project
I have the staff queries so I can choose from a list of names that are relevant to that job in a form, so we don't have to look through a huge list of staff.I then want to use a query to pull all this information together (project details and contact details of each person involved) so that I can print a single record on a report, however the query isn't working correctly and returns only one project record, not all of them.
There is only one leader, one project coordinator and one minion per project (as far as I am concerned) and I want to reflect this in the query by providing their name and contact details from the single staff list.
Is the relationship between the project and the individual roles Many-to-Many or One-to-Many. I have many projects, and many staff, however each project only has one leader, one coordinator and one minion. I'm thinking One-to-Many, however when I relate the tables together, using the name as the relationship on each form this is where I have problems.
Access 2007 is behaving very peculiar concerning sizing of forms. For example, some open at an enormous size (25 plus inches) when the width size in the property box is 8 inches. I've tried various combinations of Auto Center, Auto Resize and Fit To Screen -- all with bazarre, unpredictable results. Does anyone know if there is an "issue" (bug) in Access 2007 Trial concerning form sizing? Thanks.
View 8 Replies View RelatedI have a database on a network which is shared by other users.
These users are supposed to use this database monthly to montitor there budgets.
I want to be able to create an audit trail on each time they open the database. I am using the OSUserName function to get there netwotk login id.
Can someone give me some code to put in the db so that when it opens, it inserts the user,date,time in and audit table.
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
Thanks for the hlep
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??
Help me, Thanks!
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?
View 1 Replies View RelatedHi - 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...
Can upload a sample test of required??
Thanks anyone that is listening?
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.
I would be grateful for somehelp.
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?
View 1 Replies View RelatedHi
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"
Anybody got any clues, please
Thanks, in advance.
ajm
well i have a scenario like this,
there is a standalone msaccess database(*.mdb)...
i want to create an audit table cantainig the respective detaill(created or update by,time stamp.n the data that is update or created)...
the challenge is there is no form ....the database is manully updated...i mean anybody can opne the databse n alter the existing table inside the databse....
its just a standalone database..
is it possible to achieve???
if yes...
how can this be achieved???????
if not is there any other approach that i can follw????
will really appreciate for ur feedbacks...
thanks in advance.......
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?
Thank you
Hello forum!
Im pretty new to access and im trying to create a database for the company i work at so that to begin with they can log details on all hardware they have.
See the image attatched, at the moment i wish to store details on there computers and also on other hardware is it ok sofar?
http://img210.imageshack.us/my.php?image=erd9xa.jpg
Recording to a table Who is logged on to Database, When they logged on and when they logged off.
Gets tricky as I really would like the info to come from the mdw file(all users go through this for access)
If someone can help would be great. Need my hand held for this as I have been attempting to do this for over a month with no real success. I have so many samples that don’t quite make it happen I am now totally confused.
Forever grateful - every crumb helps
Danson
Hi,
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?
Any advice would be greatly appreciated!
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
Is their a way to create a audit log in access?I have a log-in screen on my db and every time a user logs in i want it to be recorded. is their away to do it?
View 14 Replies View RelatedI 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.
Hi all,
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.
Thanks in advance for any help.
Cheers,
Chris
How do I write a query to find the last audit date?
Let's say the table looks like this (year,company,audit date):
2004, Company1, 6/3/2004
2005, Company2, 2/2/2005
2006, Company3, 3/7/2006
2008, Company4, 3/2/2008
Let's say I want to add a new record (in the year 2010) and pull up the last audit date. How do I do that?
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)
Many thanks,
CB