Dynamic Field Names

[Guest]

I have a table that has 100 fields in it, named Field1, Field2, thru
Field100. I wish to write a loop to work with each field as opposed to
writing the same logic 100 times. Is there a way to do this? I am not much
luck. Here is a simple code snipet to just prove the logic via printing out
the content of each field in a MsgBox.

‘ * * * * * * start of Snipet example
Dim intField as Integer, strName as String

intField = 1
Do while intField < 100
strName = “Field†& CStr(intField)
Msgbox “Value of current field = “ & rcdTable!strName
intField = intField + 1
Loop

‘ * * * * * * end of logic

Thanks in advance for your help. I’ve been stuck on this all days…
Mike p.

 

[Jason Lepack]

rcdTable.fields(strName).value

 

[Steve]

99.99999999999 % of tables have less than twenty five fields. There's a
better chance to win the lottery than there is that your tables are
correctly designed. Before you go further, post back why your table has so
many fields.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Hi

You could spin through a loop:

Set rst = CurrentDb.OpenRecordset("your_table_name")
Set flds = rst.Fields
For Each fld In flds
... coding to amend each field goes here ....
... identify each field using fld.Name ........
... e.g.
Debug.Print fld.Name
Next

Cheers.

BW

 

[John W. Vinson]

I have a table that has 100 fields in it, named Field1, Field2, thru
Field100. I wish to write a loop to work with each field as opposed to
writing the same logic 100 times. Is there a way to do this? I am not much
luck. Here is a simple code snipet to just prove the logic via printing out
the content of each field in a MsgBox.

‘ * * * * * * start of Snipet example
Dim intField as Integer, strName as String

intField = 1
Do while intField < 100
strName = “Field” & CStr(intField)
Msgbox “Value of current field = “ & rcdTable!strName
intField = intField + 1
Loop

‘ * * * * * * end of logic

Thanks in advance for your help. I’ve been stuck on this all days…
Mike p.

As Steve says, it's a virtual certainty that this table is simply incorrectly
designed. I'm *hoping* that the purpose of your code is to migrate the data
into a properly normalized structure because it's coming from some non-normal
source that you can't control. If that's not the case, please post some
details about the reason for this "wide-flat" spreadsheet design and what your
code is intended to accomplish.

That said:

Dim intField As Integer
Dim strName As String
For intField = 1 To 100
Msgbox "Value of Field" & intField & ": " & rs.Fields("Field" & intField)
Next intField

should do what you want.

John W. Vinson [MVP]