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

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:
[
  {
    "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"
  }
]

Open in new window

(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

Open in new window



How the fuction is called :

Private Sub CmdSales_Click()
  Call Invoices
End Sub

Open in new window

Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

You need to nest a dictionary inside the Top level dictionary for each case
Something like
Dim dictDistrictCode   As Scripting.Dictionary
Dim rs2 as DAO.Recordset
set rs2 = ...... ' Just Filter the fields in question
For Each fld In rs2.Fields
        dictDistrictCode.Add fld.Name, rs2.Fields(fld.Name).Value
      Next fld

dict.Add "DistrictCode ",dictDistrictCode   

Open in new window

Similar you work for the "[ ]" case
Dim dictItem1As Scripting.Dictionary
Dim rs3 as DAO.Recordset
For Each fld In rs3.Fields
        dictItem1 .Add fld.Name, rs3.Fields(fld.Name).Value
      Next fld

dictDistrictCode.Add "Item1",dictItem1 

Open in new window

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

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



User generated imageTraining.accdb
Here is an incomplete quick and dirty sample...
Training--1-.accdb
ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece 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
Again many many thanks for the good work.

Regards

Chris
Hope you will sort it out...
The "key" is that Dictionary is for "{} and collection for "[]"