Login and Audit trail

[johannaellamay]

I have a database for HR. It's almost finished. I've already created a login form. I want to create an audit trail. I've already researched about it and I think I could do those. However, they're not exactly what I was looking for. I want my audit trail table to be connected to my login form.

Most, if not all, of the examples that I found in the internet about audit trails have a "created by" or "edited by" tracker that uses only the username from the login in Windows, and not the login from my actual login table on Access, if that makes sense.

I used this (http://scottgem.wordpress.com/2012/10/18/audit-trail-using-data-macros-2/) for my reference because it's the easiest one I could find. However, I don't know what I should input as the value for "changeby."

Bottomline is, I want to create an audit trail that utilizes the username from the login form/table in my database, and not from Windows or something.

Can you please help me? Or do you know any references I can study? I would really appreciate it.

 

[namliam]

Try the windows login?

http://support2.microsoft.com/kb/161394

 

[johannaellamay]

Try the windows login?

http://support2.microsoft.com/kb/161394

Hi thank you for the response. However, as I've said in my first post, I don't want to use the username in Windows. I want to use the username in my login form which is also in access. Do you think it's possible?

 

[namliam]

Guess I stopped reading at: for "changeby.", Appologies

Well, do you keep the login form open or hidden, so you can get the login name from there?

Or store the login username in a public varaible, perhaps?

 

[johannaellamay]

Guess I stopped reading at: for "changeby.", Appologies

Well, do you keep the login form open or hidden, so you can get the login name from there?

Or store the login username in a public varaible, perhaps?

Hmm. I've set the form to:

 Me.Visible = False

Is that what you are talking about? This is what my code looks like under f_Login

Private Sub cmdLogin_Click()

'Check that User is selected
If IsNull(Me.cboUserName) Then
    MsgBox "Please select a User Name.", vbCritical, "Select a User Name"
    Me.cboUserName.SetFocus
Else

'Check for correct password
If Me.txtPassword = Me.cboUserName.Column(2) Then
    DoCmd.OpenForm "f_Employee"
    Me.Visible = False
    
Else

MsgBox "Password is incorrect. Please re-enter!", vboOkOnly + vbExclamation, "Incorrect Password"
Me.txtPassword = Null
Me.txtPassword.SetFocus

End If

End If

End Sub

 

[namliam]

if your form is still open, but hidden you can fetch the login using something like:

Forms("F_Login").cboUsername

 

[cnstarz]

You could also use a TempVar if using Access 2007 or later: http://www.utteraccess.com/wiki/index.php/TempVars. That way you dont have to keep the form open and hidden all the time (less resources).

 

[irish634]

I wrote this a while ago (Audit Trail) and have modified it a bit since, but I use a global variable from my login forms and pass the value to the "DB User Field". I also log the PC name and the windows log in as well

 

[johannaellamay]

if your form is still open, but hidden you can fetch the login using something like:

Forms("F_Login").cboUsername

Thanks! I tried to do this and it worked. But I have to keep the form hidden or invisible right? I will try TempVars just because I don't want to have to keep the form open all the time. But if I can't make it work, then I'll go back to this procedure.

 

[johannaellamay]

You could also use a TempVar if using Access 2007 or later: http://www.utteraccess.com/wiki/index.php/TempVars. That way you dont have to keep the form open and hidden all the time (less resources).

Yes, thank you. I'me still trying to figure out how this works. I tried to use TempVar but Access just tells me: "Invalide Outside Procedure." I don't even know what that means so right now, I'm stuck.

 

[spikepl]

"Invalid Outside Procedure" is one of the most idiotic and ambiguous messages generated by access, and its author ought to be hanged, drawn and quartered.

In a module you can only declare variables or define functions and subroutines. Nothing else. You have probably attempted to do some operation ( assign a value?) to a TempVar not inside a function or subroutine, but outside.

Update: The same info you could have obtained by putting the error message into google. 99% of issues have been dealt with and the solutions are findable using google. Being able to google effectively is a huge time saver. Work on it.

 

[cnstarz]

To create/use a TempVars variable called "Login" that stores the value of a selected item from a ComboBox named "cboLogin", use this code on the combobox's On Change event:

TempVars.Add "Login", Me.cboLogin.Value

To retrieve the value of the "Login" TempVars variable, use this code:

TempVars.Item("Login")

Say I want a button that displays the Login chosen from a combobox:

Private Sub Command8_Click()

    MsgBox TempVars.Item("login")
    
End Sub

Remember, you must be using Access 2007 or greater.

 

[spikepl]

@cnstarz
Not On Change, which fires for every letter you type in the combo, because that is unnecessary overkill. After Update is the one.