Passing Array to Sub (1 Viewer)

[Wysy]

Hi,
I have a form/subform object. For some functions i use arrays. In order to make it simplier i try to make a function in a modul that creates the array and then call this function in the forms sub to perform specific task like marking records shown in subform.
It would look like this:
function array1(arr())
end fucntion
then
in form:
Public Sub click()
Dim testarray()
Call array1(testarray)
end sub
I do not get i working. Is it at all possible?
thanks
Andrew

 

[MajP]

Passing And Returning Arrays With Functions

 

[Wysy]

Thank you but i am still do not get it.
This the function to create the array and populate with a recordset
Function txArray1()
Dim tx As Recordset
Dim strMarked As String
Dim txList()
Dim txType As String
strMarked = "SELECT * FROM tbTransactions WHERE tx_Status=''"
Set tx = CurrentDb.OpenRecordset(strMarked, dbOpenSnapshot)
tx.MoveLast
tx.MoveFirst
txList = tx.GetRows(tx.RecordCount)
tx_RC = tx.RecordCount - 1
End Function

Then I have a sub that would use this recordset in the array
Private Sub cmdMarkAll_Click(ByRef Arr())
Dim test ()
Call txArray(test)
For i=0 to tx_RC

End Sub

 

[MajP]

First of all there is probably a much better way to do this. There is really never a reason to work with db data in arrays when you can work with a recordset. But for academic purposes it is important to know how to pass and use arrays. From your example I am very unclear where you want to pass the array to and where it is being passed from.
I definitely caution against doing this. Getrows returns a 2 dimensional array. The first is the index of the field, the second of the record. This is a PITA to work with. You can pass a recordset around far easier and work with it far easier. If you are not set on doing this, I would do it another way.

 

[theDBguy]

Thank you but i am still do not get it.

Hi. I agree with @MajP. To help you understand "how" to pass an array to a function, try the following experiment.

In a Standard Module, copy and paste the following code:

Public Function FillArray(arr() As Variant) As Variant
'thedbguy@gmail.com
'12/8/2021

Dim x As Long

For x = 0 To UBound(arr)
    arr(x) = x
Next

End Function

Then, create a blank form and add a button to it. In the Click event of the button, use the following code:

Private Sub cmdTestArray_Click()
Dim arr(10) As Variant
Dim x As Long

Call FillArray(arr())

For x = 0 To UBound(arr)
    Debug.Print arr(x)
Next


End Sub

View the form in Normal view and then click the button. Look in the Immediate Window for the result.

Take a look at the code and hope that helps explain/demonstrate how you could pass an array to a function.

Cheers!

 

[Wysy]

Thank you so much. I understand.
Actually what i want is to have a button on a main form to select all records in subform. Do you have any suggestion theh?

 

[MajP]

Here is another example using getrows. This is even more complicated since it is a 2d array. You can pass a query and print all of its values.

qryOne qryOne

ID	StringField	dtm	nmb
2​	qdd	12/8/2021​	456​
3​	fff	12/10/2021​	789​
4​	gg	12/10/2021​	1111​
6​	hh	12/16/2021​	1212​
7​	ii	12/9/2021​	1313​

Public Function GetArray(Domain As String) As Variant()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset(Domain)
  If Not rs.EOF Then
    rs.MoveLast
    rs.MoveFirst
    GetArray = rs.GetRows(rs.RecordCount)
   End If
End Function

Public Sub TestArr()
  Dim arr() As Variant
  arr = GetArray("qryOne")
  Dim i As Integer
  Dim j As Integer
  For i = 0 To UBound(arr, 1)
    For j = 0 To UBound(arr, 2)
      Debug.Print "i:" & i & " j:" & j & "  "; arr(i, j)
    Next j
  Next i

End Sub

Out put

i:0 j:0   2
i:0 j:1   3
i:0 j:2   4
i:0 j:3   6
i:0 j:4   7
i:1 j:0  qdd
i:1 j:1  fff
i:1 j:2  gg
i:1 j:3   hh
i:1 j:4   ii
i:2 j:0  12/8/2021
i:2 j:1  12/10/2021
i:2 j:2  12/10/2021
i:2 j:3  12/16/2021
i:2 j:4  12/9/2021
i:3 j:0   456
i:3 j:1   789
i:3 j:2   1111
i:3 j:3   1212
i:3 j:4   1313

 

[MajP]

What do you mean by select? Set some field to true? Get a recordset of them?

 

[Pat Hartman]

I agree with the others, there is never a reason to load a recordset into an array. A recordset IS itself an array.

 

[theDBguy]

Thank you so much. I understand.
Actually what i want is to have a button on a main form to select all records in subform. Do you have any suggestion theh?

If you want to grab a copy of the records in the subform, maybe you could try using a RecordsetClone. Just a thought...

 

[Wysy]

Hi. I agree with @MajP. To help you understand "how" to pass an array to a function, try the following experiment.

In a Standard Module, copy and paste the following code:

Public Function FillArray(arr() As Variant) As Variant
'thedbguy@gmail.com
'12/8/2021

Dim x As Long

For x = 0 To UBound(arr)
    arr(x) = x
Next

End Function

Then, create a blank form and add a button to it. In the Click event of the button, use the following code:

Private Sub cmdTestArray_Click()
Dim arr(10) As Variant
Dim x As Long

Call FillArray(arr())

For x = 0 To UBound(arr)
    Debug.Print arr(x)
Next


End Sub

View the form in Normal view and then click the button. Look in the Immediate Window for the result.

Take a look at the code and hope that helps explain/demonstrate how you could pass an array to a function.

Cheers!

Thank you, i understand now

 

[Wysy]

I have got it worked, marking all records in subform.
Private Sub cmdMarkAll_Click()
Dim mark() As Variant

Dim markall As Recordset
Set markall = CurrentDb.OpenRecordset("SELECT mark FROM tbTransactions")
markall.MoveLast
markall.MoveFirst

mark = markall.GetRows(markall.RecordCount)
markRC = markall.RecordCount - 1
For i = 0 To markRC
Me.fsubLedger.Form.mark = -1
Me.fsubLedger.SetFocus
DoCmd.GoToRecord , , acNext


Next i

End Sub

thank you for the help

 

[MajP]

You might want to look at an update query. Much less code and no looping. The subform records are based likely on a link to the main form. Lets just say that is the OrderID.

CurrentDb.execute "Update tblTransactions Set MARK = -1 where OrderID = " & me.OrderID

so that update all records in the subform since the subform is filtered by some linking field.

 

[Wysy]

Thank you i will.