Need help filtering form based on Combo box

raka · #1 (**permalink**) 08-18-12, 21:57

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.

sps · #2 (**permalink**) 08-20-12, 10:07

Quote:

Originally Posted by raka

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.

raka · #3 (**permalink**) 08-21-12, 19:41

Quote:

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.

Quote:

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?

Quote:

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.

Quote:

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.

Quote:

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

sps · #4 (**permalink**) 08-22-12, 11:17

Quote:

Originally Posted by raka

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

Internet Services

Web Development

Digital Marketing

Consumer Tech