Hi,
I'm new to Access or as a matter of fact it's the first time that I'm working with a database. I'm working on a program in Access for a class project and I'm having some trouble with the login application. The program is basically a database for a small store which allows the employees to login with or without administrator access. The usename and password for each employee is stored in tblEmployee under strUseName and strPassword. The table also contains a ysnAdmin checkbox. (The ysnAdmin boolean variable is declared globally since it is also called from frmMain.)The problem I'm having is that when I try to do the rst.FindFirst it works if the password and username I enter are incorrect and the record is not found but when I enter a password and username that is stored in the tblEmployee I get a Type error message. I would really appreciate if somebody could look at this code and tell me what I'm doing wrong. -
Private Sub cmd_Login_Click()
-
Dim rst As DAO.Recordset
-
Dim strUser As String
-
Dim strPass As String
-
Dim strSQL As String
-
-
-
-
If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
-
MsgBox "Please enter a valid User Name", vbExclamation, strApp
-
Me.txtUserName.SetFocus
-
Exit Sub
-
ElseIf Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
-
MsgBox "Please enter a valid password", vbExclamation, strApp
-
Me.txtPassword.SetFocus
-
Exit Sub
-
End If
-
strUser = Me.txtUserName
-
strPass = Me.txtPassword
-
-
strSQL = "SELECT tblEmployee.strUserName, tblEmployee.strPassword, tblEmployee.ysnAdmin FROM tblEmployee WHERE (((tblEmployee.strUserName)='" & strUser & "') AND ((tblEmployee.strPassword)='" & strPass & "'))"
-
-
-
Set rst = CurrentDb.OpenRecordset(strSQL)
-
-
rst.FindFirst (strSQL) '(Type error message)
-
If rst.NoMatch Then
-
MsgBox ("Access Denied")
-
Else
-
If Not rst.EOF Then
-
If rst!ysnAdmin = True Then
-
ysnAdmin = True
-
Else
-
ysnAdmin = False
-
End If
-
End If
-
End If
-
-
rst.Close
-
Set rst = Nothing
-
-
DoCmd.Close acForm, Me.Name
-
-
End Sub
-
10 1548
We're not supposed to be doing anybody's homework here, but here's a fix for the bad part of your code. -
Set rst = CurrentDb.OpenRecordset(strSQL)
-
-
If (rst.BOF And rst.EOF) Then
-
MsgBox ("Access Denied")
-
Else
-
ysnAdmin = rst!ysnAdmin
-
End If
-
But after that point, you just close the form whether or not they've successfully logged in - all you really know is if they're an admin. That's a problem.
Thanks so much wvmitchel! I know that I still need to improve a lot of things but at least the part that has been bugging me for a long time works now thanks to your help :).
Hi,
I'm new to Access or as a matter of fact it's the first time that I'm working with a database. I'm working on a program in Access for a class project and I'm having some trouble with the login application. The program is basically a database for a small store which allows the employees to login with or without administrator access. The usename and password for each employee is stored in tblEmployee under strUseName and strPassword. The table also contains a ysnAdmin checkbox. (The ysnAdmin boolean variable is declared globally since it is also called from frmMain.)The problem I'm having is that when I try to do the rst.FindFirst it works if the password and username I enter are incorrect and the record is not found but when I enter a password and username that is stored in the tblEmployee I get a Type error message. I would really appreciate if somebody could look at this code and tell me what I'm doing wrong. -
Private Sub cmd_Login_Click()
-
Dim rst As DAO.Recordset
-
Dim strUser As String
-
Dim strPass As String
-
Dim strSQL As String
-
-
-
-
If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
-
MsgBox "Please enter a valid User Name", vbExclamation, strApp
-
Me.txtUserName.SetFocus
-
Exit Sub
-
ElseIf Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
-
MsgBox "Please enter a valid password", vbExclamation, strApp
-
Me.txtPassword.SetFocus
-
Exit Sub
-
End If
-
strUser = Me.txtUserName
-
strPass = Me.txtPassword
-
-
strSQL = "SELECT tblEmployee.strUserName, tblEmployee.strPassword, tblEmployee.ysnAdmin FROM tblEmployee WHERE (((tblEmployee.strUserName)='" & strUser & "') AND ((tblEmployee.strPassword)='" & strPass & "'))"
-
-
-
Set rst = CurrentDb.OpenRecordset(strSQL)
-
-
rst.FindFirst (strSQL) '(Type error message)
-
If rst.NoMatch Then
-
MsgBox ("Access Denied")
-
Else
-
If Not rst.EOF Then
-
If rst!ysnAdmin = True Then
-
ysnAdmin = True
-
Else
-
ysnAdmin = False
-
End If
-
End If
-
End If
-
-
rst.Close
-
Set rst = Nothing
-
-
DoCmd.Close acForm, Me.Name
-
-
End Sub
-
Again based on not wanting to do homework for you it might be pertinent to illustrate a few thoughts to you to make you think on the design front.
The logic of what you are intending is to FINDFIRST the value of the strSQL string statement in its completeness which is never going to work.
If you were going to use the findfirst method then you would pass to it something to find ie: where strusername = struser and strpassword= strpass not the complete strSQL string if you understand me.
As it is you are writing an SQL string that is in itself capable of retrieving rows of data (and I says rows) that should be sufficient to test for the username and password anyway by virtue of the restrictive WHERE clause you are defining.
Now what follows is a difference in approach for that you 'might' wish to analyse to understand the differences.
Your full SQL could potentially return any number of rows including duplicate entries (unless of course you have a composite primary key based on username and password.) With that in mind have a look at this. The procedure is based around your SQL statement and is designed to illustatrate the point I elude to based around your SQL string rather than emphasising any its overall efficiency. -
-
Private Sub cmdLogin_Click()
-
On Error GoTo errhandler
-
Dim db As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim strUser As String
-
Dim strPass As String
-
Dim strSQL As String
-
Dim mycount As Integer
-
If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
-
MsgBox "Please enter a valid User Name", vbExclamation, "User name required"
-
Me.txtUserName.SetFocus
-
Exit Sub
-
ElseIf Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
-
MsgBox "Please enter a valid password", vbExclamation, "Password Required"
-
Me.txtPassword.SetFocus
-
Exit Sub
-
End If
-
strUser = Me.txtUserName
-
strPass = Me.txtPassword
-
strSQL = "SELECT strUserName,strPassword,ysnAdmin FROM tblEmployee "
-
strSQL = strSQL & "WHERE strUserName='" & strUser & "' AND strPassword ='" & strPass & "'"
-
Set db = CurrentDb
-
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
-
rst.MoveLast
-
mycount = rst.RecordCount
-
If mycount = 0 Then
-
MsgBox "The combination of username and password is not known.", vbExclamation, "System Message"
-
Exit Sub
-
ElseIf mycount > 1 Then
-
MsgBox "There is more than one of the combinations of username and password in the table notify an administrator."
-
Exit Sub
-
ElseIf mycount = 1 Then
-
If rst!strUserName = strUser And strpassword = strpassword Then
-
MsgBox "Hooray we got in based on that combination!!", vbInformation, "Success"
-
If rst!ysnAdmin = True Then
-
ysnAdmin = True
-
Else
-
ysnAdmin = False
-
End If
-
End If
-
Else
-
MsgBox ("Access Denied")
-
End If
-
rst.Close
-
Set rst = Nothing
-
Exit_cmdLogin_Click:
-
DoCmd.Close acForm, Me.Name
-
Exit Sub
-
errhandler:
-
If Err.Number = 3021 Then
-
'this error number denotes no current recordset returned based on the combination used
-
MsgBox "The combination of user name and password is not known.", vbInformation, "System Message"
-
Else
-
'indeterminate error so return an error message
-
MsgBox Err.Number & Err.Description, vbInformation, "System Message"
-
End If
-
Resume Exit_cmdLogin_Click
-
-
End Sub
Regards
Jim :)
Jim, you said
"Your full SQL could potentially return any number of rows including duplicate entries (unless of course you have a composite primary key based on username and password.) "
But a user login table must always have a unique index on the username, therefore since you will never have a duplicate username, a composite index is not necessary. Examples - logging in to your email account, or online banking, or even this forum, you are allowed one and only one password for a given username.
The original strSQL filters for both the username & password, so the only two possible outcomes are either 1 row (success) or 0 rows (failure). It is more efficient to test for (rst.BOF And rst.EOF) which means failure, otherwise you have success & then you grab the admin flag from the rst & you're done.
Back to the original full post, if you add
DoCmd.Quit, acSaveNone
right after the Access Denied message, the app will close if the user fails to log in. Which accomplishes the desired control of access to the application.
Jim,
Sorry if I sounded rude, I meant no disrespect.
Bill
Thanks everybody for your help and sorry to get you involved in my homework. The good news is that I got the login to work using another global boolean variable and your help and that I learned something new.
Basically when the user logs in certain cmdbuttons get enabled in the main form depending if the user has admin access or not. This restrics certain tasks to the administrator only. - Private Sub cmd_Login_Click()
-
Dim rst As DAO.Recordset
-
Dim strUser As String
-
Dim strPass As String
-
Dim strSQL As String
-
-
If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
-
MsgBox "Please enter a valid User Name", vbExclamation, strApp
-
Me.txtUserName.SetFocus
-
Exit Sub
-
ElseIf Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
-
MsgBox "Please enter a valid password", vbExclamation, strApp
-
Me.txtPassword.SetFocus
-
Exit Sub
-
End If
-
strUser = Me.txtUserName
-
strPass = Me.txtPassword
-
-
strSQL = "SELECT tblEmployee.strUserName, tblEmployee.strPassword, tblEmployee.ysnAdmin FROM tblEmployee WHERE (((tblEmployee.strUserName)='" & strUser & "') AND ((tblEmployee.strPassword)='" & strPass & "'))"
-
-
Set rst = CurrentDb.OpenRecordset(strSQL)
-
-
If (rst.BOF And rst.EOF) Then
-
If MsgBox("Access Denied! Would you like to try again?", vbExclamation + vbYesNo, strApp) = vbNo Then
-
DoCmd.Close acForm, Me.Name
-
bol = False
-
Else
-
Me.txtUserName = ""
-
Me.txtPassword = ""
-
End If
-
Else
-
bol = True
-
ysnAdmin = rst!ysnAdmin
-
DoCmd.Close acForm, Me.Name
-
End If
-
rst.Close
-
Set rst = Nothing
-
-
End Sub
-
Well thanks again. :)
P.S.
Can you recommend me a good MS Access/VBA book or website?
Jim, you said
"Your full SQL could potentially return any number of rows including duplicate entries (unless of course you have a composite primary key based on username and password.) "
But a user login table must always have a unique index on the username, therefore since you will never have a duplicate username, a composite index is not necessary. Examples - logging in to your email account, or online banking, or even this forum, you are allowed one and only one password for a given username.
The original strSQL filters for both the username & password, so the only two possible outcomes are either 1 row (success) or 0 rows (failure). It is more efficient to test for (rst.BOF And rst.EOF) which means failure, otherwise you have success & then you grab the admin flag from the rst & you're done.
Back to the original full post, if you add
DoCmd.Quit, acSaveNone
right after the Access Denied message, the app will close if the user fails to log in. Which accomplishes the desired control of access to the application.
Bill, 'But a user login table must always have a unique index on the username, therefore since you will never have a duplicate username, a composite index is not necessary'
But they don't necessarily know that they must have a unique index do they and they have not stated it.
I am not saying a composite index is necessary I am saying IF they have a composite index. The original strSQL filters for both the username & password, so the only two possible outcomes are either 1 row (success) or 0 rows (failure).
If you consider it from the feasible standpoint of having no unique index then potentially more than one row can be returned. The poster has not yet said they have a unique index on username........ yet! that is what I am hoping to arrive at. Remember we all sit here not knowing what the poster actually has in place. It is more efficient to test for (rst.BOF And rst.EOF)
I couldnt agree more 'Back to the original full post, if you add'
After the critical onslaught this sounds somewhat dismissive of me Bill but hey ho.
Jim :)
Jim,
Sorry if I sounded rude, I meant no disrespect.
Bill
Hi Bill,
I am sure you have no intention of being rude!.
The poster makes no mention of table structure or indeed knowing the obvious and that is to have a uniqueness on username.
Now without doing the assignment or project for them or providing the solution their next post might have been "Hey I can put two Bills in this login table how can I determine which one is logging in??"
I am familiar with the ramifications of logging into systems having personally built multi user enterprise wide commercial strength systems. I was rather hoping to treat the thread as a progressive thing, where the poster might gleefully have criticised my offering at which point I could have said something like "and why do you think that happened?" and so on, until they arrived at the solution themselves.
As it is you have splendidly advised both the poster on the solution and me on my logic and inefficiency (which in my para 5 if you notice I said it was not intending it to be so).
Thanks
Jim :)
Don't you think that DoCmd.Quit is required after a failed login in your post ( #4 ) ? Otherwise, the dialog is left open & the user has no way to cancel or close out from Access.
OTOH a savvy user could simply press Alt-F4 to close the dialog (unless the built-in Close button has been disabled on the form) and they would be granted access to the application. That's a security hole.
The OP has since added a boolean flag to denote login success or failure, but the original post did not include that. Their new version also includes an option to either re-try or cancel the login attempt, so the DoCmd.Quit would not be necessary any more.
Intellectual discussions aside guys, you are probably confusing the OP who is a newbie.
Can one or both of you outline the points you are making about unique indexes and application quiting for the benefit of the OP.
No criticism intended, I enjoyed the intellectual debate :D
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bert |
last post by:
Sorry for the unclear subject line.
I've got to write a download script for and existing file manager (we
are working on a complete redesign for this file manager, but the
functionality of...
|
by: Kamliesh Nadar |
last post by:
Hi
I am developing a software using VB.NET I am facing
following problems -
1. Though I have placed the NotifyIcon control on the
window service application, after starting the service I
am...
|
by: Art |
last post by:
Hello everyone,
I am interested in starting an all volunteer website which will be directed
at recovering missing children. I am aware that there are few other sites
out there with the same...
|
by: Dave W |
last post by:
Hi,
I urgently need help on an access database. I can't get back into design
mode! It needs to be handed in in approx 45 mins
http://www.aouh43.dsl.pipex.com/GeorgeFinal.zip
Cheers,
Dave
|
by: xunitinmullik |
last post by:
Hello ppl:
I am facing a problem that I ahve never experienced before with the
secured database. I created an MS Access 2000 application (.mdb) using
MS Access 2002 and secured it using exactly...
|
by: el prinCipante |
last post by:
I'm getting tired of the following error message.
Compiler Error message : Error: Need explicit cast to convert
from: float
to: float *
I am trying to use a routine from the Numerical Recipes...
|
by: Joonshik Kim |
last post by:
I was trying to define 3d array with pointer to pointer. I wrote like
following.
int ***d;
nx = 3;
ny = 5;
nz = 4;
d = (int ***)malloc((int) nx*sizeof(int **));
*d = (int **)malloc((int)...
|
by: janakivenk |
last post by:
Hello,
I am running Oracle 10g R2 in our office. I created the following procedure. It is suppose to access an xml file ( family.xml). The procedure is compiled and when I try to run it, i get the...
|
by: crack.the.hack |
last post by:
Hi All,
If I am changing the database machine, what should I do not to prep
bind the sqc files everytime.
Because I need to build my application everytime the database is
changed?
Is there...
|
by: Robert Wells |
last post by:
Gentlemen,
We are looking for two IBM documents that are needed urgently for a
project. They are titled "4680 Store Systems Serial I/O Channel Attachment
Information" and "Serial I/O Product...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| |