I need to log user logon... (1 Viewer)

[Steindi]

Hi everyone,

I have a large database and it has a login screen wich user has to log on with a user name and password.

I need to log the user logon with a timestamp in a seperate table. Can anyone help.

I have a basic understanding of working with code and I'm a fast learner

Thanks,
Steindi

 

[April15Hater]

From where is the logon screen derived? Is it 2003 and below user level access, one that has been developed, or windows username?

Either way, I think using the currentdb.execute function with an append query (INSERT INTO) would do the trick here. The property/variable/function feeding the append query values would depend on the answers to the questions above. For the timestamp value, you can use the now() function. Make sure you set the data type in the table to Date/Time.

The now() function should have a pound sign (#) on each side and the username value should have quotes.

'strVClause will show VALUES (#10/10/2013 12:02:15PM#, 'jsmith')
strVClause = "VALUES (#" & now() & "#, '" & strUser & "')"

Or for the query to translate double quotes:

'strVClause will show VALUES (#10/10/2013 12:02:15PM#, "jsmith")
strVClause = "VALUES (#" & now() & "#, """ & strUser & """)"

Joe

 

[Steindi]

I'm working on a split database, Access 2010

The user chooses his username from a combobox. I got the basic code online and built arround it myself.


Thank you April15hater, I'll try your suggestions.

Steindi

 

[Steindi]

Still having trouble with this...

Is there no way i can put some code in the logon form that populates a field, with the user name in my userlogs table?

regards,
Steindi

 

[April15Hater]

can you post your code?

 

[Steindi]

Yes here is the code for the login form.


Private Sub txtPassword_AfterUpdate()

'Check that EE is selected
If IsNull(Me.cboUser) Then
MsgBox "Veldu nafn af listanum", vbCritical
Me.cboUser.SetFocus
Else
'Check for correct password
If Me.txtPassword = Me.cboUser.Column(2) Then
'Check if password needs to be reset
If Me.cboUser.Column(3) = True Then
DoCmd.OpenForm "frm_PasswordChange", , , "[UserID] = " & Me.cboUser
End If
DoCmd.OpenForm "Adal_form_1_SE"
Me.Visible = False
Else
MsgBox "Þetta lykilorð passar ekki!", vbOKOnly
Me.txtPassword = Null
Me.txtPassword.SetFocus
End If
End If
End Sub

 

[April15Hater]

Give this a go... It's untested, but should take care of what you need.

Private Sub txtPassword_AfterUpdate()
    'Check that EE is selected
    If IsNull(Me.cboUser) Then
        MsgBox "Veldu nafn af listanum", vbCritical
        Me.cboUser.SetFocus
    Else
 
        'Check for correct password
        If Me.txtPassword = Me.cboUser.Column(2) Then
 
            'Check if password needs to be reset
            If Me.cboUser.Column(3) = True Then
                DoCmd.OpenForm "frm_PasswordChange", , , "[UserID] = " & Me.cboUser
            End If
            DoCmd.OpenForm "Adal_form_1_SE"
 
            'Test if Userlog table exists.  If not, create it.
            If DCount("[Name]", "MSysObjects", "(Type = 1 OR Type = 6) AND [Name] = " & _
                "'tblUserLog'") < 1 Then
                CurrentDb.Execute _
                    "CREATE TABLE tblUserLog " & _
                    "(tblUserLogPK AUTOINCREMENT PRIMARY KEY, TimeStamp DATETIME, " & _
                    "Username CHAR);"
            End If
 
            'Append Username and timestamp to tblUserLog
            CurrentDb.Execute "INSERT INTO tblUserLog (TimeStamp, Username) VALUES " & _
                "(#" & Now() & "#,'" & cboUser.Value & "');"
            Me.Visible = False
        Else
            MsgBox "Þetta lykilorð passar ekki!", vbOKOnly
            Me.txtPassword = Null
            Me.txtPassword.SetFocus
        End If
    End If
End Sub

 

[Steindi]

Hi again, thank you for your help.

I've tried this and get this error.

"
I get a run-time error 3292

Syntax error in field definition."

 

[RainLover]

Hi again, thank you for your help.

I've tried this and get this error.

"
I get a run-time error 3292

Syntax error in field definition."

Can you give more detail. Does any of the code change colour.

Have you been able to compile the code.

Shooting from the hip in order to get a better idea of the problem.

 

[RainLover]

April15Hater

It is good to see Code that is presented correctly.

Spacing, Indenting etc.

 

[April15Hater]

April15Hater

It is good to see Code that is presented correctly.

Spacing, Indenting etc.

Thanks! I'm just OCD like that. Reddick-Gray is where it's at!

Steindi:

That's what I get for trying to be all fancy with the CREATE TABLE query....

try this:

Private Sub txtPassword_AfterUpdate()
    'Check that EE is selected
    If IsNull(Me.cboUser) Then
        MsgBox "Veldu nafn af listanum", vbCritical
        Me.cboUser.SetFocus
    Else
 
        'Check for correct password
        If Me.txtPassword = Me.cboUser.Column(2) Then
 
            'Check if password needs to be reset
            If Me.cboUser.Column(3) = True Then
                DoCmd.OpenForm "frm_PasswordChange", , , "[UserID] = " & Me.cboUser
            End If
            DoCmd.OpenForm "Adal_form_1_SE"
 
            'Test if Userlog table exists.  If not, create it.
            If DCount("[Name]", "MSysObjects", "(Type = 1 OR Type = 6) AND [Name] = " & _
                "'tblUserLog'") < 1 Then
                CurrentDb.Execute _
                    "CREATE TABLE tblUserLog " & _
                    "(tblUserLogPK Integer PRIMARY KEY AUTOINCREMENT, " & _ 
                        TimeStamp DATETIME, Username CHAR);"
            End If
 
            'Append Username and timestamp to tblUserLog
            CurrentDb.Execute "INSERT INTO tblUserLog (TimeStamp, Username) VALUES " & _
                "(#" & Now() & "#,'" & cboUser.Value & "');"
            Me.Visible = False
        Else
            MsgBox "Þetta lykilorð passar ekki!", vbOKOnly
            Me.txtPassword = Null
            Me.txtPassword.SetFocus
        End If
    End If
End Sub

 

[Steindi]

Ok, now I get some of the code red and I get a Syntax error.

Code:
Private Sub txtPassword_AfterUpdate()
'Check that EE is selected
If IsNull(Me.cboUser) Then
MsgBox "Veldu nafn af listanum", vbCritical
Me.cboUser.SetFocus
Else

'Check for correct password
If Me.txtPassword = Me.cboUser.Column(2) Then

'Check if password needs to be reset
If Me.cboUser.Column(3) = True Then
DoCmd.OpenForm "frm_PasswordChange", , , "[UserID] = " & Me.cboUser
End If
DoCmd.OpenForm "Adal_form_1_SE"

'Test if Userlog table exists. If not, create it.
If DCount("[Name]", "MSysObjects", "(Type = 1 OR Type = 6) AND [Name] = " & _
"'tblUserLog'") < 1 Then
CurrentDb.Execute _
"CREATE TABLE tblUserLog " & _
"(tblUserLogPK Integer PRIMARY KEY AUTOINCREMENT, " & _
TimeStamp DATETIME, Username CHAR);"
End If

'Append Username and timestamp to tblUserLog
CurrentDb.Execute "INSERT INTO tblUserLog (TimeStamp, Username) VALUES " & _
"(#" & Now() & "#,'" & cboUser.Value & "');"
Me.Visible = False
Else
MsgBox "Þetta lykilorð passar ekki!", vbOKOnly
Me.txtPassword = Null
Me.txtPassword.SetFocus
End If
End If
End Sub

 

[Steindi]

How do you post code the correct way??

I've marked the red part with bold.

 

[April15Hater]

                CurrentDb.Execute _
                    "CREATE TABLE tblUserLog " & _
                    "(tblUserLogPK Integer PRIMARY KEY AUTOINCREMENT, " & _ 
                        "TimeStamp DATETIME, Username CHAR);"

 

[Steindi]

Nope, still get an error.

Run time error 3290

Syntax error in create table statement.

The same part of the code is highlighted.