user Role

[alex_]

First of all, let me congratulate you for the excellent work you have been doing through this forum and apologize if my question may sound trivial to some, or has been answered before. What I am trying to do is to assign roles to the users of my db and programmatically allow them (according to their assigned role) to have access to some of the db’s functionalities.
So, I have a table (=LoginTable) with four fields: userID (autonumber), username (text), password (text) and Role (text):

|userID|username|password|Role|

A basic login form (=LoginForm) has been created with two unbound fields namely: usernamecombo (having as a source the username field of the LoginTable) and of course password (=passwordtxt). A button is also added to enter the db’s switchboard. The code I’ve written for the “Enter db” button works fine so once this is pressed the user sees the switchboard, but I am stuck with writing the correct code about the user’s role once a button on the switchboard is pressed. For example, if the user’s role is “user” then a message box will notify him/her that access is not granted etc. I thought of writing a code once the button is pressed similar to:

Dim userRole as string
userRole = dlookup(“[Role]”,”LoginTable”,…..(criteria))
if userRole = “user” then msgbox ....
Else
Docmd.OpenForm "MAIN"
End if

Probably I am doing something wrong with the criteria in the dlookup line, because no matter what I have tried, I always get an error message. Do I have to declare the role variable as a public variable in order to use it while I am in a form other than the LoginForm?

Any suggestions are more than welcome.

Thank you in advance for your assistance.

 

[spikepl]

One thing at a time.

When you ask a question concerning an error message in code, then provide the actual text of the message and the actual code generating that message, and an indication of which specific line failed. Otherwise the possibilities are endless (and the readers - correction: some readers - here do not have remote vision)

I note that your post shows smart quotes “” - these are hopefully not in your code.

 

[Taruz]

Hi..

try this way..:


userRole = DLookup("[Role]", "LoginTable", _
"username ='" & usernamecombo & "' ")

 

[alex_]

Thank you very much for your replies (especially Taruz). So for those unlucky few (just like myself) who do not possess remote vision, I respectfully present you with the code (it didn't contain any smart quotes from the first place, but thanks for pointing that out):

Private Sub Option4_Click()
Dim useRole As String
userRole = DLookup("[Role]", "LoginTable", _
"username ='" & usernamecombo & "' ")
If userRole = "user" Then
MsgBox "You are not authorised to perform this action", vbCritical, "Not permitted"
Else
DoCmd.OpenForm "MAIN"
End If
End Sub

Results:

No error! But: Now, once the button on the switchboard is pressed form "MAIN" opens, although the user's role in the table is defined as "user". Any ideas?

PS: Taruz thanks a lot! I knew I was missing something from the criteria in the dlookup command (those little details...).

 

[alex_]

OK, the code I've entered before contains a little typo: in the definition of the userRole variable, it was written useRole instead of userRole. Anyway, even with the following code:

Private Sub Option4_Click()
Dim userRole As String
userRole = DLookup("[Role]", "LoginTable", "username ='" & usernamecombo & "' ")
If userRole = "user" Then
MsgBox "You are not authorised to perform this action", vbCritical, "Not permitted"
Else
DoCmd.OpenForm "MAIN"
End If
End Sub

I get the following error when the button is pressed:

Run-time error '94'
Invalid use of Null

Thank's in advance for your assistance.

 

[jdraw]

Use Option Explicit at the top of your modules and Access will highlight undefined variables and spelling errors automatically.

Use Error trapping in your procedures.

There is a free utility MZTools, that can insert error trapping in a procedure with the click of a button. It does much much more as well. If you don't have it, get it

www.mztools.com

As for your user role issue, I don't think there is quick answer without seeing the application or doing specific tests.

You may need to put some code in the OnLoad or OnOpen of each form that may be opened to actually prevent the Form opening. Or you may have to disable some buttons depending on "role".

There is a discussion on Permissions that may give you more options/ideas at
http://www.mrexcel.com/forum/showthread.php?t=248191
SydneyGeek is a well respected MVP

 

[alex_]

Thank you all for the kind assistance. Please find attached an example db with what I am trying to do. The code written for the button created on the sample switchboard, gives the error message posted in a previous reply.

Have a look and -if possible- tell me your opinion.

Thanks in advance for your assistance.

PS: Passwords for the loginTable are "admin" and "user"

 

[alex_]

Re: user Role/Solved

Problem solved. See attached .zip file. If someone can come up with a faster (or smarter) way to do this, please enlight the other members.

Thank you.