Process objects stored in array

[better]

Is there a way that I can store objects, not their values, in an array, and then process them using array syntax? I'm thinking of something along these lines...



Private Sub UpdateControls(Exists as Boolean)
Dim CtrlArr(2, 2) as Variant
Dim I as Integer
Dim rs as Object
Set rs = CurrentDb.OpenRecordset ("ContactInfo")
CtrlArr(0, 0) = Me![Customer]
CtrlArr(0, 1) = Me![Address]
CtrlArr(0, 2) = Me![Phone]
CtrlArr(1, 0) = rs(0)
CtrlArr(1, 1) = rs(1)
CtrlArr(1, 2) = rs(2)
CtrlArr(2, 0) = ""
For I=0 to UBound CtrlArr(0,1)
If Exists = True Then
CtrlArr(0, I) = CtrlArr(1, I)
Else
CtrlArr(0, I) = CtrlArr(2, 0)
End If
Next
End Sub


This is obviously somewhat simplified. There are 33 form fields that I need to either update or clear conditionally, and I don't want to code for each one seperately. How can I do this?

Thanks in advance!

Bart

[OBP]

How can you use an array, surely each field will have a different criteria for clearing/updating etc.

[better]

How can you use an array, surely each field will have a different criteria for clearing/updating etc.

No, they won't. If a record exists, I want to show the related entries. If not, I want to clear the fields and set focus. I just don't know how to get the object reference, rather than its value, into the array.

[OBP]

You might be able to do it using the form's "Field Collection" but I know you can't set a value when you use the Table's field collection.

[better]

I figured it out!



Private Sub UpdateControls(Exists as Boolean)
Dim CtrlArr(2, 2) as Variant
Dim I as Integer
Dim rs as Object
Dim TB as Control
Set rs = CurrentDb.OpenRecordset ("ContactInfo")
Set TB = Me![Customer]
Set CtrlArr(0, 0) = TB
Set TB = Me![Address]
Set CtrlArr(0, 1) = TB
Set TB = Me![Phone]
Set CtrlArr(0, 2) = TB
For I = 0 To UBound(CtrlArr, 2)
CtrlArr(1, I) = rs(I)
Next
CtrlArr(2, 0) = ""
For I=0 to UBound CtrlArr(2)
If Exists = True Then
Set TB = CtrlArr(0, I)
TB = CtrlArr(1, I)
Else
Set TB = CtrlArr(0, I)
TB = CtrlArr(2, 0)
End If
Next
End Sub





The trick is loading the control into an appropriately declared variable on the way into and out of the array, in this case, "TB as Control".

[OBP]

Well done, I will file this one away for future use, in case someone els wants to do the same thing.

[Norie]

Why do you need the 2 Sets for each control?

Couldn't you just use something like this?


Set CtrlArr(0,0) = Me![Customer]

Also why a 2-dimensional array?

[stanl]

Aren't arrays by default VARIANT; and can't you just use the Type() function to determine they are controls?:think: Stan