Trying to use an array in a If Statement.

[lws]

I am trying to use an array in a If statement inside a For loop to cycle through serveral check boxes on a form. The For loop works but the If statment gives me a Run-Time Error '13' Type mismatch error.

Is it even possible to use an array in a If statement?

An excerpt for the code is as follows:

Dim verStore1(4) As String
Dim verStore2(4) As String

Dim i As Long

verStore1(0) = "OOS"
verStore1(1) = "RTS"
verStore1(2) = "PM"
verStore1(3) = "CAL"
verStore2(0) = "[OOS_Check]"
verStore2(1) = "[RTS_Check]"
verStore2(2) = "[PM_Check]"
verStore2(3) = "[CAL_Check]"


For i = 0 To 3

verstr = "[Forms]![F_Maintenance_Log]!" & verStore2(i)

If verstr = True Then

Me.[Variable_Field] = Me.[Variable_Field] & verStore1(i) & vbCrLf
Else

End If

Next i

 

[MarkK]

Would it help to just put the controls you want in an array? Consider this code . . .

   dim vMyControls
   dim var
   vMyControls = Array(me.chk1, me.chk2, me.chk3)

   for each var in vMyControls
     debug.print var.name, var.value
   next

... so that constructs an array of controls that you can then enumerate directly. Similarly, you can construct such an array on a form, and make it public so that other objects can consume it. Consider . . .

[COLOR="Green"]'code on form A[/COLOR]
dim m_myControls

Public Property Get MyControlsA  As Variant
[COLOR="Green"]'  Exposes an array of selected check box controls on form A   
   'checks if the variable has been initialize and if not, initializes it
[/COLOR]   If IsEmpty(m_myControls) Then m_myControls = Array(me.chk1, me.chk2, me.ch3, me.chk4)
[COLOR="Green"]   'always returns an array for the lifetime of formA[/COLOR]
   MyControlsA = m_myControls
End Property

[COLOR="Green"]'code on form B[/COLOR]
   
   dim frmA as Form_FormA
   dim var
[COLOR="Green"]
   'get a reference to formA[/COLOR]
   Set frmA = Forms("FormA")
[COLOR="Green"]   'consume it's custom public property called MyControlsA[/COLOR]
   For Each var In frmA.MyControlsA
     debug.print var.name, var.value
   Next

Maybe that gives you some ideas that make what you are doing easier?

 

[CJ_London]

For i = 0 To 3

verstr = "[Forms]![F_Maintenance_Log]!" & verStore2(i)

If verstr = True Then

Me.[Variable_Field] = Me.[Variable_Field] & verStore1(i) & vbCrLf
Else

End If

Verstr is a string which says [Forms]![F_Maintenance_Log]![OOS_Check] and is not referring to the value on your form - so you are trying to see if text=true rather than the value on the form.

You need to wrap it in the Eval function

If Eval(verstr) = True Then

Depending on what you are trying to achieve, you may also need to use it on the next line of your code as well

Here is a link to help explain in more detail
http://msdn.microsoft.com/en-us/library/office/aa172212(v=office.11).aspx

 

[lws]

Thanks!! The Eval() worked. Appreciate the help.