Array of variables in a survey database

[natilton]

Hello,

I'm *very* new to VBA, and I'm not sure of how to accomplish a task that I think would warrant the use of an array. This would be an array of survey questions, MUL1_05 through MUL1_33, all of which I've already defined in the table design.

What I'd like to do is disable certain fields and set their values on the form (the MUL1 variables) based on the subject's age.

So when an ID is entered, I have the program look up the child's age from the enrollment table. Then, if the child is between 5 and 8 months, they start with question MUL1_05, and nothing is disabled. If they are between 9 to 12 months, they start with MUL1_08, while 05, 06, and 07 are disabled, and their values are set to 1.

I'd like to have an array so that I can have the program loop through the variables that need to be disabled, instead of having to specify each variable.

I have an AfterUpdate procedure

Private Sub CaseID_AfterUpdate
AgeMon = DLookup (.....) / I know how to do this part

If (AgeMon >= 9 and AgeMon <= 12) Then
Me.MUL1_05.Enabled = False
Me.MUL1_05.Value = 1

and so on.


Any help would be much appreciated

Thanks,
Nick

[June7]

Since the controls are similarly named you can use the Controls collection in a loop. Since you know how many controls have this name structure, try:

For i = 1 to 33
Me.Controls("Mul1_" & IIf(i<10, "0", "") & i).Enabled = False
Next

Expand on this to include other conditional code to consider the age criteria.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[natilton]

Thanks! That seems much easier to implement than what I was envisioning.

[natilton]

Since the controls are similarly named you can use the

For i = 1 to 33
Me.Controls("Mul1_" & IIf(i<10, & "0" & i)).Enabled = False
Next

This worked like a charm. Thanks again!

[June7]

Ooops! Slight error in my suggestion and I edited post.
Use:
Me.Controls("Mul1_" & IIf(i<10, "0", "") & i).Enabled = False

[natilton]

I just adjusted it slightly:

Me.Controls(IFF(n < 10, "MUL1_0", "MUL1_") & n).Enabled = False

[June7]

The function is IIf not IFF.