473,322 Members | 1,620 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

rstFindFirst Help Needed Urgently!

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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_Login_Click()
  2. Dim rst            As DAO.Recordset
  3. Dim strUser        As String
  4. Dim strPass        As String
  5. Dim strSQL         As String
  6.  
  7.  
  8.  
  9.     If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
  10.         MsgBox "Please enter a valid User Name", vbExclamation, strApp
  11.         Me.txtUserName.SetFocus
  12.         Exit Sub
  13.     ElseIf Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
  14.         MsgBox "Please enter a valid password", vbExclamation, strApp
  15.         Me.txtPassword.SetFocus
  16.         Exit Sub
  17.     End If
  18.     strUser = Me.txtUserName
  19.     strPass = Me.txtPassword
  20.  
  21.     strSQL = "SELECT tblEmployee.strUserName, tblEmployee.strPassword, tblEmployee.ysnAdmin FROM tblEmployee WHERE (((tblEmployee.strUserName)='" & strUser & "') AND ((tblEmployee.strPassword)='" & strPass & "'))"
  22.  
  23.  
  24.     Set rst = CurrentDb.OpenRecordset(strSQL)
  25.  
  26.     rst.FindFirst (strSQL)  '(Type error message)
  27.     If rst.NoMatch Then
  28.         MsgBox ("Access Denied")
  29.     Else
  30.         If Not rst.EOF Then
  31.             If rst!ysnAdmin = True Then
  32.                 ysnAdmin = True
  33.             Else
  34.                 ysnAdmin = False
  35.             End If
  36.         End If
  37.     End If
  38.  
  39.     rst.Close
  40.     Set rst = Nothing
  41.  
  42.     DoCmd.Close acForm, Me.Name
  43.  
  44. End Sub
  45.  
Dec 1 '07 #1
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.

Expand|Select|Wrap|Line Numbers
  1.     Set rst = CurrentDb.OpenRecordset(strSQL)
  2.  
  3.     If (rst.BOF And rst.EOF) Then
  4.         MsgBox ("Access Denied")
  5.     Else
  6.         ysnAdmin = rst!ysnAdmin
  7.     End If
  8.  
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.
Dec 1 '07 #2
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 :).
Dec 1 '07 #3
Jim Doherty
897 Expert 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_Login_Click()
  2. Dim rst As DAO.Recordset
  3. Dim strUser As String
  4. Dim strPass As String
  5. Dim strSQL As String
  6.  
  7.  
  8.  
  9. If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
  10. MsgBox "Please enter a valid User Name", vbExclamation, strApp
  11. Me.txtUserName.SetFocus
  12. Exit Sub
  13. ElseIf Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
  14. MsgBox "Please enter a valid password", vbExclamation, strApp
  15. Me.txtPassword.SetFocus
  16. Exit Sub
  17. End If
  18. strUser = Me.txtUserName
  19. strPass = Me.txtPassword
  20.  
  21. strSQL = "SELECT tblEmployee.strUserName, tblEmployee.strPassword, tblEmployee.ysnAdmin FROM tblEmployee WHERE (((tblEmployee.strUserName)='" & strUser & "') AND ((tblEmployee.strPassword)='" & strPass & "'))"
  22.  
  23.  
  24. Set rst = CurrentDb.OpenRecordset(strSQL)
  25.  
  26. rst.FindFirst (strSQL) '(Type error message)
  27. If rst.NoMatch Then
  28. MsgBox ("Access Denied")
  29. Else
  30. If Not rst.EOF Then
  31. If rst!ysnAdmin = True Then
  32. ysnAdmin = True
  33. Else
  34. ysnAdmin = False
  35. End If
  36. End If
  37. End If
  38.  
  39. rst.Close
  40. Set rst = Nothing
  41.  
  42. DoCmd.Close acForm, Me.Name
  43.  
  44. End Sub
  45.  
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdLogin_Click()
  3.     On Error GoTo errhandler
  4.     Dim db As DAO.Database
  5.     Dim rst As DAO.Recordset
  6.     Dim strUser As String
  7.     Dim strPass As String
  8.     Dim strSQL As String
  9.     Dim mycount As Integer
  10.     If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
  11.         MsgBox "Please enter a valid User Name", vbExclamation, "User name required"
  12.         Me.txtUserName.SetFocus
  13.         Exit Sub
  14.     ElseIf Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
  15.         MsgBox "Please enter a valid password", vbExclamation, "Password Required"
  16.         Me.txtPassword.SetFocus
  17.         Exit Sub
  18.     End If
  19.     strUser = Me.txtUserName
  20.     strPass = Me.txtPassword
  21.     strSQL = "SELECT strUserName,strPassword,ysnAdmin FROM tblEmployee "
  22.     strSQL = strSQL & "WHERE strUserName='" & strUser & "' AND strPassword ='" & strPass & "'"
  23.     Set db = CurrentDb
  24.     Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
  25.     rst.MoveLast
  26.     mycount = rst.RecordCount
  27.     If mycount = 0 Then
  28.         MsgBox "The combination of username and password is not known.", vbExclamation, "System Message"
  29.         Exit Sub
  30.     ElseIf mycount > 1 Then
  31.         MsgBox "There is more than one of the combinations of username and password in the table notify an administrator."
  32.         Exit Sub
  33.     ElseIf mycount = 1 Then
  34.         If rst!strUserName = strUser And strpassword = strpassword Then
  35.             MsgBox "Hooray we got in based on that combination!!", vbInformation, "Success"
  36.             If rst!ysnAdmin = True Then
  37.                 ysnAdmin = True
  38.             Else
  39.                 ysnAdmin = False
  40.             End If
  41.         End If
  42.     Else
  43.         MsgBox ("Access Denied")
  44.     End If
  45.     rst.Close
  46.     Set rst = Nothing
  47. Exit_cmdLogin_Click:
  48.     DoCmd.Close acForm, Me.Name
  49.     Exit Sub
  50. errhandler:
  51.     If Err.Number = 3021 Then
  52.         'this error number denotes no current recordset returned based on the combination used
  53.         MsgBox "The combination of user name and password is not known.", vbInformation, "System Message"
  54.     Else
  55.         'indeterminate error so return an error message
  56.         MsgBox Err.Number & Err.Description, vbInformation, "System Message"
  57.     End If
  58.     Resume Exit_cmdLogin_Click
  59.  
  60. End Sub
Regards

Jim :)
Dec 1 '07 #4
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.
Dec 1 '07 #5
Jim,

Sorry if I sounded rude, I meant no disrespect.

Bill
Dec 1 '07 #6
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.

Expand|Select|Wrap|Line Numbers
  1.  Private Sub cmd_Login_Click()
  2. Dim rst            As DAO.Recordset
  3. Dim strUser        As String
  4. Dim strPass        As String
  5. Dim strSQL         As String
  6.  
  7.     If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
  8.         MsgBox "Please enter a valid User Name", vbExclamation, strApp
  9.         Me.txtUserName.SetFocus
  10.         Exit Sub
  11.     ElseIf Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
  12.         MsgBox "Please enter a valid password", vbExclamation, strApp
  13.         Me.txtPassword.SetFocus
  14.         Exit Sub
  15.     End If
  16.     strUser = Me.txtUserName
  17.     strPass = Me.txtPassword
  18.  
  19.     strSQL = "SELECT tblEmployee.strUserName, tblEmployee.strPassword, tblEmployee.ysnAdmin FROM tblEmployee WHERE (((tblEmployee.strUserName)='" & strUser & "') AND ((tblEmployee.strPassword)='" & strPass & "'))"
  20.  
  21.     Set rst = CurrentDb.OpenRecordset(strSQL)
  22.  
  23.     If (rst.BOF And rst.EOF) Then
  24.         If MsgBox("Access Denied! Would you like to try again?", vbExclamation + vbYesNo, strApp) = vbNo Then
  25.             DoCmd.Close acForm, Me.Name
  26.             bol = False
  27.         Else
  28.             Me.txtUserName = ""
  29.             Me.txtPassword = ""
  30.         End If
  31.     Else
  32.         bol = True
  33.         ysnAdmin = rst!ysnAdmin
  34.         DoCmd.Close acForm, Me.Name
  35.     End If
  36.     rst.Close
  37.     Set rst = Nothing
  38.  
  39. End Sub
  40.  


Well thanks again. :)

P.S.
Can you recommend me a good MS Access/VBA book or website?
Dec 1 '07 #7
Jim Doherty
897 Expert 512MB
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 :)
Dec 2 '07 #8
Jim Doherty
897 Expert 512MB
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 :)
Dec 2 '07 #9
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.
Dec 2 '07 #10
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 11 '07 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

0
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...
0
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...
37
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...
2
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
4
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...
5
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...
4
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)...
1
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...
6
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...
1
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
0
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...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.