Specifiy a form control from a variable in MS Access VBA 1

[gdlkm]

Hi,
Having a little trouble trying to use a variable to specify a form control.

In a form I have 40 text fields which are named num1, num2,...num40.
In my code I have a dlookup command that then looks for the occurence of the number 1 within a table and updates num1 with the count. I want to repeat this for the forty text fields ( i.e. number 2 for num2,... number 40 for num40. I am trying not to write the dlookup command 40 times. What I have tried (and failed) is

Dim fieldname as control
Dim i as integer

For i = 1 to 40
set fieldname="num" & i (requires object error)
Me.[Fieldname]= Dlookup(field, table, criteria = i)
next i

If I don't use set I error out on me.fieldname as can't find field field name.

Is this possible?

Regards
GDLKM

This

 

[MajP]

in order to use a variable you would need to use the controls collection and use dot notation. Example

dim strName as string
strName = "num" & 1
debug.print me.controls(strName).value

So at a minimum
Me.[Fieldname] should be me.controls(FieldName)

However this is how I would do it. In each control put a tag in the tag property. How about 1 to 40. Then something like this (untested)

dim myControl as access.control
for each myControl in me.controls
if not myControl.tag = "" then
mycontrol = dlookup(field,table,"something = " & mycontrol.tag)
end if
next myControl

 

[Remou]

Are you quite happy with you table design? You may wish to read:

http://www.r937.com/relational.html