By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

QuickBooks to MS Access for Sales Receipts

P: 2
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.  
  3.   'On Error GoTo Errs
  4.     Dim Country As String
  5.     Country = "US"
  6.     'We want to know if we've begun a session so we can end it if an
  7.     'error sends us to the exception handler.
  8.     Dim bSessionBegun As Boolean
  9.     bSessionBegun = False
  10.     Dim bConnectionOpen As Boolean
  11.     bConnectionOpen = False
  12.  
  13.     Dim accessDB As Database
  14.     Set accessDB = CurrentDb
  15.     If (accessDB Is Nothing) Then
  16.         Exit Sub
  17.     End If
  18.  
  19.     'Create the session manager object
  20.     Dim SessMgr As New QBSessionManager
  21.  
  22.     'Create the message set request object for the specific version messages
  23.     Dim requestMsgSet As IMsgSetRequest
  24.     Set requestMsgSet = SessMgr.CreateMsgSetRequest("US", 3, 0)
  25.     'requestMsgSet.Attributes.OnError = roeContinue
  26.  
  27.     'Connect to QuickBooks and begin a session
  28.     SessMgr.OpenConnection "", "SDK SalesReceipts Data"
  29.     bConnectionOpen = True
  30.     SessMgr.BeginSession "", omDontCare
  31.     bSessionBegun = True
  32.  
  33.    'Perform the request and obtain a response from QuickBooks
  34.     Dim responseMsgSet As IMsgSetResponse
  35.     Set responseMsgSet = SessMgr.DoRequests(requestMsgSet)
  36.  
  37.     'Build SalesReceipts Query
  38.  
  39.     'add the request to the message set request object
  40.     Dim query2 As ISalesReceiptQuery
  41.     Set query2 = requestMsgSet.AppendSalesReceiptQueryRq
  42.  
  43.     'Set the value of the ISalesReceiptQuery.IncludeLineItems element
  44.     query2.IncludeLineItems.SetValue (True)
  45.  
  46.      'begin ParseSRQueryRs
  47.     If (responseMsgSet Is Nothing) Then
  48.        MsgBox "No Detail Available for responseMsgSet"
  49.        Exit Sub
  50.     End If
  51.  
  52.     Dim responseList As IResponseList
  53.     Set responseList = responseMsgSet.responseList
  54.     If (responseList Is Nothing) Then
  55.       MsgBox "No Detail Available for responseList"
  56.       Exit Sub
  57.     End If
  58.  
  59.  
  60.     'Go through all of the responses in the list
  61.     Dim i As Integer
  62.     For i = 0 To responseList.Count - 1
  63.       Dim response As IResponse
  64.       Set response = responseList.GetAt(i)
  65.  
  66.       If (response.StatusCode = 0) Then
  67.         Dim respType2 As IResponseType
  68.         Set respType2 = response.Type
  69.         Dim j As Integer
  70.         'Check for SalesReceiptQueryRs
  71.         If (respType2.GetValue = rtSalesReceiptQueryRs) Then
  72.             Dim salesReceiptRetList As ISalesReceiptRetList
  73.             Set salesReceiptRetList = response.Detail
  74.  
  75.             Dim cursrcpt2 As ISalesReceiptRet
  76.  
  77.             Dim insSQL2 As String
  78.             For j = 0 To salesReceiptRetList.Count - 1
  79.                 insSQL2 = "INSERT INTO SalesReceipts " _
  80.                     & "(SalesReceiptID, QBEditSequence, CkNo, Addr1, Addr2, Addr3, City, State, Zip, TDate, ReceiptNo) " _
  81.                     & "VALUES " _
  82.                     & "("
  83.  
  84.                 Set cursrcpt2 = salesReceiptRetList.GetAt(j)
  85.                     'go through all of the elements of ISalesReceiptRet
  86.                     'place each value into associated Access field
  87.                     insSQL2 = insSQL2 & "'" & cursrcpt2.TxnID.GetValue & "',"
  88.                     insSQL2 = insSQL2 & "'" & cursrcpt2.EditSequence.GetValue & "',"
  89.                     If (Not cursrcpt2.CheckNumber Is Nothing) Then
  90.                         insSQL2 = insSQL2 & "'" & cursrcpt2.CheckNumber.GetValue & "',"
  91.                     Else
  92.                         insSQL2 = insSQL2 & "'',"
  93.                     End If
  94.                     If (Not cursrcpt2.BillAddress.Addr1 Is Nothing) Then
  95.                         insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.Addr1.GetValue & "',"
  96.                     Else
  97.                         insSQL2 = insSQL2 & "'',"
  98.                     End If
  99.  
  100.                     If (Not cursrcpt2.BillAddress.Addr2 Is Nothing) Then
  101.                         insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.Addr2.GetValue & "',"
  102.                     Else
  103.                         insSQL2 = insSQL2 & "'',"
  104.                     End If
  105.                     If (Not cursrcpt2.BillAddress.Addr3 Is Nothing) Then
  106.                         insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.Addr3.GetValue & "',"
  107.                     Else
  108.                         insSQL2 = insSQL2 & "'',"
  109.                     End If
  110.  
  111.                     If (Not cursrcpt2.BillAddress.City Is Nothing) Then
  112.                         insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.City.GetValue & "',"
  113.                     Else
  114.                         insSQL2 = insSQL2 & "'',"
  115.                     End If
  116.                     If (Not cursrcpt2.BillAddress.State Is Nothing) Then
  117.                         insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.State.GetValue & "',"
  118.                     Else
  119.                         insSQL2 = insSQL2 & "'',"
  120.                     End If
  121.                     If (Not cursrcpt2.BillAddress.PostalCode Is Nothing) Then
  122.                         insSQL2 = insSQL2 & "'" & cursrcpt2.BillAddress.PostalCode.GetValue & "',"
  123.                     Else
  124.                         insSQL2 = insSQL2 & "'',"
  125.                     End If
  126.                     If (Not cursrcpt2.TxnDate Is Nothing) Then
  127.                         insSQL2 = insSQL2 & "'" & cursrcpt2.TxnDate.GetValue & "',"
  128.                     Else
  129.                         insSQL2 = insSQL2 & "'',"
  130.                     End If
  131.                     'RefNumber is the SaleNo
  132.                     If (Not cursrcpt2.RefNumber Is Nothing) Then
  133.                         insSQL2 = insSQL2 & "'" & cursrcpt2.RefNumber.GetValue & "');"
  134.                     Else
  135.                         insSQL2 = insSQL2 & "'');"
  136.                     End If
  137.                     accessDB.Execute insSQL2
  138.                     'Get the value of the ISalesReceiptRet.ORSalesReceiptLineRetList element
  139.                     Dim insSQL3 As String
  140.                     Dim k As Integer
  141.                     For k = 0 To salesReceiptRet.ORSalesReceiptLineRetList.Count - 1
  142.                       insSQL3 = "INSERT INTO SalesReceiptLine " _
  143.                       & "(SalesReceiptID, QBEditSequence, item, qty, desc, amount) " _
  144.                       & "VALUES " _
  145.                       & "("
  146.  
  147.                        Dim orSalesReceiptLineRet84 As IORSalesReceiptLineRet
  148.                        Set orSalesReceiptLineRet84 = salesReceiptRet.ORSalesReceiptLineRetList.GetAt(k)
  149.                        insSQL3 = insSQL3 & "'" & orSalesReceiptLineRet84.SalesReceiptLineRet.TxnLineID.GetValue & "',"
  150.                        insSQL3 = insSQL3 & "'" & orSalesReceiptLineRet84.SalesReceiptLineRet.EditSequence.GetValue & "',"
  151.                        If (Not orSalesReceiptLineRet84.SalesReceiptLineRet.ItemRef Is Nothing) Then
  152.                            'get fullname value
  153.                            Dim fullName86 As String
  154.                            fullName86 = orSalesReceiptLineRet84.SalesReceiptLineRet.ItemRef.FullName.GetValue
  155.                            insSQL3 = insSQL3 & "'" & fullName86 & "',"
  156.                        Else
  157.                            insSQL3 = insSQL3 & "'',"
  158.                        End If
  159.                        If (Not orSalesReceiptLineRet84.SalesReceiptLineRet.Quantity Is Nothing) Then
  160.                           Dim quantity88 As Double
  161.                           quantity88 = orSalesReceiptLineRet84.SalesReceiptLineRet.Quantity.GetValue
  162.                           insSQL3 = insSQL3 & "'" & quantity88 & "',"
  163.                        Else
  164.                            insSQL3 = insSQL3 & "'',"
  165.                        End If
  166.                        If (Not orSalesReceiptLineRet84.SalesReceiptLineRet.Desc Is Nothing) Then
  167.                           Dim desc87 As String
  168.                           desc87 = orSalesReceiptLineRet84.SalesReceiptLineRet.Desc.GetValue
  169.                           insSQL3 = insSQL3 & "'" & desc87 & "',"
  170.                        Else
  171.                            insSQL3 = insSQL3 & "'',"
  172.                        End If
  173.                        If (Not orSalesReceiptLineRet84.SalesReceiptLineRet.Amount Is Nothing) Then
  174.                           insSQL3 = insSQL3 & "'" & orSalesReceiptLineRet84.SalesReceiptLineRet.Amount.GetValue & "');"
  175.                        Else
  176.                           insSQL3 = insSQL3 & "'');"
  177.                        End If
  178.  
  179.                        accessDB.Execute insSQL3
  180.                     Next k
  181.             Next j
  182.         End If
  183.       End If
  184.     Next i
  185.     'End ParseSRQueryRs
  186.  
  187.     'Close the session and connection with QuickBooks
  188.     SessMgr.EndSession
  189.     bSessionBegun = False
  190.     SessMgr.CloseConnection
  191.     bConnectionOpen = False
  192.     Set SessMgr = Nothing
  193.  
  194.     Dim strMsg2 As String
  195.     strMsg2 = "Press ok"
  196.     MsgBox strMsg2
  197.  
  198.     Exit Sub
  199. Errs:
  200.     MsgBox "HRESULT = " & Err.Number & "(" & Hex(Err.Number) & ") " & vbCrLf & vbCrLf & Err.Description, vbOKOnly, "Error"
  201.  
  202.     'SampleCodeForm.ErrorMsg.Text = Err.Description
  203.  
  204.     ' Close the session and connection with QuickBooks.
  205.     If (bSessionBegun) Then
  206.       SessMgr.EndSession
  207.     End If
  208.     If (bConnectionOpen) Then
  209.       SessMgr.CloseConnection
  210.     End If
  211.  
  212. End Sub 'end Command2_Click
Nov 25 '07 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,306
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.
Nov 28 '07 #2

P: 2
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."
Dec 6 '07 #3

NeoPa
Expert Mod 15k+
P: 31,306
Clearly you've misunderstood something if you think your question even remotely conforms to the instructions in that paragraph.
Dec 9 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.