Refer to a field name with a variable

[mmogul]

I wish to assign a value to text field on a form. The field names on the form go from f1 to f10. Depending on the value of a variable x which can range from 1 to 10, a value gets assigned to the field.

example: if x=3 then value y is assigned to field f3

How do I refer to the field programmatically?

 

[PWise]

me("f" & x)=x

 

[mmogul]

thanks. that was quick.

i had been trying me.("f" & x) -- but that was giving a compile error. I'm close now. The formula works but the value only stays in the last control (if there were 8 records, only the 8th field has a value). Here is my code.

With rs
If Not .EOF Then
Do Until .EOF
Set txt = Me.Controls("txt" & cnt)
txt.ControlSource = !FieldID

txt.Width = !FieldWidth * 1440
txt.Top = 0.3333 + (0.25 * cnt) * 1440
txt.Visible = True
txt.TabIndex = cnt

Set lbl = Me.Controls("lbl" & cnt)
lbl.Top = 0.3333 + (0.25 * cnt) * 1440
lbl.Caption = !Caption

*** this is the section with the problem ***
Me("field" & cnt) = !FieldID
cnt = cnt + 1

.MoveNext
Loop
Else
End If



End With

 

[PWise]

but that was giving a compile error

What Error


Me("field" & cnt) = !FieldID

what is this control

 

[mmogul]

Field1 (calculated as me("field" & cnt) = !Field

refers to a text box on the form

As background, in my app I let the user define the fields they can use on an entry form. I am using a form that starts with a series of txt and label controls that are unbound and set as invisible. Based on another table, certain fields are displayed.

I am attempting to write the name of the selected field in the text box named "Field1 (where 1 is the counter value. Knowing this value will then allow me to write out the user entered value to the proper field in the appropriate table.

 

[MajP]

PWise's original reply was incorrect

Every control added to a form becomes both a property of a form and a member of the control collection

thus for a control you can do something like because you are using a variable as an index for a collection
me("ctl"&count)

The only reason you can do that is that this is short hand for the following
me.controls.item("ctl"&count).value
vb has default methods and property and you are not required to use them
Value is the default property of a control
Item is the default property of a collection
and the controls collection is the default property of a form.
Leaving you with
Me("ctl"&count)

When you add a recordsource to a form each field is added as a property of the form. But for some reason the form does not have a Fields collection, probably because its recordsource does have a field collection.

Thus you cannot do
Me("fld"&count)
You could do
Me.recordset.fields.item("fld"&count)
and since the fields collection is the default of the recordset object you could do
Me.recordset("fld"&count)

Bottom line you can not do
object.variable
can do
object.collection(variable)

 

[mmogul]

Thanks so much for taking the time to present this explanation!

 

[PWise]

MajP:

i did missread mmogul question I thought that he wants to put x in fieldx after rereading i see that he wants to put y in fieldx

then it sb

me("f" & x)=y

 

[MajP]

only if the controls have the same name as the field.
me("f"&x)=y
is shorthand only for
me.controls.item("f"&x)

This often works because people often do not change the name of the fields and controls.