Solved Storing received Json from the internet into Parent table & child table at one go

[Josef P.]

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:

    For Each itm In Json("data")("saleList")
        NewPurchID = AddPurchaseHeader(rsHeader, itm) ' Function returns new value from PurchID
        AddPurchaseItems rsDetail, itm("itemList"), NewPurchID
    Next

vs.

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)

 

[nector]

Thank you so much Josef , I appreciate your efforts and I hope others will learns something here.

regards

Chris