Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    13

    Unanswered: Login opens different form for different users

    I have a DB set up with a login form (2 unbound fields for username and password) that is using the following code behind a command button to open a single form:

    Private Sub cmdLogin_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 tblUsers to see if this
    'matches value chosen in combo box

    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblUsers", _
    "[lngEmpID]=" & Me.cboEmployee.Value) Then


    'Close logon form and open user form

    DoCmd.Close acForm, "frmtblEmployees"
    DoCmd.OpenForm "frmAdminAsst"


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

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
    MsgBox "You do not have access to this database.Please contact admin.", _
    vbCritical, "Restricted Access!"
    Application.Quit
    End If

    However, I would like to open different forms for different users. I have a table of my users, which has a field with the name of the form I want that user to have access to, so I was wanting the code to lookup that form name to know which form to open. ??? Can anyone help with this? Also, I know very minimal VBA, and even less about Access security. Thanks so much!!

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    This code is independent from any Access security. Based on my understanding of your description, I would make the following change:

    Code:
    DoCmd.OpenForm "frmAdminAsst"
    Change the above to:

    Code:
    DoCmd.OpenForm DLookup("strForm", "tblUsers", "[lngEmpID]=" & Me.cboEmployee.Value)
    Change strForm to the name of the field that has the form name in it. This assumes the form name is in the tblUsers table.

  3. #3
    Join Date
    Jul 2009
    Posts
    13

    Talking yipee!!

    you just made me so happy! why couldn't i figure that out???? it works perfectly...thank you!

  4. #4
    Join Date
    Jun 2017
    Posts
    3

    Inquiry on assigning users specific forms in access

    Quote Originally Posted by DCKunkle View Post
    This code is independent from any Access security. Based on my understanding of your description, I would make the following change:

    Code:
    DoCmd.OpenForm "frmAdminAsst"
    Change the above to:

    Code:
    DoCmd.OpenForm DLookup("strForm", "tblUsers", "[lngEmpID]=" & Me.cboEmployee.Value)
    Change strForm to the name of the field that has the form name in it. This assumes the form name is in the tblUsers table.
    " i have followed your instruction, but I am getting an Runtime Error 2467 - The expression you entered refers to an object that is closed nor doesn't exist."

    I had a table called tblEmployees with a field named EmpForms (contains the forms for each user).

    Hope you can advise me on this

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    394
    Change your coding from
    DoCmd.Close acForm, "frmtblEmployees"
    DoCmd.OpenForm DLookup("strForm", "tblUsers", "[lngEmpID]=" & Me.cboEmployee.Value)

    to

    DoCmd.OpenForm DLookup("strForm", "tblUsers", "[lngEmpID]=" & Me.cboEmployee.Value)
    DoCmd.Close acForm, "frmtblEmployees"

    In your original code you have closed "frmtblEmployees" then you are trying to do a Dlookup on the employee value when the form is closed. You need to open form returned by the Dlookup then close "frmtblEmployees"

  6. #6
    Join Date
    Jun 2017
    Posts
    3

    Assigning users specific forms upon login in Access

    Dear Poppa Smurf,

    I had changed my code as advised, but still there is an error when I run it. For your reference, I had pasted my existing code that works but only opens same form for all users:

    Private Sub cmdLogin_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("EmpPassword", "tblEmployees", "[EmpID]=" & Me.cboEmployee.Value) Then

    MyEmpID = Me.cboEmployee.Value

    'Close logon form and open splash screen

    DoCmd.Close acForm, "frmLogin1", acSaveNo
    DoCmd.OpenForm "frm_incoming main"

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

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
    MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
    Application.Quit
    End If

    End Sub

    ************************************************** ************************************************** ****************************
    I have a table named tblEmployees with the following fields:

    EmpID
    EmpCode
    EmpName
    EmpPassword
    EmpRole
    EmpForms - this are the forms that specific user will open upon login

    also in your suggested code below:

    DoCmd.OpenForm DLookup("strForm", "tblUsers", "[lngEmpID]=" & Me.cboEmployee.Value)
    DoCmd.Close acForm, "frmtblEmployees"

    What is "strForm"? and "lngEmpID"? Are these variables? where will I get these because I dont have these in my tables? Sorry I am new to MS Access and trying to learn and develop a simple database...

    thank you.

    Click image for larger version. 

Name:	screenshot of error.jpg 
Views:	1 
Size:	195.0 KB 
ID:	17384

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    394
    In your line of code DoCmd.OpenForm DLookup("strForm", "tblUsers", "[lngEmpID]=" & Me.cboEmployee.Value) you are using tblUsers. When the Dlookup is activated it is looking for the fields as specified in the Dlookup that do not exist in table Users that is why you are getting the error. You should be using tblEmployees, as this table stores the form that a specific user will open upon login.
    In your Dlookup replace "strform" with "EmpForms" , [lngEmpID] with [EmpID] and tblUsers with tblEmployees

  8. #8
    Join Date
    Jun 2017
    Posts
    3

    Thumbs up Login opens different form for different users

    Dear Poppa Smurf,

    Thank you very much for the advice. I did as suggested and it worked.....

    Again your help is highly appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •