Rules required on how to allocate a query recordset that belong to a Dictionary & that, that Belonging to a collection
Dear Experts!
Kindly see how you can help regarding fine tuning the generated json below:
(2) From item1 to Banker should go into [ ]
(3) But Banker should be outside [
It seems their rules somewhere to apply on how to allocate items that belong to a dictionary & those belonging to collection, kindly share with me those rules if possible, I would not want to just rely on copy paste I want to learn this also.
Main function:
How the fuction is called :
Kindly see how you can help regarding fine tuning the generated json below:
[
{
"DistrictCode": "12563XC",
"IssueTime": "2019-09-25",
"DocumentType": 0,
"Status": 0,
"Qualification": 0,
"ProfessionalBody": "5808863",
"Jobtitle": "Chris",
"CustomerCode": "WFG102563",
"BuyerName": "Clement & Sons",
"BankAccount": "FGH Bank",
"Address": "Rsv West",
"Telephon": "+256306903",
"BankCode": "null",
"Branch": "null",
"ItemID": 1,
"Description": "Pepsi (Rgb 350 ML)",
"BarCode": "6009803227328",
"Qty": 10,
"Price": 41.5,
"Discount": 0,
"Tax": "T",
"TotalAmount": 415,
"Banker": "True",
"Sp": 80.5,
"DocumentDate": "2019-09-26"
}
]
(1) From DistrictCode to Branch + DocumentDate should go into { }(2) From item1 to Banker should go into [ ]
(3) But Banker should be outside [
It seems their rules somewhere to apply on how to allocate items that belong to a dictionary & those belonging to collection, kindly share with me those rules if possible, I would not want to just rely on copy paste I want to learn this also.
Main function:
Option Compare Database
Option Explicit
Public Function Invoices()
' Const SQL_SELECT As String = "SELECT * FROM Qry3;"
Dim json As String
Dim coll As VBA.Collection
Dim dict As Scripting.Dictionary
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
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
Set coll = New VBA.Collection
' Set db = CurrentDb
' Set rs = db.OpenRecordset(SQL_SELECT, dbOpenSnapshot)
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF
Set dict = New Scripting.Dictionary
For Each fld In rs.Fields
dict.Add fld.Name, rs.Fields(fld.Name).Value
Next fld
coll.Add dict
rs.MoveNext
Loop
End If
rs.Close
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
Set dict = Nothing
MsgBox JsonConverter.ConvertToJson(coll, Whitespace:=3), vbOKOnly, "Audited by Chris Hankwembo"
Set coll = Nothing
End Function
How the fuction is called :
Private Sub CmdSales_Click()
Call Invoices
End Sub
ASKER
Thank you so much John just a clarification
dictItem1 .Add fld.Name, rs3.Fields(fld.Name).Value
You mean I have to put the actual field names? I know very well you helped several times with a lot of appreciations , would mind to try one line on the sample database?
Just use the combox to select the invoice , if it works all I need is solid understanding & learning , I'm still facing some challenges , kindly try it
Training.accdb
dictItem1 .Add fld.Name, rs3.Fields(fld.Name).Value
You mean I have to put the actual field names? I know very well you helped several times with a lot of appreciations , would mind to try one line on the sample database?
Just use the combox to select the invoice , if it works all I need is solid understanding & learning , I'm still facing some challenges , kindly try it
Training.accdb
Here is an incomplete quick and dirty sample...
Training--1-.accdb
Training--1-.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Again many many thanks for the good work.
Regards
Chris
Regards
Chris
Hope you will sort it out...
The "key" is that Dictionary is for "{} and collection for "[]"
The "key" is that Dictionary is for "{} and collection for "[]"
Something like
Open in new window
Similar you work for the "[ ]" caseOpen in new window