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

  • 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?

    Wednesday, November 15, 2017 1:37 PM

Answers

  • 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 MSDNFSF@microsoft.com.

    • Marked as answer by seebert Tuesday, November 21, 2017 11:25 AM
    Monday, November 20, 2017 8:07 AM

All replies

  • "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


    Wednesday, November 15, 2017 3:06 PM
  • 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

    Wednesday, November 15, 2017 3:20 PM
  • 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?

    Wednesday, November 15, 2017 5:09 PM
  • 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


    Wednesday, November 15, 2017 5:13 PM
  • Take a look at ChangedRecordDemo.zip in my public databases folder at:

    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

    Wednesday, November 15, 2017 10:45 PM
  • 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

    Friday, November 17, 2017 1:00 PM
  • 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 MSDNFSF@microsoft.com.

    • Marked as answer by seebert Tuesday, November 21, 2017 11:25 AM
    Monday, November 20, 2017 8:07 AM
  • Thanks Edward -

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

    Tracy

    • Marked as answer by seebert Tuesday, November 21, 2017 11:25 AM
    • Unmarked as answer by seebert Tuesday, November 21, 2017 11:25 AM
    Tuesday, November 21, 2017 11:25 AM