refer to a field name with a variable

[Guest]

Thanks for taking the time to read my question.

I have a table with field names like Field1, Field2, Field3 etc.

I want to refer to each field in code and add a value to each.

Here is my code:

Do Until rst.EOF
CurrentPyramid = rst!SowBarn

Do Until CurrentPyramid <> rst!SowBarn
With rst2
.AddNew
!Barn & x = rst!FeederBarn
.Update
x = x + 1
End With
rst.MoveNext
Loop
Loop

Where x is an integer, how do refer to the field name properly? I tried
!Controls("Field" & x) = rst!FeederBarn but that didn't work. I don't think
the Controls part is correct.

Any ideas?

Brad

 

[Dirk Goldgar]

Thanks for taking the time to read my question.

I have a table with field names like Field1, Field2, Field3 etc.

I want to refer to each field in code and add a value to each.

Here is my code:

Do Until rst.EOF
CurrentPyramid = rst!SowBarn

Do Until CurrentPyramid <> rst!SowBarn
With rst2
.AddNew
!Barn & x = rst!FeederBarn
.Update
x = x + 1
End With
rst.MoveNext
Loop
Loop

Where x is an integer, how do refer to the field name properly? I
tried !Controls("Field" & x) = rst!FeederBarn but that didn't work.
I don't think the Controls part is correct.

You're close, Brad. On a form, a reference to the Me.Controls("Field" &
x) would work. But a recordet doesn't have a Controls collection; it
has a Fields collection instead. Use this:

.Fields("Barn" & x) = rst!FeederBarn

 

[Guest]

Perfect!

Thank you for the enlightenment

Have a great weekend!

Brad

 

[Steven]

Hello,

I have a similiar question and am curious to see if you can help.

I'm trying to assign variables the field name to later use to populate an
HTML table that is embedded into an e-mail.

Basically look at doing a loop to put the field names into a variable. If
there is a way to look at a query, determine the number of fields, and assign
that number to a variable to determine the loop count that would be great
also.

so - do all the normal RecordSet stuff and get a query that has, say, five
fields...
qryTest with field names, "name","Address", "Zip", "Phone", "misc"

dim all the variable but perhaps use an array (although I haven't done that
before)...

loop through the count of fields and assign to variable. let's set the
variables to assign the field names in the Dim property but if you want to
add an array set that would be great....

Dim Field1 as string, Field2 as string, Field3 as string, Field4 as string,
Field5 as string
Dim iNumberofFields as integer, x as integar

iNumberofFields = 5

for x = 1 to iNumberofFields

With RS
.Fields("Field" & x) = rst!FeederBarn
next x

The goal: assign field names to variables
Field1 = name
Field2 = Address
Field3 = Zip
Field4 = Phone
Field5 = misc

Later I would populate a table that would assign the information in a table to

The below information would be declared above in code as well but breviated
for this communication:
sColumn1Data = rs(Field1)
sColumn2Data = rs(Field2)
ect....This is later used in html coding added in to present in a table
layout for the body of an e-mail.

I can send the full script that I'm using if it helps as well as a screen
shot of the e-mail output.

Thank you.

 

[RoyVidar]

Hello,

I have a similiar question and am curious to see if you can help.

I'm trying to assign variables the field name to later use to
populate an HTML table that is embedded into an e-mail.

Basically look at doing a loop to put the field names into a
variable. If there is a way to look at a query, determine the
number of fields, and assign that number to a variable to determine
the loop count that would be great also.

so - do all the normal RecordSet stuff and get a query that has, say,
five fields...
qryTest with field names, "name","Address", "Zip", "Phone", "misc"

dim all the variable but perhaps use an array (although I haven't
done that before)...

loop through the count of fields and assign to variable. let's set
the variables to assign the field names in the Dim property but if
you want to add an array set that would be great....

Dim Field1 as string, Field2 as string, Field3 as string, Field4 as
string, Field5 as string
Dim iNumberofFields as integer, x as integar

iNumberofFields = 5

for x = 1 to iNumberofFields

With RS
.Fields("Field" & x) = rst!FeederBarn
next x

The goal: assign field names to variables
Field1 = name
Field2 = Address
Field3 = Zip
Field4 = Phone
Field5 = misc

Later I would populate a table that would assign the information in a
table to

The below information would be declared above in code as well but
breviated for this communication:
sColumn1Data = rs(Field1)
sColumn2Data = rs(Field2)
ect....This is later used in html coding added in to present in a
table layout for the body of an e-mail.

I can send the full script that I'm using if it helps as well as a
screen shot of the e-mail output.

Thank you.

Check out the following little routine, which probably contains
the elements you need, if I'm reading correct.

Dim rs As DAO.Recordset
Dim idx As Long

Set rs = CurrentDb.OpenRecordset("YourQuery")

For idx = 0 To rs.Fields.Count - 1
Debug.Print rs.Fields(idx).Name,
Next idx
Debug.Print
Do While Not rs.EOF
For idx = 0 To rs.Fields.Count - 1
Debug.Print rs.Fields(idx).Value,
Next idx
Debug.Print
rs.MoveNext
Loop