Results 1 to 13 of 13
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Variable reference to a member of Recordset Collection

    In following Microsoft discussion pertaining to variable referencing in the Recordset collection, I've tried without success to solve the runtime error 91. While I did set the recordsets in order rsCol1,2,3 and 4, I was reluctant to use collection indexes 0-3 as a matter of principle avoiding "magic numbers" in code. If I replicate the four statements for each of the recordsets for each case in the Select, all goes well. I just didn't like seeing the replication. Any suggestions on how to reference a member of the collection?
    Code:
    Dim strRS As String
    
    
    On Error GoTo Err_Handler
    
    
    Select Case left(ChkBox, 4)
        Case "ck11": strRS = "rsCol1"
        Case "ck12": strRS = "rsCol2"
        Case "ck21": strRS = "rsCol3"
        Case "ck22": strRS = "rsCol4"
    End Select
    
    
    Me.Recordset(strRS).MoveFirst
    Me.Recordset(strRS).Edit
    Me.Recordset(strRS).Fields(ChkBox) = False
    Me.Recordset(strRS).Update
    
    snip



    These forms of assignment all failed as well:
    Code:
    CurrentDb.Recordsets("strRS").MoveFirst
    CurrentDb.Recordsets("strRS").Edit
    CurrentDb.Recordsets("strRS").Fields(ChkBox) = False
    CurrentDb.Recordsets("strRS").Update
    
    
    CurrentDb.Recordsets(0).MoveFirst
    CurrentDb.Recordsets(0).Edit
    CurrentDb.Recordsets(0).Fields(ChkBox) = False
    CurrentDb.Recordsets(0).Update
    Last edited by GraeagleBill; 06-21-2022 at 11:42 PM.

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    from this MS article https://docs.microsoft.com/en-us/off...collection-dao

    To refer to a Recordset object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:

    • Recordsets(0)
    • Recordsets("name")
    • Recordsets![name]
    Your using Me.Recordset. Your referencing the form recordset?

    I would think you would need recordsets, with an s, to refer to the recordsets collection.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Im confused as to what your asking. Are you wanting to use a variable to refere to a field (column) in a recordset?

    Something like:
    Me.Recordset.Fields("rsCol1") = False

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I missed the "s", but still no success. When I hard code the name of the recordset the assignment below works just fine.
    Code:
    rsCol1.Fields(ChkBox) = False
    If I try to reference the recordset using a variable to hold the name of the recordset I get a 3265 runtime error. As shown in the OP, strRS is a string variable whose current value at the time of the 3265 is "rsCol1".
    Code:
    CurrentDb.Recordsets(strRS).Fields(ChkBox) = False

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tested code from that MS Docs page.

    First of all, the OpenDatabase command fails so I just used CurrentDb to set a database object variable.

    Output of Debug.Print " " & .Name
    is whatever is in the OpenRecordset command. So in the example of
    OpenRecordset("Categories", dbOpenTable)
    the Name output is Categories not rstTable as you might expect. If there is an SQL statement then the first 256 characters would be the output (https://docs.microsoft.com/en-us/off...e-property-dao) - yes, I tested.

    So your code is not finding rsCol1 in the Recordsets collection.
    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.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    It would probably be helpful to see the whole procedure.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Recordsets Set when the form loads:
    Code:
    Private Sub Form_Load()
    
    
    (snip)
    
    
    Set rsCol1 = DBEngine(0)(0).OpenRecordset("tblCol1")
    Set rsCol2 = DBEngine(0)(0).OpenRecordset("tblCol2")
    Set rsCol3 = DBEngine(0)(0).OpenRecordset("tblCol3")
    Set rsCol4 = DBEngine(0)(0).OpenRecordset("tblCol4")
    
    
    Call LoadAndInit
    
    
    (snip)
    
    
    End Sub
    The various names corresponding to the form's check box controls and LIKE table names are generated. Initialization process sets all check box controls and their corresponding table fields to False.
    Code:
    Private Sub SetTable(ChkBox As String)
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    '  From the name of the check box control, we can determine which column and
    '  hense which table and field needs to be initialized.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim strRS As String
    
    
    On Error GoTo Err_Handler
    
    
    Select Case left(ChkBox, 4)
        Case "ck11": strRS = "rsCol1"
        Case "ck12": strRS = "rsCol2"
        Case "ck21": strRS = "rsCol3"
        Case "ck22": strRS = "rsCol4"
        Exit Sub
    End Select
    
    
    'Debug.Print CurrentDb.Recordsets![strRS]            'See if found in collection
    'Debug.Print CurrentDb.Recordsets("strRS")           'See if found in collection
    Debug.Print CurrentDb.Recordsets(0)                   'See if found in collection
    
    
    
    
    CurrentDb.Recordsets![strRS].MoveFirst
    CurrentDb.Recordsets![strRS].Edit
    CurrentDb.Recordsets![strRS].Fields(ChkBox) = False
    CurrentDb.Recordsets![strRS].Update
    
    
    Me.Controls(ChkBox) = False
    If Me.Dirty Then Me.Dirty = False
    
    
    Exit_Handler:
       Exit Sub
    Err_Handler:
       MsgBox "Error " & Err.Number & " attempting to initialize column table : " & Err.Description
       Resume Exit_Handler
       
    End Sub
    As June7 has pointed out, the desired recordsets are not found within the Recordsets collection. Hopefully, someone will come along and spot the problem.

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    This version of the Sub works perfectly. But again, the replication of update code for lack of accessing the Recordsets collection to obtain the desired Recordset is not generally acceptable. If anyone can come up with the proper statement to access the recordset via the recordsets collection, I'll be happy to try it.
    Code:
    Private Sub SetTable(ChkBox As String)
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    '  From the name of the check box control, we can determine which column and
    '  hense which table and field needs to be initialized.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    On Error GoTo Err_Handler
    
    
    Select Case left(ChkBox, 4)
        Case "ck11"
            rsCol1.MoveFirst
            rsCol1.Edit
            rsCol1.Fields(ChkBox) = False
            rsCol1.Update
            
        Case "ck12"
            rsCol2.MoveFirst
            rsCol2.Edit
            rsCol2.Fields(ChkBox) = False
            rsCol2.Update
            
        Case "ck21"
            rsCol3.MoveFirst
            rsCol3.Edit
            rsCol3.Fields(ChkBox) = False
            rsCol3.Update
            
        Case "ck22"
            rsCol4.MoveFirst
            rsCol4.Edit
            rsCol4.Fields(ChkBox) = False
            rsCol4.Update
    End Select
    
    
    Me.Controls(ChkBox) = False
    If Me.Dirty Then Me.Dirty = False
    
    
    Exit_Handler:
       Exit Sub
    Err_Handler:
       MsgBox "Error " & Err.Number & " attempting to initialize column table : " & Err.Description
       Resume Exit_Handler
       
    End Sub

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I am all for optimising code, but how long do you want to spend wasting your time, when you have something that works now.

    IF you ever find out how to get it working, you can go back and change it.
    I can't help thinking when you get the chkbox value then set the record set, and just use the one? But that is another approach.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Code:
    IF you ever find out how to get it working, you can go back and change it
    Yes, that's exactly where I am with this caper and exactly what I intend to do. I'm just leaving the door open for now.

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Admittedly I'm not familiar with the Currentdb.recordsets collection. But without verifying it looks to me like you opened your own recordsets as global variables however I'm guessing they're not automatically added to this .recordsets collection. OR maybe your recordset variables weren't global or they'd fallen out of scope and had been closed?

    Anyway, I made some *really quick and dirty* test code that seems to demonstrate what I think you're after:

    Code:
    Dim rsCol1 As DAO.Recordset
    Dim rsCol2 As DAO.Recordset
    Dim rsCol3 As DAO.Recordset
    Dim rsCol4  As DAO.Recordset
    
    
    Public Sub test()
        Set rsCol1 = DBEngine(0)(0).OpenRecordset("SELECT * FROM test_table WHERE ID In(1,2);")
        Set rsCol2 = DBEngine(0)(0).OpenRecordset("SELECT * FROM test_table WHERE ID In(3,4);")
        Set rsCol3 = DBEngine(0)(0).OpenRecordset("SELECT * FROM test_table WHERE ID In(5,6);")
        Set rsCol4 = DBEngine(0)(0).OpenRecordset("SELECT * FROM test_table WHERE ID In(7,8);")
        
        
        Call test2("ck22")
        
    End Sub
    
    Public Sub test2(ChkBox As String)
    On Error GoTo Err_Handler
        Dim rs As DAO.Recordset
        
        Select Case Left(ChkBox, 4)
            Case "ck11": Set rs = rsCol1
            Case "ck12": Set rs = rsCol2
            Case "ck21": Set rs = rsCol3
            Case "ck22": Set rs = rsCol4
            
        End Select
        
        rs.MoveFirst
        rs.Edit
        rs.Fields("Field1") = "Hello, world"
        rs.Update
    
    Exit_Handler:
        Set rs = Nothing
        Exit Sub
       
    Err_Handler:
        MsgBox "Error!!!"
        Resume Exit_Handler
    
    End Sub
    Is there any reason you need to keep this rsColX open all the time? You could just do
    Code:
    Select Case Left(ChkBox, 4)
            Case "ck11": Set rs = DBEngine(0)(0).OpenRecordset("tblCol1")
            Case "ck12": Set rs = DBEngine(0)(0).OpenRecordset("tblCol2")
            Case "ck21": Set rs = DBEngine(0)(0).OpenRecordset("tblCol3")
            Case "ck22": Set rs = DBEngine(0)(0).OpenRecordset("tblCol4")
    End Select
    OR

    Code:
    Select Case Left(ChkBox, 4)
            Case "ck11": strRS = "tblCol1"
            Case "ck12": strRS = "tblCol2"
            Case "ck21": strRS = "tblCol3"
            Case "ck22": strRS = "tblCol4"
    End Select
    
    set rs = DBEngine(0)(0).OpenRecordset(strRS)

    Also, you might want to check that the recordset has records before trying to move in it:
    Code:
    If Not (rs.BOF and rs.EOF) Then
        'now it's safe to move in recordset
    else
        'the recordset is empty... trying to move through it will throw an error
    End if
    http://allenbrowne.com/ser-29.html

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    (Edit: What June7 said in post #5)

    I did a little more digging and found that the name assigned to the name property of a recordset, within the recordset collection, is the db object's name or the sql query used to open the recordset. (that's a mouthful) In otherwords the name property value does not equal the variable name.

    For example your code
    Code:
    Set rsCol1 = DBEngine(0)(0).OpenRecordset("tblCol1")
    Would have added a recordset with a name property equal to "tblCol1" to the db's recordset collection, NOT "rsCol1"

    If your code looked like this
    Code:
    Set rsCol1 = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblCol1")
    then the name property would equal "SELECT * FROM tblCol1".

    So *I think* you may would want something like: (untested)
    Code:
    Select Case left(ChkBox, 4)
        Case "ck11": strRS = "tblCol1"
        Case "ck12": strRS = "tblCol2"
        Case "ck21": strRS = "tblCol3"
        Case "ck22": strRS = "tblCol4"
        Exit Sub
    End Select
    
    
    
    Debug.Print CurrentDb.Recordsets(strRS).Name           'See if found in collection

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    What is "rsCol1" ?

    Is it a table? A saved Query?, a string like "Select * from SomeTable"?

    I can iterate through the recordsets collection and get the names but it seems you have to have a name.

    Code:
    Sub test()
    
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim rs2 As DAO.Recordset
        Dim rs3 As DAO.Recordset
        Dim r As Variant
        Dim x, y, z
    
    
        x = "Query1"
        y = "tblStaff"
        z = "tblPeople"
    
    
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(x)
        Set rs2 = db.OpenRecordset(y)
        Set rs3 = db.OpenRecordset(z)
    
    
        For Each r In db.Recordsets
            Debug.Print r.Name
        Next
    
    
    MyExit:
        If Not rs Is Nothing Then rs.Close: Set rs = Nothing
        If Not rs2 Is Nothing Then rs2.Close: Set rs2 = Nothing
        If Not rs3 Is Nothing Then rs3.Close: Set rs3 = Nothing
        Set db = Nothing
    End Sub
    OutPut:
    Code:
    Query1
    tblStaff
    tblPeople
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Reference to form by variable
    By jaryszek in forum Programming
    Replies: 26
    Last Post: 05-25-2018, 05:05 PM
  2. Replies: 4
    Last Post: 10-03-2017, 09:36 AM
  3. Variable to reference a spreadsheet from Access
    By lawdy in forum Programming
    Replies: 9
    Last Post: 03-03-2015, 11:31 AM
  4. The requested member of the collection does not exist.
    By murfeezlaw in forum Programming
    Replies: 2
    Last Post: 07-12-2013, 07:24 AM
  5. Reference recordset of different form
    By tylerg11 in forum Forms
    Replies: 3
    Last Post: 07-09-2013, 10:35 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