×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Problem Using Variable as Field Name with DAO Recordset

Problem Using Variable as Field Name with DAO Recordset

Problem Using Variable as Field Name with DAO Recordset

(OP)
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(n).Name & vbCrLf
strFldNm = "FE_DbPath"

RstMasterT.AddNew
RstMasterT.Fields(strFldNm).Value = RstSoloT.Fields(strFldNm).Value 'Works!
RstMasterT.Fields(strFieldName).Value = RstSoloT.Fields(strFieldName).Value 'Item not found in this collection

Next n
End With

RstSoloT.MoveNext
End If

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

RE: Problem Using Variable as Field Name with DAO Recordset

Why vbCrLf in strFieldName?

combo

RE: Problem Using Variable as Field Name with DAO Recordset

ponder

CODE

...
strFieldName = .Fields(n).Name & vbCrLf
... 

---- Andy

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

RE: Problem Using Variable as Field Name with DAO Recordset

(OP)
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.

RE: Problem Using Variable as Field Name with DAO Recordset

(OP)
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(n).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

RE: Problem Using Variable as Field Name with DAO Recordset

Thanks for sharing your solution. wavey3

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



so we can see:

CODE

....
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close