FormQuery

[Sheba]

I have two tables. One is the tblUser which has UserID, LastName, FirstName, LoginID, Password, PWReset and then I have another table tblAccess which had LName, FName, CategoryID and UserID.



In tblAccess, some users occur many times because they have been assigned to more than one catID

[June7]

Each form is associated with one CategoryID? So the form in the example is associated with CategoryID 450? Don't need the other CategoryID.

If IsNull(DLookup("UserID", "tblAccess", "CategoryID=450")) Then
    MsgBox "You are not authorized to view this form"
    Cancel = True
Else
    Select Case Me!userlevel
        Case 3
        'set these permissions
        Case 4
        'set these permissions
    End Select
End If

[ssanfu]

To answer your question:

Could you advise on what I'm doing wrong please?

CatID = DLookup("UserID", "tblAccess", "CategoryID=" & tblAccess.[CategoryID] & "")

You CANNOT reference a field in a table like this. It doesn't make sense anyway.
You are saying "Give me the userid from tblAccess where the CategoryID is equal to the CategoryID in tblAccess". Whaaaat?

This is what you wrote in post#4.

lUser = Forms!frmLogin!cmbUser.Column(0)
CatID = DLookup("CategoryID", "tblAccess", "UserID='" & lUser & "'")  

This makes sense. (is correct). Do you see/understand the difference?

[Sheba]

In the Category table, each form is associated with an ID, yes but the Developer has an ID assigned to him, which will be referenced in the code.
The developer might need to access the form in order to update its design and I want an avenue for him to do that. Please let me know if this helps.

[Sheba]

hey Steve, I know, I know, I'm disappointed too Also, too much coffee and too many long hours.
Yes I see the difference but the doggone code didn't work. It was asking for a missing object or something. confounding!

[June7]

Lost again.

You know what CategoryID to use as criteria because you know the form. FormA is CategoryID 450. Now does the user have permission to use this form/category? Lookup the UserID - if there is a record associating it with CategoryID then they have permission to use this form. What level of permission? That's what my suggested code programs.

Provide pictures or the database, otherwise we will keep going in circles.

[Sheba]

my question is not about the "user" but about the Developer. He has to access ALL forms. If I assign every catID to him, this would mean multiple entries for each form (i.e one entry for him plus anther for the dataentrant). To prevent this, I gave him ID 100. Then in the code, the application should look for the correct catID (in this case 450 or 100).

I was wondering how to Grant form access to the developer, in your code.

HTH

[Sheba]

just a FYI, I don't have a table where the forms are connected to the Categories. I only have the category table and then in the code (for each form) I specify that a user should have a specific ID (on the category table) before access is granted.

[Sheba]

just a FYI, I don't have a table where the forms are connected to the Categories. I only have the category table and then in the code (for each form) I specify that a user should have a specific ID (on the category table) before access is granted.

[June7]

Developer is another type of user - the highest-level (superuser). How should code know this is the developer? Isn't the developer listed in tblUser? Would permission field identify that record as Developer? Already know the UserID and already know the form's CategoryID. If the user is Developer then there are no restrictions. Only run code to set restrictions if user is not Developer.

If DLookup("Permission", "tblUser", "UserID='" & Me.lUser & "'") <> "Developer" Then
If IsNull(DLookup("UserID", "tblAccess", "CategoryID=450")) Then

As an example, in my db there are 3 types of users - Admin, Manager, Staff. At this time there is only one Admin - me (the developer). These types are assigned to users in Users table. Then code:

If strPermissions = "staff" Then
    Me.cbxUser.Visible = False
    Me.btnAdmin.Visible = False
    Me.btnCost.Visible = False
End If
If strPermissions <> "admin" Then Call SetAccessXCloseButton(False)

[Sheba]

Hi June,

I'm getting error "Method or data member not found" although I have amended my code to look like this:

lUser = Forms!frmLogin!cmbUser.Column(0)

If DLookup("AccessLevelID", "tblAccess", "UserID='" & Me.lUser & "'") <> 1 Then

If IsNull(DLookup("UserID", "tblAccess", "CategoryID=450")) Then

MsgBox "You are not authorized to view this form"

Cancel = True

Else

With Me

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

End With

End If

End If

In my login query, userID is the first column hence column(0).

[Sheba]

hey June I fixed the error! shoulda been just lUser not Me.lUser

now to test the various catIDs and userIDs.

[Sheba]

I celebrated too soon June7. There are no compiler errors but users are able to open category forms that are not for them... I need for them to only open their own form(s). How can I do this?

[June7]

What do you mean by 'their own form(s)' - why would users have their 'own' forms? Your code only shows attempting to set properties of a form, not open different forms.

Step debug. Follow the code as it executes, one line at a time. See where it deviates from expected behavior and then fix and debug again. Are variables getting properly set? Are the DLookups pulling correct values?

[Sheba]

hi June,

I hope this clears it up a bit better...

• Each category is represented by a form.

• On the table tblAccess, each catID has a userID associated with it.

• Only the user with userID associated with a particular catID in tblAccess should be granted access and can open the form (i.e. excluding the developer).

• So like I've said, for each form, only users with a specific catID associated with them should be allowed to access the form

• if the current user's ID is not associated with a catID in tblAccess, he shouldn't be able to open the form, he's clicking on