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
Expected results
How do I include the new collection ("tax category") in the below VBA to hold ["B"] just below "taxable"
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.
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
}
]
},
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
}
]
},
Amendment required on VBAHow 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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.