Private Sub Command_Test_Click()
Dim rst As DAO.Recordset
Dim Field As DAO.Field
Dim strFields As String
Dim strSQL As String
Dim i As Integer
For Each Field In Me.Recordset.Fields
If .OrdinalPosition = 5 Then
If Len(strFields) Then strFields = strFields & ", "
strFields = strFields & .name
strSQL = "SELECT " & strFields & " FROM (" & Me.RecordSource & ")"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Do Until .EOF
For i = 0 To rst.Fields.Count - 1
Debug.Print rst.Fields(i).name, rst.Fields(i).Value
Set rst = Nothing
The problem is that the form is based off of a complex query that can take a while to run. So I don't want to have to run the query again with just a slimmed down Fields list; I just want to copy a single column from a recordset that has ALREADY been created and use that as my recordset (then I'll loop through and do stuff with it).
When I create a recordsetClone, it grabs all 50+ columns, and I only need one. So when I loop through the recordset it takes longer than necessary. So I'd like to either delete these unnecessary fields, or create the rs without them in the firstplace before I loop through.
Maybe if I try to give a little more explanation it might help: I have a form with a subform with a bunch of data in it (about 50 fields of 20k records or so). One of these fields is a memo field. I need to loop through all of these fields for all the records and concatenate them together. The query that populates this subform is based on a bunch of use-input and is fairly complex. So I'd like not to have to requery for just the one field, but rather use the recordset that has already been created.
I can do this using the recordsetclone of the subform and looping through, the problem is when I do a rs.movenext in a "do while not rs.eof" type loop, it fetches all the fields for the next record, thus unnecessarily slowing down the looping process. So I'd like to simplify the recordset down to just the column I need before I start looping. How do I do this?