Determine Fieldname to be used on the fly

[wquatan]

Hi

For a parsing routine with more than 100 fields, I'm looking a way to get values out of Fields by determining the Fieldname on the fly.

As a simple example :
Let's assume the following

FieldABC = 123
FieldDEF = "sometext"
FieldXYZ = 987

What I would like to do

Somehow the second part of the FieldName is determined :
FieldNamePart2 = "DEF"

Then the value of that Field is used
TargetField = content "Field" & FieldNamePart2

TargetField contains "sometext", the value of FieldDEF

I would like to know too the same from databasefields
TargetField = content [tablename].["Field" & FieldNamePart2]


Is something like this achievable in VBA ?

Thx

[ChipR]

I believe you can use the Fields collection of the TableDef, but I haven't tried it. Something like:

Dim index as integer
Dim tbl as DAO.TableDef
Dim fld as DAO.Field
Dim fldNamePart2 as String
index = 0
Set tbl = CurrentDb.TableDefs(tableName)
Set fld = tbl.Fields(index)
fldNamePart2 = Right(fld.Name, len(fld.Name)-5)

Also if you have a RecordSet you can do (have used this)

Dim records as Object
Dim strContent as String
Set records = DBEngine(0)(0).OpenRecordset(strSQL)
strContent = records.Fields(index)   'like index=0 is the first column in the table

[puppydogbuddy]

the only thing that comes to mind is to create a combobox/listbox whose row source is based on a field list, table or value list that includes the field name and field value as columns. If you provide more details on the purpose of the application and how you envision it working, we may be able to help you more.

[NeoPa]

The following code assumes you have fields named [FieldXXX] where XXX may be of variable length :

Dim str2ndPart As String
Dim tdfThis As DAO.TableDef
Dim fldThis As DAO.Field

Set tdfThis = CurrentDB.TableDefs("YourTable")
For Each fldThis In tdfThis.Fields
  With fldThis
    If Left(.Name, 5) = "Field" Then
      str2ndPart = Mid(.Name, 6)
      'Rest of what you need to do
      ...
    End If
  End With
Next tdfThis

I'm a bit confused by the rest of your explanation to be honest.