MS Access API Call with a Nested Loop

[djs79]

Hello everyone,

Forgive me if this is a dumb question, because this is really my first time using MS Access, so I may be going about this all wrong.

I'm trying to make an API call, to post data to a website, and I'm getting stuck on how perform a loop inside of the call.



Here's my code:

Private Sub Command5_Click()
Dim result As String
Dim URL As String, postData As String
Dim winHttpReq As Object
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
Dim rs As DAO.Recordset

' -- SQL QUERY
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Orders WHERE OrderNumber = 1003")

' -- BEGIN THE API CALL
If Not rs.EOF Then
URL = "https://websiteURL.com?token=myAPItoken&xmlData=" & _
"<Invoices>" & _
"<row no='1'>" & _
"<FL val='Customer Number'>" & rs.Fields("cid") & "</FL>" & _
"<FL val='Name'>" & rs.Fields("name") & "</FL>" & _
"<FL val='Product Details'>"

' -- QUERY MULTIPLE PRODUCT DETAILS BASED ON THE CURRENT ORDER NUMBER
Set prods = CurrentDb.OpenRecordset("SELECT * FROM OrderDetails WHERE OrderNumber = 1003")

    With prods
            Do While Not .BOF
                'Do something with the recordset/Your Code Goes Here
                "<product no='1'>" & _
                     "<FL val='Product Id'>prodsField("prodID")</FL>" & _
                     "<FL val='Product Name'>prodsField("prodName")</FL>" & _
                     "<FL val='Unit Price'>prodsField("price")</FL>" & _
                     "<FL val='Quantity'>prodsField("qty")</FL>" & _
                "</product>"
            Loop
        End If
    End With

"</FL>" & _
"</row>" & _
"</Invoices>"

winHttpReq.Open "POST", URL, False
winHttpReq.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
winHttpReq.Send (postData)
 
result = winHttpReq.responseText
End If
  
End Sub

The issue I'm running in to is that the 'Do While' loop breaks the API URL. I'm not sure how to interject the loop inside of the API call.
If anyone can help me with this, I'd really appreciate it!

Thanks!

[pbaldy]

I don't know what your finished string is supposed to look like, but I'd expect it to be continued like:

URL = URL & "more string"

Also, you aren't concatenating in the loop like you are at the beginning (the recordset values).

[djs79]

Thank you for that info. You're right, I can just add additional strings to the API call like: winHttpReq.Open "POST", myURL & product1 & product2 & product3, False. I don't know why I didn't think of that!

My new dilemma is that I now need to create a dynamic variable for each iteration of the loop to store that products information, that I can then reference later within the API call. (in other words, I need to create those product1, product2 and product3 variables and assign them within the loop)

I have a counter that effectively adds +1 to each iteration within the loop. The counter variable is called "Cntr", so I need to do something like the following:

product(Cntr) = "<product no='" & Cntr & "'>" & _    
     "<FL val='Product Id'>prodsField("prodID")</FL>" & _    
     "<FL val='Product Name'>prodsField("prodName")</FL>" & _    
     "<FL val='Unit Price'>prodsField("price")</FL>" & _    
     "<FL val='Quantity'>prodsField("qty")</FL>" & _ 
"</product>"

But this gives me an error. I've done some research on dynamically creating a variable name, but none of them are working. Do you have any insight on how to accomplish this?

Thanks again!

[pbaldy]

I wouldn't use dynamically created variables, I'd just keep adding on to the string. Of course, I still don't know what the string is supposed to look like. I'm thinking:

URL = URL & "<product no='" & Cntr & "'>" & _

[djs79]

The completed API Call should look like this: (the number of products will vary with each call)

Dim result As String
Dim myURL As String, postData As String
Dim winHttpReq As Object
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")


URL = "https://websiteURL.com?token=myAPItoken&xmlData=
<Invoices>
<row no='1'>
<FL val='Customer Number'>Customer Number</FL>
<FL val='Name'>Customer Name</FL>
<FL val='Product Details'>
<product no='1'>
   <FL val='Product Id'>Product ID</FL>
   <FL val='Product Name'>Product Name</FL>
   <FL val='Unit Price'>5.00</FL>
   <FL val='Quantity'>1</FL>
</product>
<product no='2'>
   <FL val='Product Id'>Product ID</FL>
   <FL val='Product Name'>Product Name</FL>
   <FL val='Unit Price'>5.00</FL>
   <FL val='Quantity'>1</FL>
</product>
<product no='3'>
   <FL val='Product Id'>Product ID</FL>
   <FL val='Product Name'>Product Name</FL>
   <FL val='Unit Price'>5.00</FL>
   <FL val='Quantity'>1</FL>
</product>
</FL>
</row>
</Invoices>"

winHttpReq.Open "POST", URL, False
winHttpReq.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
winHttpReq.Send (postData)
 
result = winHttpReq.responseText

So, in taking your original idea, I looped through the products and thought that I would save each product as a variable and then do something like this:
winHttpReq.Open "POST", myURL & product1 & product2 & product3, False

I'm not sure how else to keep adding to the string because when I interject the loop, in the middle of the URL, I can't seem to just add "&_" to keep adding on to it. Maybe that's the part that I'm confused about. Is there a way to continue adding on to that URL string after the loop is placed in the middle of it?

Thanks again!

[pbaldy]

I must not be showing this clearly enough. First build the main part of your string:

URL = "https://websiteURL.com..."

Then, in the loop, add each product:

URL = URL & "<product no='" & Cntr & "'>" & _

The part in red will add the bit in the loop to the previously built string.

[djs79]

I'm sorry - I was just misreading what you were typing. That worked perfectly! Thank you so much!

[pbaldy]

Happy to help, and welcome to the site by the way!