rstFindFirst Help Needed Urgently!

[Silvana]

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)  '([I]Type error message[/I])
    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

[wvmitchell]

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.

[Silvana]

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 :).

[Jim Doherty]

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) '([i]Type error message[/i])
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 :)

[wvmitchell]

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.

[wvmitchell]

Jim,

Sorry if I sounded rude, I meant no disrespect.

Bill

[Silvana]

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 Doherty]

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 Doherty]

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 :)

[wvmitchell]

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.

[MMcCarthy]

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