Creating a User Log from Login Form

[thinair421]

Hey everyone,

I currently have a login form. Consists of a username combo box which gets the UserID and UserName from a table (call this the UserName table...the UserLog table is different!...see below). Below is a password form, which of course the user enters. Upon clicking the Login command button, a check is performed via the following code:

Private Sub OK_Click()
On Error GoTo Err_OK_Click
 'Check to see if data is entered into the UserName combo box
    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboEmployee.SetFocus
        Exit Sub
    End If
    'Check to see if data is entered into the password box
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box
    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
            "[lngEmpID]=" & Me.cboEmployee.Value) Then
        lngMyEmpID = Me.cboEmployee.Value
        'Close logon form and open splash screen
        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "SWITCH"
    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If
Exit_OK_Click:
    Exit Sub
Err_OK_Click:
    MsgBox Err.Description
    Resume Exit_OK_Click
 
End Sub

I would like to know how I would go about making a user log, which logs the date/time & and the username based off what is selected/entered into this form. I have attempted setting my control source for my username combo box to a field in my userlog table (to store the name), along with adding a hidden date box in the background of the login form (to track the date). Seems to work just fine...but not quite.

The UserLog table which should be storing this data has 3 fields:
1)LoginID - autonumber (primary key)
2)Date/Time - saves date and time
3)Username - saves username

It will make one entry (i.e. Autonumber 1), but any entry after that overwrites the previous (only overwrites the username)...essentially I only have one row of data that is constantly being updated/overwritten. Why does it only create one record. Thanks so much in advance.

 

[ghudson]

Use an append query to add a new record to the log table.

 

[thinair421]

Sorry about the delay on my reply hudson...

So I had actually never heard of an append query before (and I'm pretty bad with queries overall) so I read up a little more on how to create one. Only issue I have is which tables I am supposed to select upon creating the query, since as of now the logon form just checks the username/password entered against a table containing all of the username/passwords (thru the OnClick event on my Login button). The login form is has no record source, and all fields are unbound. So heres where the issue is:

1)Click on query button in the objects box
2)Create new query in design view

Then it asks me to select a table. Am I selecting the table that has all of my usernames/passwords stored in it, or a new table that I should have created. Thanks for all of the help, my apologies for the lack of intuition on this one.

 

[ghudson]

You can create the query based on the data in the unbound form and also fill in the blanks with variables or functions. Below is an append query I run each time a user opens the database to update a user log in table. It stores the users NT ID, Computer Name and the Date & Time. You just have to build the query and then run it from code like my example below.

    DoCmd.SetWarnings False
        DoCmd.RunSQL ("INSERT INTO tblUserLog ( NTID, ComputerName, [DateTime] ) SELECT DISTINCT fGetUserName() AS NTID, Environ('ComputerName') AS ComputerName, Now() AS [DateTime] FROM tblUserLog;")
    DoCmd.SetWarnings True

 

[thinair421]

So my VBA knowledge is very limited (non-existent), this is my first database, and I'm currently not running the database on an SQL server so I'm not sure the effect that will have on this...but here is what I can tell you.

The login form/table that I have created is based EXACTLY from this tutorial

http://www.databasedev.co.uk/login.html

Maybe after seeing that we can talk in terms of my actual table names/field values so that I can get a grasp of exactly how this is working. Would greatly appreciate it. Thanks a ton.

 

[ghudson]

I regret that I do not have the time to dig into this for you. Google will be your friend and well as searching this forum. I just googled http://www.google.com/search?hl=en&...to+build+query+with+form+values&start=10&sa=N for you and found these two links that should help lead you on your way.

http://office.microsoft.com/en-gb/a...ble-by-using-an-append-query-HA010076526.aspx
http://blog.springhouse.com/index.php/2008/06/26/using-form-value-to-run-a-query-or-report/

 

[thinair421]

Excellent, thanks a bunch man. Really appreciate all of the help. I'm actually toying around with Access' security features right now, trying to weight out my best option here.

 

[speakers_86]

Hudson's advice is if you want to store the COMPUTER'S login credentials in access. Looking at your vba code, it looks like you are adding your own security system other than what windows provides. So, replace this code:

DoCmd.Close acForm, "frmLogon", acSaveNo

with this

me.visible=false
docmd.runquery "your append query name"

The query only has to query the username from your login form, and =date(). It's pretty straight forward. I believe that queries cannot access columns in a combo box, so just create a hidden control that contains the user name on your login form. You can have the value be set to the correct username in the after update event of the combo box. It should look something like this:

TextBoxName.value=me.ComboBoxName.column(1)

I'm no expert, so don't try to copy and paste my code. I depend on intellisense to get my vba right.

 

[DCrake]

Take a look at this link abd se if this helps you.