04-21-12, 16:40 #1Registered User
- Join Date
- Mar 2012
Unanswered: Need help opening record sets with a split database
I'm trying to create a log in form for my database using a tutorial I found here. MS Access 2010 - How to create a login form in access - YouTube. I got it working fine, but when I split my database into a front end and back end file I could no longer retrieve the record set for this code. I recieve the error "Item not found in this collection" on the highlighted line of code below. I'm at a loss as to how I would retrieve the record set with a split end database. The code used in the log on button is below, any help is appreciated.
Private Sub submitButton_Click()
Dim dbs As Database
Dim rstUserPwd As Recordset
Dim bFoundMatch As Boolean
Set dbs = CurrentDb
Set rstUserPwd = dbs.OpenRecordset("qryUserPwd")
bFoundMatch = False
If rstUserPwd.RecordCount > 0 Then
Do While rstUserPwd.EOF = False
If rstUserPwd![UserID] = Me.userNameTextBox.Value And rstUserPwd![Password] = Me.passwordTextBox.Value Then
bFoundMatch = True
04-21-12, 16:55 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
Could I ask a dumb question?
why do you believe you need a log in form?
Personally I don't see the point in creating your own logon when the network username is available to you. adding the network userid to the workgroup file and then checking the user has permissions for the required option handles the security side
yes you still have to add a user to the relevant workgroup and make certain the permissions are right.
but you don't have to roll your own security, you dont' have to worry about passwords, changing and validating and so on.I'd rather be riding on the Tiger 800 or the Norton
04-21-12, 17:22 #3Registered User
- Join Date
- Mar 2012
Honestly I agree with you, this is actually a project for school where we're working for a local business to create a financial system for them to use. I don't see a need for log in security, but they do. While it's not critical that I get this to work, it's still something they want and expect at this point.
If I can't get it to work I'll live, but I'd like to be able to get it functional if at all possible.
04-22-12, 04:10 #4Moderator
Provided Answers: 14
- Join Date
- Mar 2009
1. There is no highlighted line in you post, so it's hard to know for sure the line on which the error occurs.
2. Check the objects:
a) Is the query "qryUserPwd" in the FE?
b) Can you open it without causing an error ? (i.e. can the query access the tables in the BE?).
c) Does the form actually have two Textboxes named "userNameTextBox" and "passwordTextBox"? (Check the spelling).
3. Since this is for a school project, here is a commented version of your code that points out several issues:
Private Sub submitButton_Click() ' ' There are at least 2 kinds of database objects (Database, RecordSet, etc.) ' that can be used in Access and both kinds do not have exactly the same ' properties and the same behaviour. It is then better to fully qualify ' the objects when you declare them: ' ' Dim dbs As Database ' Dim rstUserPwd As Recordset ' Dim dbs As DAO.Database Dim rstUserPwd As DAO.Recordset ' Dim bFoundMatch As Boolean Set dbs = CurrentDb ' ' By default, the OpenRecordset method will open a Dynaset type ' Recordset (which consumes more ressources) when used on a query ' or an attached table. This is not necessary in this case. ' ' Set rstUserPwd = dbs.OpenRecordset("qryUserPwd") ' ' A Snapshot type Recordset is sufficient as we shall not write any data: ' Set rstUserPwd = dbs.OpenRecordset("qryUserPwd", dbOpenSnapshot) ' In Basic, a variable is initialized when it is created. ' False ( = 0 ) is the initialization value for a boolean. ' This is then useless, as the value of bFoundMatch is already False. ' ' bFoundMatch = False ' ' This cannot work and will cause an error if there ' actually are no records into the Recordset. ' ' If rstUserPwd.RecordCount > 0 Then ' ' To test for the existence of records (rows), you must use: ' If rstUserPwd.BOF = False And rstUserPwd.EOF = False Then ' ' This instruction is useless: When opening a Recordset ' that contains records, the current record is already the first one. ' ' rstUserPwd.MoveFirst ' ' Looping in a RecordSet is a very inefficient method ' for searching for a row corresponding to a criteria. ' ' Do While rstUserPwd.EOF = False ' If rstUserPwd![UserID] = Me.userNameTextBox.Value And rstUserPwd![Password] = Me.passwordTextBox.Value Then ' bFoundMatch = True ' Exit Do ' End If ' rstUserPwd.MoveNext ' Loop ' ' Use the FindFirst method of the Recordset object instead: ' rstUserPwd.FindFirst "UserID = '" & Me.userNameTextBox.Value & "' And Password = '" & Me.passwordTextBox.Value & "'" If rstUserPwd.NoMatch = False Then bFoundMatch = True ' End If ' ' Note: You can get the same result in a single line: ' ---- ' bFoundMatch = CBool(DCount("*", "qryUserPwd", "UserID = '" & Me.userNameTextBox.Value & "' And Password = '" & Me.passwordTextBox.Value & "'")) ' End SubHave a nice day!