xaveboyz
asked on
simple question (i guess): assign different roles in access
hai, i'm new access user and i want to create login form for my database, i have "user table" that contains user id, name, role and password, here is the sample:
id name role pass
1 A admin 1111
2 B user 2222
in the form i have textbox for inserting pasword, 1 combobox to select username, and one more combobox for selecting user role
and this is my code inside the login form:
Private Sub cmdlogin_Click()
Static intpasswordcount As Integer
If Me!txtpassword = Me![cmbusername].Column(3)
DoCmd.OpenForm "frmwelcome", acNormal
Else
If Me!txtpassword = "" Then
MsgBox "Please insert your password", vbCritical
Else
MsgBox "Incorrect password or wrong user role", vbCritical
End If
intpasswordcount = intpasswordcount + 1
If intpasswordcount = 3 Then
DoCmd.Close acForm, Me.Name
End If
Exit Sub
End If
End Sub
the problem is everytime i pushed the enter button, an "incorrect password or user role" error message will come up.Actually what I want is if A enters the login page , the database must know that he is and "Admin", not "user", so though he has typed correct password but choose wrong role, he will be still kicked out..can somebody help me ??
thank you
id name role pass
1 A admin 1111
2 B user 2222
in the form i have textbox for inserting pasword, 1 combobox to select username, and one more combobox for selecting user role
and this is my code inside the login form:
Private Sub cmdlogin_Click()
Static intpasswordcount As Integer
If Me!txtpassword = Me![cmbusername].Column(3)
DoCmd.OpenForm "frmwelcome", acNormal
Else
If Me!txtpassword = "" Then
MsgBox "Please insert your password", vbCritical
Else
MsgBox "Incorrect password or wrong user role", vbCritical
End If
intpasswordcount = intpasswordcount + 1
If intpasswordcount = 3 Then
DoCmd.Close acForm, Me.Name
End If
Exit Sub
End If
End Sub
the problem is everytime i pushed the enter button, an "incorrect password or user role" error message will come up.Actually what I want is if A enters the login page , the database must know that he is and "Admin", not "user", so though he has typed correct password but choose wrong role, he will be still kicked out..can somebody help me ??
thank you
Hi xaveboyz,
change
If Me!txtpassword = Me![cmbusername].Column(3)
into
If Me!txtpassword = Me![cmbusername].Column(3)
Good luck,
YP
change
If Me!txtpassword = Me![cmbusername].Column(3)
into
If Me!txtpassword = Me![cmbusername].Column(3)
Good luck,
YP
ASKER
for YP, i think i've tried that for couple of times, but doesnt work, but thank you anyway...
for andrew, when I typed ?me!cmbusername.column(2),
for andrew, when I typed ?me!cmbusername.column(2),
xaveboyz,
don't see a reason why it wouldn't work
you can try first
If Me!txtpassword = Me![cmbusername].Column(3)
and if this works then change to
If Me!txtpassword = Me![cmbusername].Column(3)
The problem will be that 'Me!cmbuserrole = "admin"' returns FALSE, you can check it in you debug window.
So see for the real value of Me!cmbuserrole and change the condition so it will be true in the cases you want.
Could you write the SELECT statement of your combobox ?
Best regards,
YP.
don't see a reason why it wouldn't work
you can try first
If Me!txtpassword = Me![cmbusername].Column(3)
and if this works then change to
If Me!txtpassword = Me![cmbusername].Column(3)
The problem will be that 'Me!cmbuserrole = "admin"' returns FALSE, you can check it in you debug window.
So see for the real value of Me!cmbuserrole and change the condition so it will be true in the cases you want.
Could you write the SELECT statement of your combobox ?
Best regards,
YP.
What are the values for each of the columns in Me![cmbusername]? What is the bould column and what is the column count set to?
Cheers, Andrew
Cheers, Andrew
Sorry bould should be bound
ASKER
the value of cmbusername is the name of user and admin (e.g. A and B) bound column is 2 and column count is set to 2
sorry, this is the true table
id name Password role
1 A 1111 admin
2 B 2222 user
sorry, this is the true table
id name Password role
1 A 1111 admin
2 B 2222 user
xaveboyz,
I think it should be like this
Rowsource : SELECT ID, Name, PassWord, Role FROM YourTableName
Bound Column : 1
Column Count : 4
Column Widths : 0;2;0;0
YP
I think it should be like this
Rowsource : SELECT ID, Name, PassWord, Role FROM YourTableName
Bound Column : 1
Column Count : 4
Column Widths : 0;2;0;0
YP
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
assuming you do not need (include) ID column then columns index starts at 0 and so for name it will be column(0) and so forth....
another option is to have the user enter userid and password during logon.So on your logon form you can have 2 textboxs ,
1-txtpassword
2-txtuser
on the cmdlogon_click write
dim rst as recordset
dim strSQL as string
dim strUser as string,strPassword as string
me.txtuser.setfocus
strUser=me.txtUser.text
me.txtpassword.setfocus
strPassword=me.txtpassword
strSQL="SELECT * FROM usertable WHERE name='" & strUser & "' and pass = '" & strpassword & "'"
'test if selection has data if empty then not valid data
set rst=currentdb.openrecordse
if not rst.eof then
'to check if admin
if rst![role]="admin"
'do something
end if
DoCmd.OpenForm "frmwelcome", acNormal
else
msgbox "Invalid Logon Info"
end if
this is just a rough idea of what you can do .You can tailor make it for your needs
pBee>>
1-txtpassword
2-txtuser
on the cmdlogon_click write
dim rst as recordset
dim strSQL as string
dim strUser as string,strPassword as string
me.txtuser.setfocus
strUser=me.txtUser.text
me.txtpassword.setfocus
strPassword=me.txtpassword
strSQL="SELECT * FROM usertable WHERE name='" & strUser & "' and pass = '" & strpassword & "'"
'test if selection has data if empty then not valid data
set rst=currentdb.openrecordse
if not rst.eof then
'to check if admin
if rst![role]="admin"
'do something
end if
DoCmd.OpenForm "frmwelcome", acNormal
else
msgbox "Invalid Logon Info"
end if
this is just a rough idea of what you can do .You can tailor make it for your needs
pBee>>
The bound column is normally the ID field but it doesn't have to be, the column count has to incl;ude all the columns you are referencing so it should be 4 to represent columns 0 to 3.
Cheers, Andrew
Cheers, Andrew
the column width also plays a role. If you keep it zero and try to display it will show null..
ASKER
i think you are the closest one, thanks
In the immidiate window (CTRL-G will show it if it is not open) you can type in the following and compare the values in each, I suspect either the column numbers are wrong or the user role column has a bound column that is an ID and your are trying to compare it to a description.
?Me!txtpassword
?Me![cmbusername].Column(3
?Me!cmbuserrole
?Me![cmbusername].Column(2
Cheers, Andrew