Results 1 to 3 of 3
  1. #1
    ProjectHelp is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    6

    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. http://www.youtube.com/watch?v=_Kdr_L_DFOI. 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
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I open recordsets from linked tables all the time. Not an issue.

    I most often use (requires a VBA reference to Microsoft ActiveX Data Objects 2.8 Library):
    Dim rs AS ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM tablename;", CurrentProject.Connection, adOpenDynamic, acLockPessimistic

    And similar to your code (might need VBA reference to Microsoft DAO 3.6 Object Library):
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tablename;", dbOpenSnapshot)

    And another:
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone

    However, an alternative to all the recordset code is a simple DLookup.

    If Not IsNull(DLookup("UserID","tablename","UserID='" & Me.userNameTextBox & "' And [Password]='" & Me.passwordTextBox & "'")) Then
    'do something like open another form
    Else
    MsgBox "Invalid entry. Try again."
    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ProjectHelp is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    6
    I wound up using the DLookup solution, and it works like a charm. Thanks for the help, it's much appreciated!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Split Record Query
    By Flippa in forum Queries
    Replies: 1
    Last Post: 12-15-2011, 05:27 PM
  2. Replies: 9
    Last Post: 09-16-2011, 03:52 PM
  3. Replies: 6
    Last Post: 05-04-2011, 06:17 PM
  4. Replies: 10
    Last Post: 03-28-2011, 08:57 AM
  5. Split Database
    By smikkelsen in forum Database Design
    Replies: 4
    Last Post: 04-16-2010, 06:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums