Passing value from table in a subform to an array!

[ripcure]

Hello,



I am very new to VBA & Access.

I want to use the data populated in a table in the subform. I want to pass the value into an Array.

Any help appreciated!

Thanks and Regards!
Ashish Naik

[ketbdnetbp]

Could you please provide some additional info...

Do you want to populate the subform itself?

OR,

Are you looking to walk through the existing subform's records and place that data into an array for some other purpose?


Please advise.

[ripcure]

Yes, I am looking to walk through the exisitng subform's records and place that data into an array.

Thanks and Regards!
Ashish Naik

Could you please provide some additional info...

Do you want to populate the subform itself?

OR,

Are you looking to walk through the existing subform's records and place that data into an array for some other purpose?


Please advise.

[ketbdnetbp]

You could try something along these lines:

'You would, of course, need to add in your own error handling.

1. Declare the Array in your/a module entitled arrays...

Public arrMyArray() as Variant '(Set bounds/dimensions () at form level)

2. From Main Form (choose appropriate event to fire code), then:

Dim strSubformControl As String
strSubformControl = "NameofSubform"
DoCmd.GoToControl strSubformControl

'This gets you to the subform

'Then, you need to know the number of records

Dim xTR As Long
xTR = Forms!MainFormName!SubformName.Form.RecordsetClone .RecordCount


'Double checks record count

If xTR >= 1 Then
Forms!MainFormName!SubformName.Form.RecordsetClone .MoveLast
xTR = Forms!MainFormName!SubformName.Form.RecordsetClone .RecordCount
End If

'2 dimensions in array, 1 for number of records, 1 for number of fields
'Re-Set array dimensions

ReDim arrMyArray(1 to xTR, 1 to Number of Fields)

'Example: ReDim arrMyArray(1 to xTR, 1 to 3)

3. Then, you start reading the data

If xTR = 1 Then
DoCmd.GoToRecord acActiveDataObject, , acFirst
'Record Counter
Dim intC As Integer
intC = 1
arrMyArray(intC, 1) = Forms!MainFormName!SubformName.Form![Field1]
arrMyArray(intC, 2) = Forms!MainFormName!SubformName.Form![Field2]
arrMyArray(intC, 3) = Forms!MainFormName!SubformName.Form![Field3]
'etc...
end if

If xTR >= 2 Then
DoCmd.GoToRecord acActiveDataObject, , acFirst
'Record Counter
Dim intC2 As Integer
intC2 = 1
arrMyArray(intC2, 1) = Forms!MainFormName!SubformName.Form![Field1]
arrMyArray(intC2, 2) = Forms!MainFormName!SubformName.Form![Field2]
arrMyArray(intC2, 3) = Forms!MainFormName!SubformName.Form![Field3]
'etc...

do until intC2 = xTR
DoCmd.GoToRecord acActiveDataObject, , acNext
Debug.Print intC2
intC2 = intC2 + 1
arrMyArray(intC2, 1) = Forms!MainFormName!SubformName.Form![Field1]
arrMyArray(intC2, 2) = Forms!MainFormName!SubformName.Form![Field2]
arrMyArray(intC2, 3) = Forms!MainFormName!SubformName.Form![Field3]
'etc...
Loop

end if

Hope this gets you started.

All the best,

Jim