title: VBA Access parsing JSON nested array
body: <p>I am using VBA Access to get data from Google Books for a library database. The code is based on that given in <a href="https://stackoverflow.com/questions/54155602/parsing-google-books-json-to-obtain-book-info-by-entering-isbn-in-excel-with-vba">this</a> stackoverflow question.</p>
  <p>I am struggling for the right code to allow for a varying number of authors as the information is in a nested array. I would like all of the author names to appear in one TextBox.</p>
  <p>I tried:</p>
  <pre><code>Form_AddAmendItems.AuthorTextBox.Value = Join(subitem(&quot;authors&quot;), &quot;,&quot;)
  </code></pre>
  <p>from the link above but that fails to find any result.</p>
  <p>I think I need to use UBound and LBound to count the number of authors and then loop through and add each one. But I haven't been able to find an example of how to do that.</p>
  <p>Currently as a workaround I can populate the AuthorTextBox with the names of up to 3 authors, which is enough for my needs. But if there are less than 3 authors the error handler message pops up because it hasn't been able to find the requested data.</p>
  <p>I am using the VBA-JSON Converter from <a href="https://github.com/VBA-tools/VBA-JSON" rel="nofollow noreferrer">here</a>.</p>
  <p>This is the JSON I would like to parse (from <a href="https://www.googleapis.com/books/v1/volumes?q=isbn:9780004589763" rel="nofollow noreferrer">here</a>)</p>
  <pre><code>{
    &quot;kind&quot;: &quot;books#volumes&quot;,
    &quot;totalItems&quot;: 1,
    &quot;items&quot;: [
      {
        &quot;kind&quot;: &quot;books#volume&quot;,
        &quot;id&quot;: &quot;BT2CAz-EjvcC&quot;,
        &quot;etag&quot;: &quot;6Z7JqyUtyJU&quot;,
        &quot;selfLink&quot;: &quot;https://www.googleapis.com/books/v1/volumes/BT2CAz-EjvcC&quot;,
        &quot;volumeInfo&quot;: {
          &quot;title&quot;: &quot;Collins Gem German Dictionary&quot;,
          &quot;subtitle&quot;: &quot;German-English, English-German&quot;,
          &quot;authors&quot;: [
            &quot;Veronika Calderwood-Schnorr&quot;,
            &quot;Ute Nicol&quot;,
            &quot;Peter Terrell&quot;
          ]
  </code></pre>
  <p>And this is my VBA code:</p>
  <pre><code>Private Sub FindBookDetailsButton_Click()
  
  
  'Error handle for Null Strings
      If IsNull(Me.ISBNTextBox) = True Then
          MsgBox &quot;Item ID not specified.&quot;, vbExclamation + vbOKOnly, &quot;Error&quot;
      Exit Sub
  
  End If
  
  'Error message if there is no match
  On Error GoTo ErrMsg
  
  Dim http As Object, JSON As Object, i As Integer, subitem As Object
  Dim ISBN As String
      ISBN = CStr(Me.ISBNTextBox.Value)
  Set http = CreateObject(&quot;MSXML2.XMLHTTP&quot;)
      http.Open &quot;GET&quot;, &quot;https://www.googleapis.com/books/v1/volumes?q=isbn:&quot; &amp; ISBN, False
      http.send
  Set JSON = ParseJSON(http.responseText)
      For Each item In JSON(&quot;items&quot;)
          Set subitem = item(&quot;volumeInfo&quot;)
              Form_AddAmendItems.TitleTextBox.Value = subitem(&quot;title&quot;)
              Form_AddAmendItems.AuthorTextBox.Value = subitem(&quot;authors&quot;)(1)
              Form_AddAmendItems.PublisherTextBox.Value = subitem(&quot;publisher&quot;)
  
  'For multiple authors
  Set subitem = item(&quot;volumeInfo&quot;)
      If subitem.Exists(&quot;authors&quot;) Then
          For Each item2 In subitem(&quot;authors&quot;)
                  Form_AddAmendItems.AuthorTextBox.Value = subitem(&quot;authors&quot;)(1) &amp; &quot;, &quot; &amp; subitem(&quot;authors&quot;)(2)
              Next
              For Each item3 In subitem(&quot;authors&quot;)
                  Form_AddAmendItems.AuthorTextBox.Value = subitem(&quot;authors&quot;)(1) &amp; &quot;, &quot; &amp; subitem(&quot;authors&quot;)(2) &amp; &quot;, &quot; &amp; subitem(&quot;authors&quot;)(3)
              Next
          End If
      Next
  'To end with success
  MsgBox (&quot;Process complete&quot;), vbInformation
  Exit Sub
  
  'To end with an error message
  ErrMsg:
  MsgBox (&quot;No match obtained&quot;), vbCritical
  
  End Sub
  </code></pre>