By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Determine Fieldname to be used on the fly

P: 48

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 ?

Jan 20 '09 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,287
I believe you can use the Fields collection of the TableDef, but I haven't tried it. Something like:
Expand|Select|Wrap|Line Numbers
  1. Dim index as integer
  2. Dim tbl as DAO.TableDef
  3. Dim fld as DAO.Field
  4. Dim fldNamePart2 as String
  5. index = 0
  6. Set tbl = CurrentDb.TableDefs(tableName)
  7. Set fld = tbl.Fields(index)
  8. fldNamePart2 = Right(fld.Name, len(fld.Name)-5)
Also if you have a RecordSet you can do (have used this)
Expand|Select|Wrap|Line Numbers
  1. Dim records as Object
  2. Dim strContent as String
  3. Set records = DBEngine(0)(0).OpenRecordset(strSQL)
  4. strContent = records.Fields(index)   'like index=0 is the first column in the table
Jan 21 '09 #2

Expert 100+
P: 1,923
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.
Jan 21 '09 #3

Expert Mod 15k+
P: 31,492
The following code assumes you have fields named [FieldXXX] where XXX may be of variable length :
Expand|Select|Wrap|Line Numbers
  1. Dim str2ndPart As String
  2. Dim tdfThis As DAO.TableDef
  3. Dim fldThis As DAO.Field
  5. Set tdfThis = CurrentDB.TableDefs("YourTable")
  6. For Each fldThis In tdfThis.Fields
  7.   With fldThis
  8.     If Left(.Name, 5) = "Field" Then
  9.       str2ndPart = Mid(.Name, 6)
  10.       'Rest of what you need to do
  11.       ...
  12.     End If
  13.   End With
  14. Next tdfThis
I'm a bit confused by the rest of your explanation to be honest.
Jan 21 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.