Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: Need help opening record sets with a split database

    Hi All,

    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
    rstUserPwd.MoveFirst

    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

    End If

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  3. #3
    Join Date
    Mar 2012
    Posts
    4
    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.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:
    Code:
    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 Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •