Function with Variable Column: Item not found in this collection 1

[DougTucker]

The following code is producing the error "Run time error 3265, Item not found in this collection". The comments sum up what the code is intended to do.

It produces the error when I pass the value through the function using the function RowsToColumns("Free Format Instructions"), and the variable in the code:

WholeString = rst!TextBlockName 'Column containing whole string

It works fine if I hard-code the column name:

WholeString = rst![Free Format Instructions]

I tried various syntax with and without quotes, brackets, etc. Any help would be appreciated. Here's the full function:

Function RowsToColumns(TextBlockName As String)
'Created by Doug Tucker on 7/20/2009
'This Function parses out individual rows in a single long text field
'of a specified table, and uses those rows to populate a series of columns
'in a table.  All records from the array are translated into columns
'until the end of the array, then the function advances to the next row
'and repeats the process until reaching the end of the table.
'INPUT "TextBlockName" is the name of the column to be parsed.
'OUTPUT columns begin with "TextBlockName", plus a squential number "0-n"
'All output columns (0-n) must already exist in the table.
'
Dim mydb As Database        'Current database
Dim rst As Recordset        'Read into recordset
Dim WholeString As String   'Contents of entire cell (multi-rows)
Dim StringArray             'Array of rows of WholeString
Dim RowContents As String   'Contents of a single row of the cell
Dim Check, Counter          'Variables for Do-Loop
Dim strDelimiter As String

strDelimiter = Chr(13) & Chr(10)    'Line Break, new Line

Set mydb = CurrentDb
'<DEV> - Verify table name if this changes in production (drop references to "v2")
Set rst = mydb.OpenRecordset("Select * from [MyDataTable] order by [Record ID]")
'
rst.Edit
Do While Not rst.EOF 'Outer Loop; continue through entire table; exit if EOF
    MoreRows = True: Counter = 0 'Initialize Variables
    'WholeString = Nz(rst.Fields![TextBlockName], "") 'Column containing whole string
    '<DEV NOTE:  Try without "Nz"...
    WholeString = rst!TextBlockName 'Column containing whole string
    If WholeString = "" Then
        MoreRows = False 'Stop immediately if array is empty
        Exit Do 'get out of inner loop immediately
        Else
        StringArray = Split(WholeString, strDelimiter)
        Do 'While MoreRows = True
        'Do While more rows exist in the array (Counter < UBound(StringArray)
            'Update data table with value from array
            RowContents = StringArray(Counter)
            rst.Edit
            rst![TextBlockName & Counter] = RowContents
            rst.Update
            If Counter = UBound(StringArray) Then   'This is the last value of array
                MoreRows = False
                Else 'Set value of flag to false
                Counter = Counter + 1
            End If
    'Exit Do         'Exit inner loop
    Loop Until MoreRows = False
    End If
    rst.MoveNext    'Go to next row of recordset;
Loop                'Exit when EOF

'<DEV MESSAGE>
MsgBox "File is Done"
rst.Close           'Close the recordset
Set rst = Nothing   'Empty the recordset
End Function

 

[joelflorendo]

try rst.Fields![TextBlockName]

 

[DougTucker]

I just tried that (rst.Fields![TextBlockName]) and it still produces the error. Do I need to store the whole string as a calculated value before I refer to it?

 

[joelflorendo]

put a

debug.print TextBlockName

at the start of the function and check the immediate window to see what value TextBlockName has and if it is indeed a valid field name.

 

[DougTucker]

Joel,

I added the debug, and when running "RowsToColumns("Free format instructions")", it shows that the variable [textblockname] does come into the function as "Free format instructions".

It's still getting hung up translating this from a string value to a column value. If I try to create a whole string (as FullFieldName = "rst![" & TextBlockName & "]"), it tells me that [WholeString] is the value in quotes (not the recordset value).

Any further ideas would be appreciated.

~ Doug T.

 

[joelflorendo]

ah, wasn't looking close enough...

field names with spaces need to be handled like this:
rsMyRecSet("[Field With Blanks]")

so I think you need to use:

rst("[" & TextBlockName & "]")

 

[DougTucker]

Thank you! That did it. Later I needed to save the data to a column [TextBlockName] + [Counter] (ex: "Free Format Instructions1" and I used the following:

RowContents = StringArray(Counter)
   rst.Edit
   rst("[" & TextBlockName & Counter & "]") = RowContents
   rst.Update

Thanks for your help.

I'm fussing with a null value issue now, but I think I can get that working by breaking out of the Do...Loop if a null [WholeString] is encountered.

~ Doug T.

 

[DougTucker]

I have this working well now. My next step is to call it from a macro, where I'll specify each of 4 column names (one at a time).

Here's the code for anyone else's use. Adjust as needed, especially the value "MyTableName".
Thank you all for your help.

~ Doug T.

Function RowsToColumns(TextBlockName As String)
'Created by Doug Tucker on 7/20/2009
'This Function parses out individual rows in a single long text field
'of a specified table, and uses those rows to populate a series of columns
'in a table.  All records from the array are translated into columns
'until the end of the array, then the function advances to the next row
'and repeats the process until reaching the end of the table.
'INPUT "TextBlockName" is the name of the column to be parsed.
'OUTPUT columns begin with "TextBlockName", plus a squential number "0-n"
'All output columns (0-n) must already exist in the table.
'
Dim mydb As Database        'Current database
Dim rst As Recordset        'Read into recordset
Dim WholeString As String   'Contents of entire cell (multi-rows)
Dim StringArray             'Array of rows of WholeString
Dim RowContents As String   'Contents of a single row of the cell
Dim Check, Counter          'Variables for Do-Loop
Dim strDelimiter As String
Dim MoreRows As Boolean

strDelimiter = Chr(13) & Chr(10)    'Line Break, new Line
Set mydb = CurrentDb
Set rst = mydb.OpenRecordset("Select * from [MyTableName] order by [Record ID]")

rst.Edit
Do While Not rst.EOF 'Outer Loop; continue through entire table; exit if EOF
    MoreRows = True: Counter = 0 'Initialize Variables
    Do While MoreRows = True
        WholeString = Nz(rst("[" & TextBlockName & "]"), "") 'Column containing whole string
        If WholeString = "" Then
            MoreRows = False 'Stop immediately if array is empty
            Exit Do 'get out of inner loop immediately
            Else
                StringArray = Split(WholeString, strDelimiter)
                'Update data table with value from array
                RowContents = StringArray(Counter)
                'CurFieldName = FullFieldName & Counter
                rst.Edit
                'rst!CurFieldName = RowContents
                rst("[" & TextBlockName & Counter & "]") = RowContents
                rst.Update
                If Counter = UBound(StringArray) Then   'This is the last value of array
                    MoreRows = False
                    Else 'Set value of flag to false
                    Counter = Counter + 1
                End If
        End If
    Loop 'Until MoreRows = False
    rst.MoveNext    'Go to next row of recordset;
Loop                'Exit when EOF

'<DEV MESSAGE>
'MsgBox "File is Done"
rst.Close           'Close the recordset
Set rst = Nothing   'Empty the recordset
End Function