How to load all the field in the subform to be part of the Json String in Ms Access VBA

Question

I want to have all the data in the open subform to be part of my JSON string. Currently, only the first row is picked up. I want all current rows to be picked up after pressing the save button.

The parent form data is properly picked up, it's just this part on the subform which is incomplete: it's only picking the first-row data, the second-row data through the last is not picked up.

I want the first row of data to the last to be picked up after pressing the save button from the subform called [sfrmInvoicedetails Subform]

Private Sub CmdSales_Click()

    Dim rs As Recordset
    Dim foo As New Dictionary
    Set foo = New Dictionary

    Dim Noor As Dictionary
    Set Noor = New Dictionary

    Dim hoo As New Collection
    Dim goo As New Dictionary
    Set goo = New Dictionary

    Dim Zoo As New Dictionary
    Set Zoo = New Dictionary

    Dim Koo As New Collection
    Dim Too As New Collection

    Set rs = Me.[sfrmInvoicedetails Subform].Form.RecordsetClone

    With rs
        Do While Not .EOF
        'Do Something
        .MoveNext
        Loop
    End With

    Set rs = Nothing
    With foo
        .Add "PosSerialNumber", Me.INV
        .Add "IssueTime", Me.InvoiceDate
        .Add "Customer", Me.Customer.Column(1)
        .Add "TransactionTyp", 0
        .Add "PaymentMode", 0
        .Add "SaleType", 0
        .Add "Items", Koo
     Koo.Add Noor
     Noor.Add "ItemID", 1
     Noor.Add "Description", Forms!frmInvoice![sfrmInvoicedetails Subform]!Description.Column(1)
     Noor.Add "BarCode", "4589630036"
     Noor.Add "Quantity", Forms!frmInvoice![sfrmInvoicedetails Subform]!Qty
     Noor.Add "UnitPrice", Forms!frmInvoice![sfrmInvoicedetails Subform]!UnitPrice
     Noor.Add "Discount", Forms!frmInvoice![sfrmInvoicedetails Subform]!Discount
     Noor.Add "Taxable", hoo
     hoo.Add Forms!frmInvoice![sfrmInvoicedetails Subform]!Taxables
     Noor.Add "Total", 120
     Noor.Add "IsTaxInclusive", Forms!frmInvoice![sfrmInvoicedetails Subform]!Inclusive
     Noor.Add "RRP", Forms!frmInvoice![sfrmInvoicedetails Subform]!RRP
     End With

     Dim member As Variant
     For Each member In foo

     Next

     MsgBox JsonConverter.ConvertToJson(foo, Whitespace:=3), vbOKOnly, "Audited by Chris H"
End Sub

Full data rows picked from the recordset of the subform called [sfrmInvoicedetails Subform] and show in the JSON string

Answer 1

You're having an issue with nesting the objects, which can get tricky. The example below should help. I created a helpful guide in this answer that I constantly refer back to.

As a side note, I strongly reccommend using better names for your variables. Not only was I confused about foo, Koo, Noor, andhoo`, but I can guarantee that you'll be confused in a few months when you have to come back and maintain/change your code.

Option Compare Database
Option Explicit

Sub CreateJSON()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Table1")

    Dim root As Dictionary
    Set root = New Dictionary

    Dim transaction As Dictionary
    Dim transactions As Collection
    Dim item As Dictionary
    Dim items As Collection
    Dim invoice As Dictionary
    Dim invoices As Collection

    Dim i As Long
    Dim j As Long
    Set transactions = New Collection

    rs.MoveFirst
    Do While Not rs.EOF
        Set transaction = New Dictionary
        transaction.Add "PosSerialNumber", rs!PosSerialNumber
        transaction.Add "IssueTime", rs!IssueTime
        transaction.Add "Customer", rs!Customer
        transaction.Add "TransactionTyp", 0
        transaction.Add "PaymentMode", 0
        transaction.Add "SaleType", 0

        '--- loop over all the items
        Dim itemCount As Long
        itemCount = 2
        Set items = New Collection
        For i = 1 To itemCount
            Set item = New Dictionary
            item.Add "ItemID", i
            item.Add "Description", "some description"
            item.Add "BarCode", "some barcode"
            item.Add "Quantity", "some quantity"
            item.Add "UnitPrice", "some unit price"
            item.Add "Discount", "some discount"

            '--- loop over all the invoices
            Dim invoiceCount As Long
            invoiceCount = 3
            Set invoices = New Collection
            For j = 1 To invoiceCount
                Set invoice = New Dictionary
                invoice.Add "Total", 120 + j
                invoice.Add "IsTaxInclusive", "of course it is"
                invoice.Add "RRP", "something about RRP"
                invoices.Add invoice
            Next j
            item.Add "Taxable", invoices
            items.Add item
        Next i
        transaction.Add "Items", items
        transactions.Add transaction
        rs.MoveNext
    Loop
    root.Add "JSON Created", Now()
    root.Add "Transactions", transactions

    Dim json As String
    json = JsonConverter.ConvertToJson(root, Whitespace:=3)
    Debug.Print json
End Sub