Problem Using Variable as Field Name with DAO Recordset 1

[xweyer]

This code is intended to pull the data from T_LinkedTableInfoCFC (and ultimately other tables of the same design) and compile its contents into T_MasterLinkedTableFields. To process all the fields the code loops through the "Master" table and extracts the name of each field. Everything works as expected until the highlighted line. Note that the line directly above it where the strFldName variable is set directly works and does not produce an error. Also note the information from the Immediate Window included at the bottom in which the contents of the two variables as displayed appear to be equivalent but.. ?strFieldName = strFldNm evaluates as False. Based on search results the "RstMasterT.Fields(strVariable).Value" syntax seems correct but I can't understand why it doesn't work when the string variable is set to the field name as pulled from the recordset but does when set directly to ?equivalent? text.

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim RstMasterT As DAO.Recordset
Dim RstSoloT As DAO.Recordset
Dim strFieldName As String
Dim strFldNm As String
Dim strDocName As String
Dim StrDocName2 As String
Dim n As Long

DoCmd.SetWarnings False
strDocName = "T_LinkedTableInfoCFC" 'test table
StrDocName2 = "T_MasterLinkedTableFields" 'The table used to collect all the info from the above tables

DoCmd.RunSQL "Delete * from " & StrDocName2 'Empty the master table

Set dbs = CurrentDb
Set RstSoloT = dbs.OpenRecordset(strDocName, dbOpenDynaset)
Set RstMasterT = dbs.OpenRecordset(StrDocName2, dbOpenDynaset)

If Not (RstSoloT.EOF And RstSoloT.BOF) Then 'RstSoloT is not empty
RstSoloT.MoveLast
RstSoloT.MoveFirst

With RstMasterT
For n = 0 To .Fields.Count - 1 'Cycle through the field names in rstLinked recordset
strFieldName = .Fields.Name & vbCrLf
strFldNm = "FE_DbPath"

RstMasterT.AddNew
RstMasterT.Fields(strFldNm).Value = RstSoloT.Fields(strFldNm).Value 'Works!
[highlight #FCE94F]RstMasterT.Fields(strFieldName).Value = RstSoloT.Fields(strFieldName).Value 'Item not found in this collection[/highlight]

Next n
End With

RstSoloT.MoveNext
End If

IMMEDIATE WINDOW
?strFieldName
FE_DbPath
?strFldNm
FE_DbPath
?strFieldName = strFldNm
False
?strFldNm = Cstr(strFieldName)
False

 

[combo]

Why vbCrLf in strFieldName?

combo

 

[Andrzejek]

...
strFieldName = .Fields(n).Name [highlight #FCE94F]& vbCrLf[/highlight]
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[xweyer]

Thanks to both combo and Andrzejek. That was the issue. The " & vbCrLf" was a vestige of a line I copied into the code and then was blind to afterwards. Duh. Thanks for the eyeopener.

 

[xweyer]

Just in case anyone is interested here's the completed cleaned up code. It just loops through a collection of tables (with a common structure) and their fields that in this instance contain information on various databases and complies them all into a single master table.

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim RstMasterT As DAO.Recordset
Dim RstSoloT As DAO.Recordset
Dim strTableName As String
Dim strFieldName As String
Dim strDocName As String

Dim n As Long

DoCmd.SetWarnings False
strDocName = "T_MasterLinkedTableFields" 'The table used to collect all the info from the above tables

DoCmd.RunSQL "Delete * from " & strDocName 'Empty the master table

Set dbs = CurrentDb
Set RstMasterT = dbs.OpenRecordset(strDocName, dbOpenDynaset)

'to process all T_LinkedTableInfo[TableName]
For Each tdf In dbs.TableDefs 'loop through all the tables

If Left(tdf.Name, 8) = "T_Linked" And (Left(tdf.Name, 4) <> "Msys") Then 'exclude system and security tables
Debug.Print tdf.Name
strTableName = tdf.Name
Set RstSoloT = dbs.OpenRecordset(strTableName, dbOpenDynaset)

If Not (RstSoloT.EOF And RstSoloT.BOF) Then 'RstSoloT is not empty
RstSoloT.MoveLast
RstSoloT.MoveFirst
Do Until RstSoloT.EOF = True

With RstMasterT
RstMasterT.AddNew
For n = 0 To .Fields.Count - 1 'Cycle through the field names in rstLinked recordset
strFieldName = .Fields.Name
RstMasterT(strFieldName).Value = RstSoloT(strFieldName).Value
Next
RstMasterT.Update

End With
RstSoloT.MoveNext
Loop

End If
End If
Next tdf

If Not RstMasterT Is Nothing Then
RstMasterT.Close
End If

If Not RstSoloT Is Nothing Then
RstSoloT.Close
End If

Set tdf = Nothing
Set RstSoloT = Nothing
Set RstMasterT = Nothing
Set dbs = Nothing

DoCmd.SetWarnings True

 

[Andrzejek]

Thanks for sharing your solution.

Just for future reference, it would be nice if you would format your code as CODE,

so we can see:

....
For n = 0 To .Fields.Count - 1 'Cycle through the field names in rstLinked recordset
    strFieldName = .Fields(n).Name
    RstMasterT(strFieldName).Value = RstSoloT(strFieldName).Value
Next
....

Use Preview before posting.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson