FormQuery

[Sheba]

I have this query as the row source for the combo box on my Login form:

SELECT tblUser.UserID, LastName FirstName AS FullName, LoginID, strPassword, AccessLevelID, PWReset, tblAccess.CategoryID
FROM tblUser, tblAccess
WHERE tblUser.UserID = tblAccess.UserID

WHY is it asking me to Enter Parameter Value I want it to give me everything based on all entries in both tables. Could someone please advise on what I should do here?

[June7]

Need to concatenate fields and a JOIN clause should be better than WHERE:

SELECT tblUser.UserID, LastName & ", " & FirstName AS FullName, LoginID, strPassword, AccessLevelID, PWReset, tblAccess.CategoryID
FROM tblUser INNER JOIN tblAccess ON tblUser.UserID = tblAccess.UserID;

Why is this two tables? Can users have more than one category?

[Sheba]

Hi June7. Thanks for your response. Yes, two users have more than one category. I have the two tables tblUser - contains among other things, user's loginId, password and permission level and then tblAccess that shows which user is authourised to access which form. In tblAccess, some userIDs occur more than once.

[Sheba]

run time error 3464 "Data Type mismatch"

Could someone please tell me why I'm having this error message? I have below code in the OnOpen event of my form. It is supposed to check the tblAccess table which contains categoryID and user IDs assigned to the categories. It should check the category associated with the current user and match it with the categoryID of the form. Based on this, Grant or Deny access.

However, my login form (frmLogin) has a combo box which has a query as its row source . This query uses tblUser, which has userID, Accesslevel, FName, LName but not categoryID .

Dim CatID As Long
Dim lUser As Long

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

CatID = DLookup("CategoryID", "tblAccess", "UserID='" & lUser & "'")

Select Case CatID

Case 450

With Me

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

End With

Case Else

MsgBox "You are not authorized to view this form"

Cancel = True

End Select

Your help is greatly appreciated

[June7]

If UserID is a number data type, remove the apostrophe delimiters.

If user can have more than one category, this code will likely fail because the DLookup will return the CategoryID from the first record that matches the UserID. If the user has this form's category in another record, it will never retrieve. The DLookup needs to use the CategoryID in the search criteria.

Also, a no match will return Null. The CatID variable is declared as Long which cannot hold null. Only Variant can hold null.

If IsNull(DLookup("UserID", "tblAccess", "CategoryID=450")) Then
MsgBox ...
Cancel = True
Else
....
End If

[Sheba]

Thank you so much June7. Excuse my ignorance but I have a question

1) you're correct. I removed the delimiters but it denied me access. I switched UserID and CategoryID and have the code looking like this but it looks wrong. The end result of the search should be the categoryID not the userID...

I'm getting error 424 "object required"

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

[June7]

Why do you need to set CatID variable? Don't you just need to know if the user is associated with the categoryID that the form is associated with? That's a true or false question. My suggested revised code provides that answer. It replaces the Select Case and doesn't use any VBA declared variables.

[Sheba]

I have an existing SELECT Case statement and need to adapt this CatID to it. I'm currently getting an error 424 on the highlighted line. Could you advise on what I'm doing wrong please?

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

SELECT Case CatID

Case 100
...
Case 188
...
Case Else

If IsNull(CatID) Then
Msg....
EndIf

End SELECT

[Sheba]

weird only just seen your response. The Administrator has a CatID that also accesses each form in addition to the User's CatID. In this case let's just say 100. So CatID 100 and 188 will access the form...

After confirming the CatID, the permission level (read, DataEntrant, Adminstrator) is then determined. This is determined by code in each Case. This was how I had it and it was working until my user table was changed and users now have access to more forms. This meant the CategoryID occurs once but userID occurs more than once. I had to create a separate table for this and need to tweak the code...

[June7]

Did you understand my comment about what happens if a user is associated with more than one CategoryID? The original DLookup will always return the same CategoryID for a given UserID.

[Sheba]

but I also need to reference the Developer's catID which is the same for EVERY form.
make updates and I need to on the table with Admin's catID.
more than one entry. On the table I gave him a catID which is for "All" categories

[June7]

What are the rules for a user to have permission for a form?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Sheba]

hi June7, yes I did. In the category table, I have the developer's catID "100" with description, "All Categories". Although he has permission levels different from the DataEntrant, he might need to access the form as well, in order to update the source code and I want this catID referenced in the code. This is why I'd used the Case statement previously.

How do I reference both CatIDs in this code? and then check for permission level (Data, Reader, Dev, etc) without having too many embedded IF statements?

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

MsgBox ...

Cancel = True

Else

If userlevel = 3 then
'developer access code
EndIf

If userlevel = 4 then
'data entry access code

endIf

EndIf

[June7]

Sorry, I am totally lost - CategoryID, permission level, userlevel, userID. I don't understand the rules for a user to have access to a form. If I don't understand the rules, I can't advise how to program them.

[Sheba]

sorry my second last post was uploaded by mistake. As for your question:

When a user tries to logs in to the application and tries to access a form from the switchboard, the application should check if on the category table, his user ID is assigned to the catID for the form. If the answer is "Yes", then what are the restrictions applied to him/her? Data entrant, Reader, Developer? I hope this answers your question?