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
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
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.
Yes, I am looking to walk through the exisitng subform's records and place that data into an array.
Thanks and Regards!
Ashish Naik
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