Log On - Match Password from table against entry into log on form

Question

I have a table called PMO Staff - fields are Password and PMName

My combo box is set to display the names in this list.  The properties look like this

My password is an unbound text box where the user enters their password.

When they click on log on, it needs to check that the password they typed into the unbound text box matches the password in the PMO Staff table.

Here is my code

 'Check value of password in PMO Staff table to see if this matches value chosen in combo box

    If Me.Password.Value = DLookup("Password", "PMO Staff", "[Password]=" & Me.Password.Value) Then

        Password = Me.Password.Value

        'Close logon form and Open Search Form
       
        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "Search Form"

    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.Password.SetFocus
    End If

When I hit log on I get the following error

E522F is the password for the particular user.

Any thoughts on how to fix this?

Answer 1

"When I hit log on I get the following error"

Which error?

If you debug the error, which line is flagged as be the cause of the error (highlighted in yellow)?

 

Also, I wouldn't normally simply compare a password blindly against a table in such a manner.  I'd have a username entered and use the username to get a password to validate with that being entered.

 

You may also like to look over http://www.devhut.net/2012/12/21/ms-access-login-form/ for sample login forms.

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

As the values are text you need to include literal quote characters, each represented by a contiguous pair of quote characters, as delimiters:

If Me.Password = DLookup("Password", "PMO Staff", "Password=""" & Me.Password & """") Then

However, this would enable any user's password to be entered, rather than the current user's password.  Assuming that the combo box in which the user selects their name has a numeric EmployeeID as its hidden bound column (personal names should never be used as keys), i.e. its RowSource is something like this:

SELECT EmployeeID, LastName & ", " & FirstName
FROM [PMO Staff]
ORDER BY LastName, FirstName;

The code would be along these lines:

If Me.Password = DLookup("Password", "PMO Staff", "EmployeeID = " & Me.cboEmployee) Then

Where cboEmployee is the name of the combo box.  Note that it's not necessary to specify the Value property as this is the default property of a control, and quote delimiters are not required in the case of a numeric value.

---

Ken Sheridan, Stafford, England

Answer 3

Thanks Ken - that worked.  My additional question is this - I would like to take the PMName (or the PMID) that logged in and append it to a log in tracing table - so the person logged in and the date/time logged in gets captured in this table.  My table has three basic fields User, LogIn, LogOut.  Is there an easy way to append this data to the log in tracking table before I call for the command to open the search form?

The end goal is to track changes that are being made.  There is a last updated table that when a record is changed it appends the owner of the record and now() to it.  The problem is that someone other than the owner of the record can and will make changes.  So I would like it to look at who is logged in - add this as the person making the change.

Does this make sense?

Answer 4

What you are wanting is to create is commonly refer to as an Audit Log.  This can be very simple or very complex depending on the level of detail needed.  You can find many examples online to help you get going in the right direction, such as:

http://allenbrowne.com/AppAudit.html

http://www.utteraccess.com/forum/index.php?showtopic=1576962&hl=audit

http://www.utteraccess.com/forum/index.php?showtopic=220783&hl=audit

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 5

Take a look at ChangedRecordDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly. 

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates how to time-stamp or log changes to a record, and how to write data to an audit log table when a change occurs.  In my demo the current user's Windows log-in name is used by calling a Widows API function, rather than a user name used when the user logs in to the database.  You can change my code to use the latter quite simply, either by hiding the log-in form by setting its Visible property to False in code rather than closing it, and then referencing the control in the form to get the user name, or you can assign the current user name to a public variable in a standard module when closing the log-in form.  You'd then get the user name from the variable whenever required.

The principle way in which my demo differs from many other examples of this sort of thing, however, is that it detects actual changes to the values of the data, rather than merely detecting updates.  A record can be updated without any changes being made to the values of its data.  My file detects changes by means of the RecordHasChanged function in the basChangedRecord module.

---

Ken Sheridan, Stafford, England

Answer 6

Thank you for sharing your demos with me.  I am still struggling and hope this is an easy fix.  Right now all I want is for the value selected into my unbound combo box to be appended into my LogList table along with the current date/time

I highlighted the docmd I am using in an attempt to get the UserName selection to insert into my table.  When I click on save - I get a parameter box asking for Me.UserName.  Now() is being appended to the table but the User remains blank. 

Here is the code I am using onClick save:

Private Sub cmdSave_Click()

'Check to see if data is entered into the UserName combo box

    If IsNull(Me.UserName) Or Me.UserName = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.UserName.SetFocus
         'LogIn_User = Me.UserName
        Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Me.Password) Or Me.Password = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.Password.SetFocus
        Exit Sub
    End If

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

    If Me.Password = DLookup("Password", "PMO Staff", "Password=""" & Me.Password & """") Then

        Password = Me.Password.Value

           
        'Close logon form and open splash screen

        DoCmd.Close acForm, "frmLogon", acSaveNo
       
        DoCmd.RunSQL " INSERT INTO tblLogList ([User],[Login]) VALUES (Me.UserName, Now());"
       
        DoCmd.OpenForm "Search Form"

    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.Password.SetFocus
    End If

Answer 7

Hi seebert,

For your issue, you need to add “’” between Me.UserName, something like below:

DoCmd.RunSQL " INSERT INTO tblLogList ([User],[Login]) VALUES ( '" & Me.UserName & "', Now());"

Since your original issue related with logon form has been resolved, if the log function still not work, I would suggest you mark the helpful reply as answer to close current thread, and then post a new thread for this new issue.

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

Thanks Edward -

Your fix worked!  I will marked this as answered as suggested.

Tracy