Link to home
Start Free TrialLog in
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScFlag for Zambia

asked on

How to insert collection in VBA/Json

I need to insert a collection called Tax Class within Json format/Ms access VBA code which is now almost complete see current results below:

Current results
{
   "PosSerialNumber": "102010",
   "IssueTime": "2019-09-15",
   "Customer": 1,
   "TransactionTyp": 0,
   "PaymentMode": 0,
   "SaleType": 0,
   "Items": [
      {
         "ItemID": 1,
         "Description": "Apple (Rgb 350 ML)",
         "BarCode": "6009803227328",
         "Quantity": 15,
         "UnitPrice": 41,
         "Discount": 0,
         "Taxable": [
            {
               "Total": 615,
               "IsTaxInclusive": "True",
               "RRP": 52.8
            }
         ]
      },

Open in new window


Expected results

{
   "PosSerialNumber": "102010",
   "IssueTime": "2019-09-15",
   "Customer": 1,
   "TransactionTyp": 0,
   "PaymentMode": 0,
   "SaleType": 0,
   "Items": [
      {
         "ItemID": 1,
         "Description": "Apple (Rgb 350 ML)",
         "BarCode": "6009803227328",
         "Quantity": 15,
         "UnitPrice": 41,
         "Discount": 0,
         "Taxable": [
              "B"
               ]
            {
               "Total": 615,
               "IsTaxInclusive": "True",
               "RRP": 52.8
            }
         ]
      },

Open in new window

Amendment required on VBA

How do I include the new collection ("tax category") in the below VBA to hold ["B"] just below "taxable"

Private Sub CmdSales_Click()
  
'  Const SQL_SELECT As String = "SELECT * FROM Qry3;"
    
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim qdf As DAO.QueryDef
  Dim prm As DAO.Parameter
  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
  Set db = CurrentDb
  Set qdf = db.QueryDefs("Qry4")
For Each prm In qdf.Parameters
    prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset()

Set qdf = Nothing
 rs.MoveFirst
    Do While Not rs.EOF
        Set transaction = New Dictionary
        transaction.Add "PosSerialNumber", DLookup("PosSerialNumber", "tblInvoice", "Inv =" & Me.CboInv)
        transaction.Add "IssueTime", DLookup("IssueTime", "tblInvoice", "Inv =" & Me.CboInv)
        transaction.Add "Customer", DLookup("Customer", "tblInvoice", "Inv =" & Me.CboInv)
        transaction.Add "TransactionTyp", 0
        transaction.Add "PaymentMode", 0
        transaction.Add "SaleType", 0

        '--- loop over all the items
        Dim itemCount As Long
        itemCount = 3
        Set items = New Collection
        For i = 1 To itemCount
            Set item = New Dictionary
            item.Add "ItemID", i
            item.Add "Description", DLookup("Description", "Qry4", "Inv =" & Me.CboInv & " AND ItemesID =" & CStr(i))
            item.Add "BarCode", DLookup("BarCode", "Qry4", "Inv =" & Me.CboInv & " AND ItemesID =" & CStr(i))
            item.Add "Quantity", DLookup("Qty", "Qry4", "Inv =" & Me.CboInv & " AND ItemesID =" & CStr(i))
            item.Add "UnitPrice", DLookup("unitPrice", "Qry4", "Inv =" & Me.CboInv & " AND ItemesID =" & CStr(i))
            item.Add "Discount", DLookup("Discount", "Qry4", "Inv =" & Me.CboInv & " AND ItemesID =" & CStr(i))

            '--- loop over all the invoices
            Dim invoiceCount As Long
            invoiceCount = 1
            Set invoices = New Collection
            For j = 1 To invoiceCount
                Set invoice = New Dictionary
                invoice.Add "Total", DLookup("TotalAmount", "Qry4", "Inv =" & Me.CboInv & " AND ItemesID =" & CStr(i))
                invoice.Add "IsTaxInclusive", DLookup("Inclusive", "Qry4", "Inv =" & Me.CboInv & " AND ItemesID =" & CStr(i))
                invoice.Add "RRP", DLookup("RRP", "Qry4", "Inv =" & Me.CboInv & " AND ItemesID =" & CStr(i))
                invoices.Add invoice
            Next j
            item.Add "Taxable", invoices
            items.Add item
        Next i
        transaction.Add "Items", items
        
        rs.MoveNext
    Loop
    
    root.Add "", transaction

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

End Sub

Open in new window



Frankly I'm struggling on this topic because it appears there is not enough reading material out , I have checked around there is NOT even a book covering Json/VBA , may this could be a new subject.
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Reading this good book : https://www.amazon.com/Access-2000-Developers-Handbook-Desktop/dp/0782123708 along with it's companion : https://www.amazon.com/VBA-Developers-Handbook-2nd-Getz/dp/0782129781 will get you up and running...but you need to READ it and UNDERSTAND it...with NO shortcuts
ASKER CERTIFIED SOLUTION
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Flag of Zambia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial