How to use the line quantities in the Subform details in VBA array

In Ms Access , I have a subform which shows lines quantities, now I want to put these line quantities into an array for further processing , How do I grab those details line quantities ? I have tried to use the code below its bringing nothing:

item.Add "Quantity", Forms!frmCustomerInvoice![sfrmLineDetails Subform].Form.Quantity & "AND ItemesID =" & CStr(i)

Open in new window

Regards

Chris

Microsoft Access SQL

Last Comment

Jim Dettman (EE MVE)

7/17/2020

Jim Dettman (EE MVE)

7/17/2020

Not sure why you'd want them in an array. As long as the form is open, you can use the form's recordsetclone property and access the records directly.

But as it stands, I don't see anything wrong with what your doing other than you need a space in front of the AND:

item.Add "Quantity", Forms!frmCustomerInvoice![sfrmLineDetails Subform].Form.Quantity & " AND ItemsID = " & CStr(i)

Open in new window

Also looks like you mis-spelled "ItemesID". Think it should be "ItemsID"

What kind of error are you getting?

and just to be clear, you are not using an array.

Jim.

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

7/17/2020

ASKER

Its returning 0

I know your have a point on the record clone part , I cannot lie to I have never used before, is it possible you can assist me with code below:

Dim Rst As DAO.Recordset
Set Rst = Forms!frmCustomerInvoice![sfrmLineDetails Subform] 
    Rst.MoveFirst
    Do While Not Rst.EOF()
        With Rst
            Me.Quantity
            .MoveNext
        End With
    Loop
Exit Sub

Open in new window

SOLUTION

Jim Dettman (EE MVE)

7/17/2020

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

ASKER CERTIFIED SOLUTION

Fabrice Lambert

7/17/2020

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Jim Dettman (EE MVE)

7/17/2020

<<That's a lot of (costly) operations to do, on the other hand, filling up a collection is straight forward: >>

For getting something into an array, GetRows() would be a better option, but I think he really just wanted to work with the records, and recordsetclone fits the bill for that.

Jim.