Variable in Field Name

[Guest]

Hello. I hope I can explain this correclty. I am using Access 2003 and I
need to use a variable in a loop to reference different field names. Here's
the basic code.

Dim myVar As Access.Control
Dim intCount as Integer

Do
Set myVar = Forms!field[intCount]
intCount = intCount + 1
Loop Whilte inCount < 5

So what I need to do is reference multiple fields, field1, field2, field3,
etc. using the above scheme, but I don't know how to do it. Any help would
be greatly appreciated.

 

[Allen Browne]

Try something like this:
Dim strName as string
For intCount = 1 to 5
strName = "Field" & i
Set myVar = Me.Controls(strName)
...
Next

 

[Douglas J. Steele]

Pass a reference to the form to the routine:

Sub MySub(WhatForm As Form)
Dim myVar As Access.Control
Dim intCount as Integer

intCount = 0
Do
Set myVar = WhatForm.Controls("Field" & intCount)
intCount = intCount + 1
....
Loop Whilte inCount < 5

End Sub

Invoke this as:

Call MySub(Me)

or

Call MySub(Forms!MyFormName)

(Note that MyFormName must be open when you do this.)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. WhatI realized is that this needs to run in the function on the
form, not in a module. Is there a way to get it to work in a module?

Try something like this:
Dim strName as string
For intCount = 1 to 5
strName = "Field" & i
Set myVar = Me.Controls(strName)
...
Next

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello. I hope I can explain this correclty. I am using Access 2003
and I
need to use a variable in a loop to reference different field names.
Here's
the basic code.

Dim myVar As Access.Control
Dim intCount as Integer

Do
Set myVar = Forms!field[intCount]
intCount = intCount + 1
Loop Whilte inCount < 5

So what I need to do is reference multiple fields, field1, field2,
field3,
etc. using the above scheme, but I don't know how to do it. Any help
would
be greatly appreciated.