Searching for Specific Recordsets

[Sorrells]

I have a form with about 10 recordsets. Of them 7 are named rstTemp_1, rstTemp_2 on to rstTemp_7. They are temporary RowSources for listboxes in the form.

In using the form, one, two up to all of the listboxes may have data placed in them. I am looking for a method to loop through all the open recordsets for a match to the string "rstTemp_" so that I can then check it for data and if present, manipulate it.


I've run across two MS Access97 Help examples but in my class module, Access gives me an error and I can understand because in the examples the recordset are dimensioned but not set. The Access code snippet is like this:

Dim dbs As Database, rst As Recordset

For Each rst In dbs.Recordsets
Debug.Print rst.Name; " "; rst.Updatable
Next rst

My test code is as follows:
Dim rstLoop As Recordset
For Each rstLoop In dbs.Recordsets
If Left(rstLoop.name, 8) = "rst_Temp" Then
Debug.Print rstLoop.name
End If
Next rstLoop

Interestingly, I don't get an error, the FOR statement is executed and the entire IF statement is jumped.

I welcome any advice offered.

 

[shay]

Hi

You forgot to assign your database ie Set dbs = OpenDatabase ...

Other than that I don't see anything else wrong. I wouldn't expect the IF statement to be executed if no recordsets had been set.

shay

 

[Sorrells]

Shay,

Actually I did much worse than that. I am beginning to understand more and more what the word 'object' means in object programming! The actual Microsoft code had 2 FOR loops and two WITH statements.
Although I had SET the database in the code (but did not inform of such in this posting ), the main reason the IF statement was not executed was because I had not SET any recordsets. I assumed that they were still open from the form's OnOpen Event, although I was in a different procedure.
In fact, I don't know how to get around this. Would a PRIVATE function keep them open and in scope of all module procedures? Currently with my now obviously incorrect assumption, I am not actually closing the recordsets until the form's OnClose event. Now I wonder if I am creating memory holes. But I digress.

The following code is working for me. With the loops below working, I intend to do some data updates on specific fields of the recordsets within the inner loop. I think it may be possible now.

=================================

Dim dbs As DATABASE
Dim NameProp As Property

Dim rst_Task_Records As Recordset
Dim rstLoop As Recordset
Set dbs = CurrentDb

Set rst_temp1 = dbs.OpenRecordset("Day_Table_1", dbOpenDynaset)
Set rst_temp2 = dbs.OpenRecordset("Day_Table_2", dbOpenDynaset)
Set rst_temp3 = dbs.OpenRecordset("Day_Table_3", dbOpenDynaset)
Set rst_temp4 = dbs.OpenRecordset("Day_Table_4", dbOpenDynaset)

With dbs
For Each rstLoop In .Recordsets
With rstLoop
For Each NameProp In rstLoop.Properties
If Left(.name, 10) = "Day_Table_" Then
Debug.Print rstLoop.name
'
' I will update fields in here
'
End If
Next NameProp
End With
Next rstLoop
End With

==============================

I still find it interesting that rstLoop does not have to be set. The Debug.Print shows up in the Debug Window.

Thanks for your help!

 

[Sorrells]

Final Touches on the Loop Code

I had lines not needed and although the code was looping, it was looping through properties until it came to a listbox with no data. In other words, it did not work right. The loop code below correctly finds all of the 7 "Day_Table_" tables, and then exits the loop gracefully.

With dbs
For Each rstLoop In .Recordsets
With rstLoop
If Left(.name, 10) = "Day_Table_" Then
Debug.Print rstLoop.name
End If
End With
Next rstLoop
End With