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