Password Validation Problem

D

Dot.FT

Hi
I have a table that contains users' Passwords and Role codes
Users need to access certain forms depending on the Role Code entered in
txtRCode textbox.
For example:
- If table field RoleCodeT = "T" user has no access to Form Admin.
- If table field RoCodeD = "D" user has access to Form Develop.
- If table field RoleCodeA = "A" user has access to Form Admin.

Password validation is working fine. However, the role code validation is
not correct.
I store and validate for either "A" or "D" as follows:

If txtRCode = "A" Then
strRoleCodeAdminIn = "A"
strRoleCodeAdmin = Nz(DLookup("Role_CodeAdmin", "AccStaff", "Role_CodeAdmin
= '" & txtRCode & "'"), "")
Exit Sub
End If

If txtCode = "D" Then
strRoleCodeDevIn = "D"
strRoleCodeDev = Nz(DLookup("Role_CodeDeveloper", "AccStaff",
"Role_CodeDeveloper = '" & txtRCode & "'"), "")
Exit Sub
End If

At the moment I can enter any char in textbox RoleCode and the user has
access. Any suggestions to validate if user entered A or D and it matches the
table entry then display the form accordingly?
Something like?
If strRoleCodeDev = strRoleCodedevIn And strUserName = txtUID And
strUserPWD = txtUPWD Then
' Open Main Menu
DoCmd.OpenForm "Administration Menu"


Thanks in advance,
Dot.FT
 
B

BeWyched

Hi, you haven't detailed how things are linked together but I think I can see
what you are trying to do. Your DLookup method doesn't seem to search for an
individual's record and so will return the first in all cases. Also, you
haven't defined if an individual can be, e.g., a developer and an
administrator. Your coding suggests they can only be one. If so, then why not
simplify your table with just 1 RoleCode field, containing either a 'D' or an
'A':

Try:

Select Case txtRCode
Case "A"
If Nz(DLookUp("RoleCodeA", "AccStaff", "UserName = '" & txtUID & "' and
UserPWD = '" & txtUPWD & "'),"") = "A" then
Docmd.OpenForm "Administration View"
Exit Sub
End If
Case "D"
as above
....
End Select

Cheers

BW
 
L

Lincoln

Any reason why you cannot use the Group Aministrator to secure your database
and manage previleges, and all the objects in the database; including
"ROLES"....
 
N

Nicholas Scarpinato

It seems to me like you're trying to do this the hard way. You only need one
field for role permissions, even with multiple roles per user, since you can
always make each role supercede the previous one. Or you could build your
roles by job type. I wrote a password checker code to handle role permissions
and such. This particular code also checks the login three times and then
boots the user. Make sure you put the lines:

Global AttemptCount
Global UsernameTag
Global UsernameForm

in one of your modules, otherwise the code doesn't work. Also, turn off the
Access special keys in the Startup settings, set your login form to open at
startup, and hide the main DB window. Here's the code I use, running off a
three field table using fields Username, Password, and User Form, and running
off the "OK" button of the main login form (by the way, feel free to modify
this as needed if you decide to use it):

Private Sub Command4_Click()
DoCmd.SetWarnings False
Dim db, sql, rs, User, Pass, tblUser, tblPass, BadLogin
Set db = CurrentDb()
sql = "SELECT * FROM [Username and Password Table];"
Set rs = db.OpenRecordset(sql)
User = Me![Username]
Pass = Me![Password]
With rs
.MoveFirst
Do Until .EOF
tblUser = .Fields("Username")
tblPass = .Fields("Password")
If User = tblUser And Pass = tblPass Then
UsernameTag = User
UsernameForm = .Fields("User Form")
GoTo LoginCorrect
End If
.MoveNext
Loop
End With
AttemptCount = AttemptCount + 1
BadLogin = 1
If AttemptCount = 3 Then
MsgBox "You have used up your three login attempts. Goodbye!"
DoCmd.Quit
Else
MsgBox "Login incorrect, please try again."
User = ""
Pass = ""
Me![Username] = ""
Me![Password] = ""
Me![Username].SetFocus
End If
GoTo LoginEnd
LoginCorrect:
BadLogin = 0
DoCmd.OpenForm UsernameForm
GoTo LoginEnd
LoginEnd:
rs.Close
db.Close
DoCmd.SetWarnings True
DoCmd.Close acForm, "Login Form"
If BadLogin = 1 Then DoCmd.OpenForm "Login Form"
End Sub

I set up my permissions to where each user has access to a specific main
menu form and they can't run anything else not on that form because I turned
off all the menu bars and toolbars via a bit of code that runs when the login
form opens (I forget who posted this code originally, but thank you to
whoever you were!):

Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = False
Next i

Then on your user forms, use this validation code to make sure the user
opening them didn't somehow circumvent the system. Granted, if they've gotten
past the fact that all the toolbars and menubars are turned off and they
can't bring up the main database window, this validation code really won't do
much... but luckily the likelyhood of that happening is pretty slim, I wrote
most of this code and I can't even get around it without opening the DB with
the shift key pressed down... This is the code for the Admin form, which
re-enables all the toolbars as well:

Private Sub Form_Open(Cancel As Integer)
Me.Visible = False
If UsernameForm <> Me.Name Then
MsgBox "You are not authorized to view this form."
DoCmd.Close acForm, Me.Name
End If
DoCmd.SelectObject acTable, "", True
Me.Visible = True
Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = True
Next i
End Sub


This should make it much easier to deal with role permissions, since you can
change a user's permissions by simply opening the user table and changing one
field (or use a form to do the same thing).
 
D

Dot.FT

Hi BeWyched
Thanks for the quick and helpfull reply. I will be taking into consideration
your code and see I go.

Many Thanks

Dot.FT
 
D

Dot.FT

Nicholas
Thanks for the quick reply.
Your advice and code are going to be very useful. I will incorporate the "3
times you are baby!" code also.

Ciao and thanks

Dot.FT

Nicholas Scarpinato said:
It seems to me like you're trying to do this the hard way. You only need one
field for role permissions, even with multiple roles per user, since you can
always make each role supercede the previous one. Or you could build your
roles by job type. I wrote a password checker code to handle role permissions
and such. This particular code also checks the login three times and then
boots the user. Make sure you put the lines:

Global AttemptCount
Global UsernameTag
Global UsernameForm

in one of your modules, otherwise the code doesn't work. Also, turn off the
Access special keys in the Startup settings, set your login form to open at
startup, and hide the main DB window. Here's the code I use, running off a
three field table using fields Username, Password, and User Form, and running
off the "OK" button of the main login form (by the way, feel free to modify
this as needed if you decide to use it):

Private Sub Command4_Click()
DoCmd.SetWarnings False
Dim db, sql, rs, User, Pass, tblUser, tblPass, BadLogin
Set db = CurrentDb()
sql = "SELECT * FROM [Username and Password Table];"
Set rs = db.OpenRecordset(sql)
User = Me![Username]
Pass = Me![Password]
With rs
.MoveFirst
Do Until .EOF
tblUser = .Fields("Username")
tblPass = .Fields("Password")
If User = tblUser And Pass = tblPass Then
UsernameTag = User
UsernameForm = .Fields("User Form")
GoTo LoginCorrect
End If
.MoveNext
Loop
End With
AttemptCount = AttemptCount + 1
BadLogin = 1
If AttemptCount = 3 Then
MsgBox "You have used up your three login attempts. Goodbye!"
DoCmd.Quit
Else
MsgBox "Login incorrect, please try again."
User = ""
Pass = ""
Me![Username] = ""
Me![Password] = ""
Me![Username].SetFocus
End If
GoTo LoginEnd
LoginCorrect:
BadLogin = 0
DoCmd.OpenForm UsernameForm
GoTo LoginEnd
LoginEnd:
rs.Close
db.Close
DoCmd.SetWarnings True
DoCmd.Close acForm, "Login Form"
If BadLogin = 1 Then DoCmd.OpenForm "Login Form"
End Sub

I set up my permissions to where each user has access to a specific main
menu form and they can't run anything else not on that form because I turned
off all the menu bars and toolbars via a bit of code that runs when the login
form opens (I forget who posted this code originally, but thank you to
whoever you were!):

Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = False
Next i

Then on your user forms, use this validation code to make sure the user
opening them didn't somehow circumvent the system. Granted, if they've gotten
past the fact that all the toolbars and menubars are turned off and they
can't bring up the main database window, this validation code really won't do
much... but luckily the likelyhood of that happening is pretty slim, I wrote
most of this code and I can't even get around it without opening the DB with
the shift key pressed down... This is the code for the Admin form, which
re-enables all the toolbars as well:

Private Sub Form_Open(Cancel As Integer)
Me.Visible = False
If UsernameForm <> Me.Name Then
MsgBox "You are not authorized to view this form."
DoCmd.Close acForm, Me.Name
End If
DoCmd.SelectObject acTable, "", True
Me.Visible = True
Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = True
Next i
End Sub


This should make it much easier to deal with role permissions, since you can
change a user's permissions by simply opening the user table and changing one
field (or use a form to do the same thing).


Dot.FT said:
Hi
I have a table that contains users' Passwords and Role codes
Users need to access certain forms depending on the Role Code entered in
txtRCode textbox.
For example:
- If table field RoleCodeT = "T" user has no access to Form Admin.
- If table field RoCodeD = "D" user has access to Form Develop.
- If table field RoleCodeA = "A" user has access to Form Admin.

Password validation is working fine. However, the role code validation is
not correct.
I store and validate for either "A" or "D" as follows:

If txtRCode = "A" Then
strRoleCodeAdminIn = "A"
strRoleCodeAdmin = Nz(DLookup("Role_CodeAdmin", "AccStaff", "Role_CodeAdmin
= '" & txtRCode & "'"), "")
Exit Sub
End If

If txtCode = "D" Then
strRoleCodeDevIn = "D"
strRoleCodeDev = Nz(DLookup("Role_CodeDeveloper", "AccStaff",
"Role_CodeDeveloper = '" & txtRCode & "'"), "")
Exit Sub
End If

At the moment I can enter any char in textbox RoleCode and the user has
access. Any suggestions to validate if user entered A or D and it matches the
table entry then display the form accordingly?
Something like?
If strRoleCodeDev = strRoleCodedevIn And strUserName = txtUID And
strUserPWD = txtUPWD Then
' Open Main Menu
DoCmd.OpenForm "Administration Menu"


Thanks in advance,
Dot.FT
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Validation 1
Validation rule for Access 2003 2
Problem with Validation Error 10
command button validation 2
validation rule for not allowing duplicate entries 3
Access 2007 exception on Date Validation rule 0
validation as an alternative to lookup fields? 2
Testing for Password on a Form 1

Top