QuickBooks to MS Access for Sales Receipts

[agrych]

hello all, I am trying to pull all of the Sales Receipts data, for a specified date range, into MS Access.
So far I am unsuccessful in my attempt. Below is the code that I have so far. FYI, I have temporarily disabled On Error so I can continue to debug this code. My responseList is empty :( Not sure what I'm doing wrong. Any help would greatly be appreciated.

Private Sub Command2_Click()
 
  'On Error GoTo Errs
    Dim Country As String
    Country = "US"
    'We want to know if we've begun a session so we can end it if an
    'error sends us to the exception handler.
    Dim bSessionBegun As Boolean
    bSessionBegun = False
    Dim bConnectionOpen As Boolean
    bConnectionOpen = False
 
    Dim accessDB As Database
    Set accessDB = CurrentDb
    If (accessDB Is Nothing) Then
        Exit Sub
    End If
 
    'Create the session manager object
    Dim SessMgr As New QBSessionManager
 
    'Create the message set request object for the specific version messages
    Dim requestMsgSet As IMsgSetRequest
    Set requestMsgSet = SessMgr.CreateMsgSetRequest("US", 3, 0)
    'requestMsgSet.Attributes.OnError = roeContinue
 
    'Connect to QuickBooks and begin a session
    SessMgr.OpenConnection "", "SDK SalesReceipts Data"
    bConnectionOpen = True
    SessMgr.BeginSession "", omDontCare
    bSessionBegun = True
 
   'Perform the request and obtain a response from QuickBooks
    Dim responseMsgSet As IMsgSetResponse
    Set responseMsgSet = SessMgr.DoRequests(requestMsgSet)
 
    'Build SalesReceipts Query
 
    'add the request to the message set request object
    Dim query2 As ISalesReceiptQuery
    Set query2 = requestMsgSet.AppendSalesReceiptQueryRq
 
    'Set the value of the ISalesReceiptQuery.IncludeLineItems element
    query2.IncludeLineItems.SetValue (True)
 
     'begin ParseSRQueryRs
    If (responseMsgSet Is Nothing) Then
       MsgBox "No Detail Available for responseMsgSet"
       Exit Sub
    End If
 
    Dim responseList As IResponseList
    Set responseList = responseMsgSet.responseList
    If (responseList Is Nothing) Then
      MsgBox "No Detail Available for responseList"
      Exit Sub
    End If
 
 
    'Go through all of the responses in the list
    Dim i As Integer
    For i = 0 To responseList.Count - 1
      Dim response As IResponse
      Set response = responseList.GetAt(i)
 
      If (response.StatusCode = 0) Then
        Dim respType2 As IResponseType
        Set respType2 = response.Type
        Dim j As Integer
        'Check for SalesReceiptQueryRs
        If (respType2.GetValue = rtSalesReceiptQueryRs) Then
            Dim salesReceiptRetList As ISalesReceiptRetList
            Set salesReceiptRetList = response.Detail
 
            Dim cursrcpt2 As ISalesReceiptRet
 
            Dim insSQL2 As String
            For j = 0 To salesReceiptRetList.Count - 1
                insSQL2 = "INSERT INTO SalesReceipts " _
                    & "(SalesReceiptID, QBEditSequence, CkNo, Addr1, Addr2, Addr3, City, State, Zip, TDate, ReceiptNo) " _
                    & "VALUES " _
                    & "("
 
                Set cursrcpt2 = salesReceiptRetList.GetAt(j)
                    'go through all of the elements of ISalesReceiptRet
                    'place each value into associated Access field
                    insSQL2 = insSQL2 & "'" & cursrcpt2.TxnID.GetValue & "',"
                    insSQL2 = insSQL2 & "'" & cursrcpt2.EditSequence.GetValue & "',"
                    If (Not cursrcpt2.CheckNumber Is Nothing) Then
                        insSQL2 = insSQL2 & "'" & cursrcpt2.CheckNumber.GetValue & "',"
                    Else
                        insSQL2 = insSQL2 & "'',"
                    End If
                    If (Not cursrcpt2.BillAddress.Addr1 Is Nothing) Then
                        insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.Addr1.GetValue & "',"
                    Else
                        insSQL2 = insSQL2 & "'',"
                    End If
 
                    If (Not cursrcpt2.BillAddress.Addr2 Is Nothing) Then
                        insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.Addr2.GetValue & "',"
                    Else
                        insSQL2 = insSQL2 & "'',"
                    End If
                    If (Not cursrcpt2.BillAddress.Addr3 Is Nothing) Then
                        insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.Addr3.GetValue & "',"
                    Else
                        insSQL2 = insSQL2 & "'',"
                    End If
 
                    If (Not cursrcpt2.BillAddress.City Is Nothing) Then
                        insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.City.GetValue & "',"
                    Else
                        insSQL2 = insSQL2 & "'',"
                    End If
                    If (Not cursrcpt2.BillAddress.State Is Nothing) Then
                        insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.State.GetValue & "',"
                    Else
                        insSQL2 = insSQL2 & "'',"
                    End If
                    If (Not cursrcpt2.BillAddress.PostalCode Is Nothing) Then
                        insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.PostalCode.GetValue & "',"
                    Else
                        insSQL2 = insSQL2 & "'',"
                    End If
                    If (Not cursrcpt2.TxnDate Is Nothing) Then
                        insSQL2 = insSQL2 & "'" & cursrcpt2.TxnDate.GetValue & "',"
                    Else
                        insSQL2 = insSQL2 & "'',"
                    End If
                    'RefNumber is the SaleNo
                    If (Not cursrcpt2.RefNumber Is Nothing) Then
                        insSQL2 = insSQL2 & "'" & cursrcpt2.RefNumber.GetValue & "');"
                    Else
                        insSQL2 = insSQL2 & "'');"
                    End If
                    accessDB.Execute insSQL2
                    'Get the value of the ISalesReceiptRet.ORSalesReceiptLineRetList element
                    Dim insSQL3 As String
                    Dim k As Integer
                    For k = 0 To salesReceiptRet.ORSalesReceiptLineRetList.Count - 1
                      insSQL3 = "INSERT INTO SalesReceiptLine " _
                      & "(SalesReceiptID, QBEditSequence, item, qty, desc, amount) " _
                      & "VALUES " _
                      & "("
 
                       Dim orSalesReceiptLineRet84 As IORSalesReceiptLineRet
                       Set orSalesReceiptLineRet84 = salesReceiptRet.ORSalesReceiptLineRetList.GetAt(k)
                       insSQL3 = insSQL3 & "'" & orSalesReceiptLineRet84.SalesReceiptLineRet.TxnLineID.GetValue & "',"
                       insSQL3 = insSQL3 & "'" & orSalesReceiptLineRet84.SalesReceiptLineRet.EditSequence.GetValue & "',"
                       If (Not orSalesReceiptLineRet84.SalesReceiptLineRet.ItemRef Is Nothing) Then
                           'get fullname value
                           Dim fullName86 As String
                           fullName86 = orSalesReceiptLineRet84.SalesReceiptLineRet.ItemRef.FullName.GetValue
                           insSQL3 = insSQL3 & "'" & fullName86 & "',"
                       Else
                           insSQL3 = insSQL3 & "'',"
                       End If
                       If (Not orSalesReceiptLineRet84.SalesReceiptLineRet.Quantity Is Nothing) Then
                          Dim quantity88 As Double
                          quantity88 = orSalesReceiptLineRet84.SalesReceiptLineRet.Quantity.GetValue
                          insSQL3 = insSQL3 & "'" & quantity88 & "',"
                       Else
                           insSQL3 = insSQL3 & "'',"
                       End If
                       If (Not orSalesReceiptLineRet84.SalesReceiptLineRet.Desc Is Nothing) Then
                          Dim desc87 As String
                          desc87 = orSalesReceiptLineRet84.SalesReceiptLineRet.Desc.GetValue
                          insSQL3 = insSQL3 & "'" & desc87 & "',"
                       Else
                           insSQL3 = insSQL3 & "'',"
                       End If
                       If (Not orSalesReceiptLineRet84.SalesReceiptLineRet.Amount Is Nothing) Then
                          insSQL3 = insSQL3 & "'" & orSalesReceiptLineRet84.SalesReceiptLineRet.Amount.GetValue & "');"
                       Else
                          insSQL3 = insSQL3 & "'');"
                       End If
 
                       accessDB.Execute insSQL3
                    Next k
            Next j
        End If
      End If
    Next i
    'End ParseSRQueryRs
 
    'Close the session and connection with QuickBooks
    SessMgr.EndSession
    bSessionBegun = False
    SessMgr.CloseConnection
    bConnectionOpen = False
    Set SessMgr = Nothing
 
    Dim strMsg2 As String
    strMsg2 = "Press ok"
    MsgBox strMsg2
 
    Exit Sub
Errs:
    MsgBox "HRESULT = " & Err.Number & "(" & Hex(Err.Number) & ") " & vbCrLf & vbCrLf & Err.Description, vbOKOnly, "Error"
 
    'SampleCodeForm.ErrorMsg.Text = Err.Description
 
    ' Close the session and connection with QuickBooks.
    If (bSessionBegun) Then
      SessMgr.EndSession
    End If
    If (bConnectionOpen) Then
      SessMgr.CloseConnection
    End If
 
End Sub 'end Command2_Click

[NeoPa]

I'm afraid that we don't supply a code checking or fixing service here.
If you can ask a reasonable question (See FAQ - How to Ask a Question) or want specific help with a line or two of code then that can often be accommodated. This certainly doesn't fit into either of those categories.

ADMIN.

[agrych]

Since I didn't know where in the code my problem lies, I posted all the code. I explained the problem I was having - which was the empty list.

Apparently I misunderstood this forum since I was following the section in the "how to ask a question"section that states "Give as much detail as possible When you post a question or problem, express the situation clearly and concisely and include all relevant information, code used, data used, result expected, result achieved and any error codes or messages that you get as a result."

[NeoPa]

Clearly you've misunderstood something if you think your question even remotely conforms to the instructions in that paragraph.