forms - Accesslevel

[Sheba]

I have AccesslevelID and CategoryID as foreign keys in my user table.

Administrator has Level 1
Data Entry has level 4
Reader has level 3
Each form has a unique category ID

On my login form, my combobox User ID has it's row source, as this query:
SELECT userID, LName & "," & FName AS FullName, Password, AccessLevelID, CategoryID


FROM tblUser
ORDER BY LName, FName;


I don't want the DataEntry user to be able to delete records. But the code below is allowing them to. Could someone advise what is wrong with it please

If Forms!frmLogin!cmbUser.Column(3) = 4 Then
    If Forms!frmLogin!cmbUser.Column(4) = "OB01" Then
        With Me
            .DataEntry = True
            .AllowEdits = True
            .AllowDeletions = False
            .AllowAdditions = True
            .AllowFilters = True
        End With
    Else
        MsgBox "You are not authorized to view this form"
        Cancel = True
        DoCmd.Close acForm, "ObstetricsForm", acSaveNo
    End If
Else
    If Forms!frmLogin!cmbUser.Column(3) = "All" Then
        If Forms!frmLogin!cmbUser.Column(4) = 1 Then
            With Me
                .DataEntry = True
                .AllowEdits = True
                .AllowDeletions = True
                .AllowAdditions = True
                .AllowFilters = True
            End With
        Else
            With Me
                .DataEntry = False
                .AllowEdits = True
                .AllowDeletions = False
                .AllowAdditions = False
                .AllowFilters = True
            End With
        End If
    End If
End If

[June7]

Use code tags when posting code and the indentation will be preserved and make it easier to read. I edited your post to show that.

Have you step debugged? Review link at bottom of my post for debugging guidelines.

[ssanfu]

I have AccesslevelID and CategoryID as foreign keys in my user table.

So you should have a table "AccessLevels"? What is it structure? (field names and types)

Cutting out a lot of code, you have:

If Forms!frmLogin!cmbUser.Column(3) = 4 Then  '<<= this is a NUMBER
    If Forms!frmLogin!cmbUser.Column(4) = "OB01" Then  '<<= this is TEXT
.
.
    End If
Else
    If Forms!frmLogin!cmbUser.Column(3) = "All" Then  '<<= this is TEXT
        If Forms!frmLogin!cmbUser.Column(4) = 1 Then   '<<= this is  a NUMBER
.
.
.

What are the field types in table "tblUser" for the fields "AccessLevelID" and "CategoryID"??

[Sheba]

Hi June7 and Steve, thanks for your responses. My AccessLevel has only two columns ID (autonumber datatype) and level(text).
The category table also has two columns ID(text datatype) and Name(text)

In the User table AccessLevel has Number data type and Category has Text data type

[Sheba]

Forgot to add: Thanks June7 for editing my post for me. Certainly looks better this way and yes, I'd step debugged it yesterday and today again.

[burrina]

A couple of things worth mentioning. You should NOT have a combo for the user, it gives anyone trying to crack your db half of the information.
It would also be easier to use a yes/no field for the security level.

HTH

[Sheba]

thanks burrina. you're correct that is why I took out the "Remember Password" box. For now, it is being tested but I'll work on changing the combo box to a text box

[ssanfu]

So you see that both tests here should be numeric:

If Forms!frmLogin!cmbUser.Column(3) = 4 Then  '<<= this is a NUMBER
Else
    If Forms!frmLogin!cmbUser.Column(3) = "All" Then  '<<= this is TEXT

and both tests here should be Text??

    If Forms!frmLogin!cmbUser.Column(4) = "OB01" Then  '<<= this is TEXT

        If Forms!frmLogin!cmbUser.Column(4) = 1 Then   '<<= this is  a NUMBER

If there is an entry in the tblCategory.CategoryID field that is a 1, you could use

If Forms!frmLogin!cmbUser.Column(4) = "1" Then   '<<= this is  a NUMBER

[Sheba]

hey Steve, sorry I'm a bit confused here. Currently there is nothing in the Category table which has a "1". I guess from you've said, changing the category ID (field and data type) to 'Number' is the way to go?

Basically the IF statements should have the same data type for their criteria?

[June7]

Steve is pointing out inconsistency in your code.

In one place Column(3) is compared to a text parameter - "All".
In another place Column(3) is compared to a number parameter - 4.

In one place Column(4) is compared to a text parameter - "OB01".
In another place Column(4) is compared to a number parameter - 1.

[Sheba]

hi June7. I hadn't picked up on that. Funny, in my program I have the correct columns referenced (as per below). I think after pasting the code yesterday, I must have made that change. Although the correct column references exist on my form, the code is still allowing the user to delete the record

If Forms!frmLogin!cmbUser.Column(3) = 4 Then

If Forms!frmLogin!cmbUser.Column(4) = "OB01" Then

With Me

.DataEntry = True
.AllowEdits = True
.AllowDeletions = False
.AllowAdditions = True
.AllowFilters = True

End With

Else
MsgBox "You are not authorized to view this form"

Cancel = True

DoCmd.Close acForm, "ObstetricsForm", acSaveNo

End If

Else
If Forms!frmLogin!cmbUser.Column(4) = "All" Then

If Forms!frmLogin!cmbUser.Column(3)= 1 Then

With Me

.DataEntry = True
.AllowEdits = True
.AllowDeletions = True
.AllowAdditions = True
.AllowFilters = True

End With
..

End If

End If

[June7]

Step debug, follow the code as it executes. Determine why it deviates from expected behavior. Fix code and repeat.

[ssanfu]

As June said, write down what conditions you require, then single step through the code watching the variables in the code.

I have two monitors - the form in the left and the code in the right monitor. So easy

[Sheba]

Thanks June7 and Steve. In my code, both references to column(3) have numeric data type while references to column(4) have text data type. I Step debugged did but it does not highlight any code for correction hence my confoundment

[burrina]

As has been suggested, you have to refer to matching data types, numbers to numbers and text to text. Is this the case?
References start at 0 for the Column count!