Audit trail, tracking user changes with data macro and USer login from form? (1 Viewer)

Db-why-not

Registered User.
Local time
Today, 09:25
Joined
Sep 17, 2019
Messages
133
Hello,
I created a Audit table to track all changes made to my database. I got the audit table to track my changes , but I am trying to track which users make the changes also. I used this website to set up my audit table followed the directions.
https://scottgem.wordpress.com/2012/10/18/audit-trail-using-data-macros-2/

I'm just having difficulty getting the data macros to track who makes the changes. I followed the directions and added a module
Module is call fUser

Public Function fUser()
fUser = Forms!User_Login!cbo_user
End Function

I added it to my backend database and my front end where I have all my forms and querys. It doesn't work and it stops tracking my changes when I add it to the data macro. I have a User_Login form that tracks the users.
 

Attachments

  • Data MAcro.JPG
    Data MAcro.JPG
    34 KB · Views: 14

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:25
Joined
Aug 30, 2003
Messages
34,748
I haven't used data macros but I have this feeling that you can't use VBA in one. Can you just refer to the form control?
 

Db-why-not

Registered User.
Local time
Today, 09:25
Joined
Sep 17, 2019
Messages
133
I haven't used data macros but I have this feeling that you can't use VBA in one. Can you just refer to the form control?
I will try!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:25
Joined
Aug 30, 2003
Messages
34,748
Though it occurs to me if the macro is in the back end it will have no knowledge of the form control.
 

Db-why-not

Registered User.
Local time
Today, 09:25
Joined
Sep 17, 2019
Messages
133
I tried Set Field
Name: Audit_trail.ChangeBy
Value = [Forms]![User_login]![cbo_user]

I tried Value =Forms!User_login!cbo_user, that didn't work either.

It still didn't work. The Login form is on my front end though. Can the data macros take values from forms that are on the frontend
 

Attachments

  • Data MAcro2.JPG
    Data MAcro2.JPG
    27.6 KB · Views: 10

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:25
Joined
Aug 30, 2003
Messages
34,748
Can the data macros take values from forms that are on the frontend

I was afraid that was going to be the case. I use code-based audit trails, though I can see the advantage of a data macro method. Hopefully jdraw's method works for you.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:25
Joined
Aug 30, 2003
Messages
34,748
use fOSUserName

Even if it would run, wouldn't that face the same limitation as a form reference in a back end data macro? The back end has no knowledge of the user's computer.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:25
Joined
Aug 30, 2003
Messages
34,748
Well, I normally use SQL Server so haven't tried data macros, but I'd expect that to pick up the user logged into the computer hosting the back end rather than the user that actually made the change. Wouldn't be the first time an assumption of mine was wrong though. There appear to be broken links in Albert's first post, but it appears there are more hoops to jump through when the data macro is running on the back end.
 

bastanu

Registered User.
Local time
Today, 07:25
Joined
Apr 13, 2010
Messages
413
I agree Paul, it would get more complicated, I never used data macros other than tinkering with them when they first came out (for an audit trail.....).
I wonder if this would help to identify the user:

Cheers,
 

Db-why-not

Registered User.
Local time
Today, 09:25
Joined
Sep 17, 2019
Messages
133
I was searching google and I found a solution. I tested it and it works now. It kind of makes my user login page not needed anymore though. It gets the userID directly from my computer Login instead.
I found this utube video and did what it said.

I created a function added it in my backend and front end. Then I added a BeforeUpdate MAcro to my audit table and have it call up the function to give my ChangeBy field the userid value in my audit table.

Function:
Option Compare Database
Option Explicit
Public Function UserName() As String
UserName = Environ("UserName")
End Function

BeforeUpdate Macro
SetField: Audit_trail.ChangeBy
Value: UserName()

Its pretty easy also. Thanks for everyones help.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:25
Joined
Aug 30, 2003
Messages
34,748
Glad you got it working.
 

Users who are viewing this thread

Top Bottom