Dynamic field and table names

[mcucino]

Hi All, I am sure there is a way to do this but I can't figure it out. I need to refer to a variable instead of (A) a field name in recordsets and (B) a table name tabledefs. Help on either (A) or (B) would be great. Here's the code:

Private Sub UpdateFields()
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim db As DAO.Database
dim TableB as String 'or dao.table or something - I have no idea
dim FieldName as string 'or dao.field or something


Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from TableA")
TableB = "LineItemsT"
    For Each fld In db.TableDefs!TableB.Fields  'TableB needs to be a variable
        rs.MoveFirst
        Do While Not rs.EOF
            rs.Edit
            FieldName = fld.name
            rs!FieldName = 12345 'FieldName needs to be variable that points to a field in the rs with the same field name as fld.name from the TableB
            rs.Update
            rs.MoveNext
        Loop
    Next


rs.Close
Set rs = Nothing


End Sub

[ssanfu]

So what are you trying to do???? Copy the values from TableA (rs) into a single field in TableB??

[kd2017]

To refer to a TableDef object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:
TableDefs(0)
TableDefs("name")
TableDefs![name]
https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/tabledef-object-dao

To refer to a Field object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:
Fields(0)
Fields("name")
Fields![name]
https://docs.microsoft.com/en-us/off...eld-object-dao

[mcucino]

I was hoping no one would ask that... ha ha. I'm really setting rs!fieldname = somecustomfunction(with,lots,of,parameters) that would take way too long in an action query and I didn't want that to further confuse the question

[mcucino]

kd2017 - thank you! the tabledefs worked great, but it's not working for the field name in the context of updating a recordset. I tried this:

            fn = "[" & fld.Name & "]"
            rs!Fields(fn) = 1

to no avail

[kd2017]

Tried it without the square brackets? Also, use a dot instead of an exclamation point: rs.fields(fn)

[mcucino]

YASSSS thanks kd2017!