Solved Storing received Json from the internet into Parent table & child table at one go (1 Viewer)

Josef P.

Well-known member
Local time
Today, 18:54
Joined
Feb 2, 2023
Messages
847
This was not intended as a replacement but rather as a suggestion for an alternative design of the code. :)
If you want to add something after 3 months, for example, it is advantageous if the code is easy to read.

For example:
Code:
    For Each itm In Json("data")("saleList")
        NewPurchID = AddPurchaseHeader(rsHeader, itm) ' Function returns new value from PurchID
        AddPurchaseItems rsDetail, itm("itemList"), NewPurchID
    Next
vs.
Code:
For Each itm In JSON("data")("saleList")
  'Process data.
  'Purchases header details to saved in the parent table
    rs.AddNew
      rs("OurTPIN") = itm("spplrTpin")
      rs("bhfId") = itm("spplrBhfId")
      rs("spplrInvcNo") = itm("spplrInvcNo")
      rs("rcptTyCd") = itm("rcptTyCd")
      rs("pmtTyCd") = itm("pmtTyCd")
      rs("cfmDt") = itm("cfmDt")
      rs("pchsDt") = CDate(Format$((itm("salesDt")), "00/00/00"))
      rs("wrhsDt") = itm("stockRlsDt")
      rs("totItemCnt") = itm("totItemCnt")
      rs("taxblAmtA") = itm("taxblAmtA")
      rs("taxblAmtB") = itm("taxblAmtB")
      rs("taxRtA") = itm("taxRtA")
      rs("taxRtB") = itm("taxRtB")
      rs("taxRtD") = itm("taxRtD")
      rs("taxRtB") = itm("taxRtB")
      rs("taxAmtA") = itm("taxAmtA")
      rs("taxAmtB") = itm("taxAmtB")
      rs("taxAmtC1") = itm("taxAmtC1")
      rs("taxAmtC2") = itm("taxAmtC2")
      rs("taxAmtD") = itm("taxAmtD")
      rs("totTaxblAmt") = itm("totTaxblAmt")
      rs("totTaxAmt") = itm("totTaxAmt")
      rs("totAmt") = itm("totAmt")
      rs("remark") = itm("remark")
      rs("regrNm") = "Admin"
      rs("regrId") = "Admin"
      rs("modrNm") = "Admin"
      rs("modrId") = "Admin"
      rs.Update

'Purchases details to saved in the child table
    ' z = 1   ' <-- What is this for? It is not even declared
    For Each lineItm In itm("itemList")
      rst.AddNew
        rst("itemSeq") = lineItm("itemSeq")
        rst("itemCd") = lineItm("itemCd")
        rst("itemClsCd") = lineItm("itemClsCd")
        rst("itemNm") = lineItm("itemNm")
        rst("bcd") = lineItm("bcd")
        rst("pkg") = lineItm("pkg")
        rst("qtyUnitCd") = lineItm("qtyUnitCd")
        rst("qty") = lineItm("qty")
        rst("prc") = lineItm("prc")
        rst("splyAmt") = lineItm("splyAmt")
        rst("dcRt") = lineItm("dcRt")
        rst("dcAmt") = lineItm("dcAmt")
        rst("taxblAmt") = lineItm("taxblAmt")
        rst("taxAmt") = lineItm("vatAmt")
        rst("totAmt") = lineItm("totAmt")
        rst("vatCatCd") = lineItm("vatCatCd")
        rst("PurchID") = DLast("PurchID", "tblpurchases")
      rst.Update
    Next
  Next

Note:
I consider the names of the recordsets rs and rst to be potentially error-prone.
You might accidentally write rs somewhere in the code even though you meant rst and when you quickly look over the code you might not even notice it. Hence my renaming to rsHeader and rsDetail (or better: rsPurchLines or PurchItems)
 
Last edited:

nector

Member
Local time
Today, 19:54
Joined
Jan 21, 2020
Messages
381
Thank you so much Josef , I appreciate your efforts and I hope others will learns something here.

regards

Chris
 

Users who are viewing this thread

Top Bottom