Audit Tracking

[LisaEllen]

Hello,

I am trying to set up simple tracking of changes made to our database but am having trouble.

I found simple tracking instructions and did as it directed but then in errors out on the code.

I added the four fields to my table that underlines the form as instructed (see below).

CreateBy
CreateDate
ModBy
ModDate

Then I went into the form and did the next step which was to add code to the Current and BeforeUpdate events of the form...see below...

Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
ModBy = Environ("username")
ModDate = Now()
End Sub

Private Sub Form_Current()


If Me.NewRecord Then
CreateBy = Environ("username")
End If
End Sub

When I saved it I got the below message...

Compile Error - Variable not defined on CreateBy = Environ("username")

I am trying this at home and not at work and I think it wants me to define username but not sure with what - we don't require a log-in to the database - would I need to do that - not really sure?

Thank you for any help.

Lisa



This database does not require the users to log in - the users can simply open it and use the forms.

[alansidman]

In the immediate window type
?Environ("username")

What result do you get. You should get the name of the Windows logged in user.

[LisaEllen]

Hello,

I get Lisa1960 - please forgive me I am new at this...and if I have several users who use the database - how would I make this so it would add their login ?

Thank you for your help.

[alansidman]

This will work so long as the users log into the same Windows machine with different logins. It will also give a different user name for each machine depending on what was assigned when the system was set up.

The fact that you got a name in the Immediate window means that Environ is working.

[LisaEllen]

Thank you. So what happens is using the exact code above and inserting into the form - I make a changes to my form and when I try to exit the form - it errors out at the same point and I don't understand why?


Private Sub Form_Current()
If Me.NewRecord Then
CreateBy = Environ("username")
End If
End Sub

When I exit out - I get the below message...

Compile Error - Variable not defined on CreateBy = Environ("username")

[alansidman]

Can you post a sanitized copy (no confidential information) of your db for further analysis. Click on the Go Advanced button after selecting Reply to thread and follow the wizard. Run a compact and repair to minimize size and then zip the file.

[LisaEllen]

yes, but I have a lot of data in there -- do you know a quick way to do that or should I copy the forms and recreate a table with test data? Sorry to be such a pain. Thank you.

[alansidman]

I would copy the db. Edit the data to enough dummy data that will allow us to analyze what is happening.

[LisaEllen]

Hello again,

Thank you for helping me and I have been trying to figure it out but I am stuck. I am including the Database I use at work (AuditTrailDatabaseLisaP) with dummy data for you to look at. I then created a second database with very little data and no subforms (called AuditTrackingTest2DBLp). The funny thing is the AuditTrackingTest DB worked when I created the first table (tblCustomers) and then created the form (FormCustomerInput) and then I added code to this form 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 boom – it is giving me the same error message in the code – “Compile Error: Variable not defined” that I got in my other Database – but just not sure what I am doing wrong? I am sure you will know straight away J …
Again, thank you for helping me - I so appreciate it.

Lisa

[LisaEllen]

Here is the other one - it was too big to send with the other message...

[alansidman]

Lisa;
In the first db, I got it to work by adding the three fields CreateBy, ModBy and ModDate to the form. I then made them not visible. Doing this created the object and satisfied access. I know Denis does not discuss this in his presentation, but I recall doing this on one project a couple of years ago when I experienced the same issue. I needed to see it to recollect it. I hope this helps to solve your issue. Once this was achieved I was able to add records with no issues and the info appeared in the table.

[LisaEllen]

Thank you that did work. Again thank you for all the help.

Lisa

[alansidman]

glad to have helped.