Solved Failing To Save Imported Data from the internet in Ms Access

[nector]

I have imported the data from the internet successfully, but surprising I'm failing to save it in the database, below is my code what is the problem here:

Private Sub CmdImports_Click()
Dim n As Integer
Dim Request As Object
Dim strData As String
Dim stUrl As String
Dim Response As String
Dim requestBody As String
Dim Company As New Dictionary
On Error Resume Next
Set Company = New Dictionary

stUrl = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

Set Request = CreateObject("MSXML2.XMLHTTP")
Company.Add "tpin", Forms!frmLogin!txtsuptpin.Value
Company.Add "bhfId", Forms!frmLogin!txtbhfid.Value
Company.Add "lastReqDt", Format((Me.txtImportDate), "YYYYMMDD000000")
strData = JsonConverter.ConvertToJson(Company, Whitespace:=3)
requestBody = strData
    With Request
        .Open "POST", stUrl, False
        .setRequestHeader "Content-type", "application/json"
        .send requestBody
        Response = .responsetext
    End With
If Request.Status = 200 Then
MsgBox Request.responsetext, vbInformation, "Internal Audit Manager"


Dim http As Object, Json As Object, i As Integer
Dim lineItm As Object
Dim Itm As Object
Dim Z As Integer
Dim Y As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblImports", dbOpenDynaset, dbSeeChanges)
Set rs = db.OpenRecordset("tblImportDumpdetails", dbOpenDynaset, dbSeeChanges)
Set Json = ParseJson(Request.responsetext)

  'i = 1
  On Error Resume Next
  For Each Itm In Json("data")("saleList")
'Process data.
'Purchases header details to saved in the parent table
rst.AddNew
rst("tpin") = Forms!frmLogin!txtsuptpin.Value
rst("bhfId") = Forms!frmLogin!txtbhfid.Value
rst.Update

'Purchases details to saved in the child table

For Each lineItm In Json("data")("itemList")
    
rs.AddNew

rs("taskCd") = lineItm("taskCd")
rs("dclDe") = lineItm("dclDe")
rs("itemSeq") = lineItm("itemSeq")
rs("dclNo") = lineItm("dclNo")
rs("hsCd") = lineItm("hsCd")
rs("itemNm") = lineItm("itemNm")
rs("imptItemsttsCd") = lineItm("imptItemsttsCd")
rs("orgnNatCd") = lineItm("orgnNatCd")
rs("exptNatCd") = lineItm("exptNatCd")
rs("pkg") = lineItm("pkg")
rs("pkgUnitCd") = lineItm("pkgUnitCd")
rs("qty") = lineItm("qty")
rs("qtyUnitCd") = lineItm("qtyUnitCd")
rs("totWt") = lineItm("totWt")
rs("netWt") = lineItm("netWt")
rs("spplrNm") = lineItm("spplrNm")
rs("agntNm") = lineItm("agntNm")
rs("invcFcurAmt") = lineItm("invcFcurAmt")
rs("invcFcurCd") = lineItm("invcFcurCd")
rs("invcFcurExcrt") = lineItm("invcFcurExcrt")
rs("ImportID") = DLast("ImportID", "tblImports")
rs.Update
    Next
    Next
    
MsgBox "completed Invoice Deatils", vbInformation, "Imports Purchases Done!"
 
    rs.Close
    rst.Close
    Set rs = Nothing
    Set rst = Nothing
    Set db = Nothing
    Set Json = Nothing
    Set Itm = Nothing
    Set lineItm = Nothing

End If

End Sub

 

[arnelgp]

but surprising I'm failing to save it in the database,

did you check the structure of the json file, maybe it did changed.

 

[CJ_London]

Suggest remove or comment out ‘on error resume next’ and step through the code

 

[nector]

Okay the problem here is that this field ImportID" is not part of Json I just wanted to link the data to the primary key in the parent table because if I do not do that the same field in the Child will appear quite okay, but the reference remains hidden that is where my problem is. But I can allow the users to retype this foreign key which is very manual now, any suggestion on how to deal with this is very much welcome

If I remove this field the child table is populated with the required data without problems, but the foreign key will remain hidden, that is where the problem is, its difficult to know the data relationship


rs("ImportID") = DLast("ImportID", "tblImports")



See picture here

 

[CJ_London]

Dlast will basically return a random number from the table since you cannot specify an order. Assuming the id is an incrementing autonumber use dmax.

Alternatively you have just appended the record so you should be able to get the id from the rst recordset

 

[nector]

Okay thanks CJ let me try it

 

[nector]

Many thanks CJ you were 100% correct on DMax , it did the magic everything is now working okay