Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: Need help filtering form based on Combo box

    Hi,

    I have created a custom Login Form.
    The user selects there name from a combo box and enters there password.
    The username and password are checked and if they are correct the user is directed to a form based on there clearance.(i.e. User level security)

    The problem...
    I am trying to create a "dashboard" where the user can only see their own information. I.e. there login in details, personnal info and there current clients(the clients are being shown in a sub-form).
    The problem I am having is that whenever I login with the user, the form opens and a new record is added to the user table.

    Is there anyway to stop this happening, what am I doing wrong?
    I have been searching through posts for ages but I cannot seem to find a solution.

    The code that is running on the "Login" button on the login form:

    Code:
    Private Sub cmdlogin_Click()
    Dim intanswer As Integer
    Dim intLogonAttempts As Integer
    Dim intSec As Recordset
    Dim openargs As Variant
    Dim Name As String
    
    Name = "[UserName]=" & Chr(34) & Me.cboEmployee & Chr(34)
    openargs = Me.cboEmployee.Value
    Set intSec = CurrentDb.OpenRecordset("tblUserDetails", dbOpenDynaset)
    intSec.FindFirst "[UserName]=" & Chr(34) & Me.cboEmployee & Chr(34)
    
    
    
        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
    
    
    
    If Me.txtPassword.Value = DLookup("Password", "tblUserDetails", _
    "[UserName]='" & Me.cboEmployee.Value & "'") Then
    
    
    Select Case intSec![SecurityClearance]
    
    Case 1
    DoCmd.Close acForm, "frmlogin", acSaveNo
    DoCmd.OpenForm "frmadmin"
    Forms!frmadmin.Filter = Name  'only show records belonging to logged in user
    Forms!frmadmin.FilterOn = True
    Forms!frmadmin!EmployeeID = Me.cboEmployee.Column(1)
    
    Case 3
    DoCmd.Close acForm, "frmlogin", acSaveNo
    DoCmd.OpenForm "frmadmin"
    DoCmd.OpenForm "frmpartner"
    
    Case 5
    DoCmd.Close acForm, "frmlogin", acSaveNo
    DoCmd.OpenForm "frmadmin"
    DoCmd.OpenForm "frmManager"
    
    End Select
    '''Error message if username and password do not match
    Else
    
    intanswer = MsgBox("You have entered an incorrect Password" & vbNewLine & "Do you wish to try again?", vbQuestion + vbYesNo, "Login Error")
    
        If intanswer = vbYes Then
    
    Me.txtPassword.SetFocus
    Else
    Application.Quit acQuitSaveAll
    
    End If
    
        End If
    intSec.Close
    Set intSec = Nothing
    
    
    End Sub
    Any help will be really appreciated, please feel free to ask any questions.
    I have attached the database, the form in question is "Loginform".

    The password for all the users is 12345.


    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by raka View Post
    The password for all the users is 12345.
    so why bother having a password at all?

    A couple overall comments:

    • You have a lot going on in your code and it would be easier to read and maintain if you split some of it out into another method. For instance, you could call a method that would validate the user and return an integer with their level.


    • The Case Statement inside of a nested if statement can get confusing and make code maintenance much more difficult. Try to restructure the logic to avoid this.


    • You set a value for Name at the start of your code but then continue to read off the combo box in later lines. If you are going to create a name variable, use it every time you need the underlying value


    • You set the value for intSec and name, which both rely on cboEmployee, before you validate that cboEmployee has a value.


    • You could use the nz function during your test, something like:
      Code:
      If (nz(cboEmployee,"") = "") Then


    • Why does the case statement only check 1,3,5? Are those the only allowable values in the table? You don't have an else clause in there to handle the rest


    • Since you always want to close frmLogin, move that statement out of the select.

    To answer your original question: Try using a simple database query to validate the user rather than opening a recordset. Or at least use dbOpenSnapshot instead of dbOpenDynaset. Dynaset is meant to be used for add/edit/delete, while Snapshot is read only.

    Steve

  3. #3
    Join Date
    Jul 2012
    Posts
    6

    so why bother having a password at all?
    I have set the password to 12345 at the moment while creating this, in the future passwords will be created by the user.

    You have a lot going on in your code and it would be easier to read and maintain if you split some of it out into another method. For instance, you could call a method that would validate the user and return an integer with their level.
    How would I go about creating such a method? I am fairly new to VBA and this is my first project involving the heavy use of VBA from the start.

    Do you know of any examples of user level security which you could post a link too?

    The Case Statement inside of a nested if statement can get confusing and make code maintenance much more difficult. Try to restructure the logic to avoid this.You set a value for Name at the start of your code but then continue to read off the combo box in later lines. If you are going to create a name variable, use it every time you need the underlying value
    You set the value for intSec and name, which both rely on cboEmployee, before you validate that cboEmployee has a value.
    You could use the nz function during your test, something like:
    Code:
    If (nz(cboEmployee,"") = "") Then
    Thanks for the advice/comment. I will look into these and correct my code and restructure it to make it more logical.

    Why does the case statement only check 1,3,5? Are those the only allowable values in the table? You don't have an else clause in there to handle the rest
    Again, the statement will be used to check 1-5 security clearance however I was only using 1,3 and 5 to test if my method worked. I didnt add in all the clearances as I saw a few bugs in the code and set about correcting them.

    To answer your original question: Try using a simple database query to validate the user rather than opening a recordset. Or at least use dbOpenSnapshot instead of dbOpenDynaset. Dynaset is meant to be used for add/edit/delete, while Snapshot is read only.
    Thanks for the advice, I'll try it and get back to you.

    Thank-you for the help and please do bear with me. I am new to VBA and as I mentioned before this is my first project which involves the heavy use of VBA.

    Regards

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by raka View Post
    How would I go about creating such a method? I am fairly new to VBA and this is my first project involving the heavy use of VBA from the start.
    Creating a method within a form is pretty straight forward. In the VBA code underlying the form just add something like this:

    Code:
    Private Function validateUser(userName As String, password As String) As Boolean
        'Do your validation code here, for example:
        
        If password = DLookup("Password", "tblUserDetails", "[UserName]='" & userName & "'") Then
            validateUser = True
        Else
            validateUser = False
        End If
        
    End Function
    The items inside the ( ) are the parameters that you pass to the function and the As Boolean portion is the result you pass back (using the name of the function)

    You can use a string or integer value if it fits your needs better.

    Then to call the function use something like this in your code:
    Code:
    If (validateUser(Me.cboEmployee.Value, Me.txtPassword.Value)) = True Then
        'Let them in
    Else
        'Kick them out
    End If
    If you want the function available to other forms then just move it into a module and change the private to public

    Steve

Posting Permissions

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