Creating a control reference with a variable?

[Philhood2]

Is it possible to create a reference to a control on a
form using a variable?

For example:

aVariable = 2000

Forms!ThisForm!aVariable = some value

When I attempt to do this, I get an error saying that the
field 'aVariable' does not exist.

Is there any way of doing this?

Many thanks for any help.

Phil.

 

[MikeC]

Phil,

You can create a reference to a control in a form's module
as follows:

Dim ctl As Control

Set ctl = Me!MyControl 'where MyControl is the control name

To change the value contained in the control:

Me!ctl = 2000

 

[philhood2]

Hi,

Thanks for the advice. Having read your reply, I don't
think I explained the situation very well!

I have a form on which there are several similar controls
named 2000, 2001, 2002, 2003, 2004 (they are all check
boxes representing years)

I am trying to write an event procedure for the form that
applies a test to each of the controls in turn. This is
why I want to refer to the control using a variable so
that I can increment the variable at the end of the test
in order to check the next control.

It would look something like:

aVariable = 2000

Do until aVariable = 2004

if Forms!Years!aVariable = x then do something

aVariable = aVariable + 1

Loop


Does this make sense? Is there a way of doing this?

Thanks again.

Phil.

 

[MikeC]

That helps clarify what you are trying to do.

If you want to cycle through the control names as though
they are numbers, (Access sees them as strings) then you
will need to convert them to strings using a function.

Dim intCtlCnt As Integer
Dim intYr As Integer

intCtlCnt = (Me.Count - 1)
intYr = 2000 'To count from 2000 to 2004

Do
With Me(intCtlCnt) 'Check the control types you
want. Assuming text box.
If .ControlType = acTextBox Then
Do While intYr <= 2004
If .Name = CStr(intYr) And .Value = x
Then 'Replace "x" with your value.
'Do something.
Else
'Do something else.
End If
intYr = intYr + 1
Loop
Else
'Skip other types of controls.
End If
End With
intCtlCnt = intCtlCnt - 1
Loop While intCtlCnt >= 0