Results 1 to 15 of 15
  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
    7

    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
    401
    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
    7

    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:	3 
Size:	195.0 KB 
ID:	17384

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    401
    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
    7

    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.

  9. #9
    Join Date
    Jun 2017
    Posts
    7

    How to automatically fill-in the username in Login Form into field of another form

    Quote Originally Posted by radniw518 View Post
    Dear Poppa Smurf,

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

    Again your help is highly appreciated.
    Dear Poppa Smurf. Thank you again for your previous help. Just a follow-up, how can I have the current username in the login form automatically inputted in the field of the 2nd form that opens after successful login. Something like "Welcome: John Adams" - if John Adams is successfully login. You may refer to my previous code which you also assisted that successfully assigns forms based on username.

    thanks

  10. #10
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    401
    On your second form create an unbound control (field) and name it EmpName

    Then add the code shown in bold, you need to use Dlookup to get the Employees Name from tbluser whilst the login screen is open

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

  11. #11
    Join Date
    Jun 2017
    Posts
    7

    Login opens different form for different users

    Thanks for your prompt reply. Sorry, I am a newbie to Access and really appreciate your help. I have now created an unbound control named EmpName on my 2nd form (frm_incoming main)

    As for your advise "to add the code shown in bold, you need to use Dlookup to get the Employees Name from tbluser whilst the login screen is open

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

    -where will i add this code in the login form or in the 2nd form (frm_incoming main)?Do I have to put it in any event?

    I have these as the code for the login button that opens frm_incoming main (form2) as you had previously advised:

    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
    'TempVars.Add "User"
    'Close logon form and open splash screen

    DoCmd.OpenForm DLookup("EmpForms", "tblEmployees", "[EmpID]=" & Me.cboEmployee.Value)
    DoCmd.Close acForm, "frmLogin1"

    '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 had also attached the screenshots of the login form and the frm_incoming main (2nd form) where I want the welcome screen with the current user to be displayed after log in as Admin.Click image for larger version. 

Name:	form 2 after login.jpg 
Views:	4 
Size:	96.0 KB 
ID:	17398

    Click image for larger version. 

Name:	login form.jpg 
Views:	2 
Size:	41.5 KB 
ID:	17399

    Hoping for your usual kind assistance. God Bless...

  12. #12
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    401
    Sorry I should have explained where to add the code.

    In your Login form
    At this section of code
    'Close logon form and open splash screen
    DoCmd.OpenForm DLookup("EmpForms", "tblEmployees", "[EmpID]=" & Me.cboEmployee.Value)
    DoCmd.Close acForm, "frmLogin1"

    You open the relevant form for the user then you close the login form.
    Now before you close the login form you need to populate the Unbound field with the Employee's name. This is done by using another Dlookup to obtain the Employee's Name as shown in bold

    'Close logon form and open splash screen
    DoCmd.OpenForm DLookup("EmpForms", "tblEmployees", "[EmpID]=" & Me.cboEmployee.Value)
    Me!EmpName ="Welcome " & DLookup("EmpName", "tblEmployees", "[EmpID]=" & Me.cboEmployee.Value)
    DoCmd.Close acForm, "frmLogin1"

    You will need to add an unbound field for each form listed in tblEmployees as not all users will be opening the same form.

  13. #13
    Join Date
    Jun 2017
    Posts
    7
    Hi Poppa Smurf.

    Thanks again for your help. I tried your suggestions but I am getting "Run-time error '2465' ...can't find the field 'EmpName' referred to in your expression".

    Is it correct that created an "unbound" field named "EmpName" as suggested using a text box in my 2nd form?

    here are some of the screenshots of the error and other configurations. Hope you can advise me again on this.

    thanks

    Click image for larger version. 

Name:	form 2  runtime error.jpg 
Views:	3 
Size:	78.5 KB 
ID:	17400

    Click image for larger version. 

Name:	fields of tblEmployees.jpg 
Views:	4 
Size:	62.0 KB 
ID:	17401

    Click image for larger version. 

Name:	form 2 after login error.jpg 
Views:	3 
Size:	77.4 KB 
ID:	17402Click image for larger version. 

Name:	form 2 after login error debug.jpg 
Views:	2 
Size:	169.9 KB 
ID:	17403
    Click image for larger version. 

Name:	fields of tblEmployees.jpg 
Views:	4 
Size:	62.0 KB 
ID:	17401

  14. #14
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    401
    Post a zipped copy of your database to the forum and I will have a look at it.

  15. #15
    Join Date
    Jun 2017
    Posts
    7
    Hi Poppa Smurf,

    thank you again for your assistance. As requested, I tried to attached a zipped file of the database but it seems to exceed the limit of attaching thru the thread. I emailed the zip file to allanmurphy47@gmail.com.


    thanks

Posting Permissions

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