We help IT Professionals succeed at work.

Quickbooks to Access Intergration using the SDK

jadaiber
jadaiber asked
on
2,306 Views
Last Modified: 2013-11-27
I've included the code I'm using to query salesorders and salesorder lines from Quickbooks into two MS Access database tables.  The code runs without error, but only the salesorder header information is returned without any line detail.   Can anyone see what I'm missing.  I'm using Acess 2003 / Quickbooks Enterprise 7.0/ Quickbooks SDK 6.  Thanks for the help



Private Sub Command3_Click()
    Dim accessDB As Database
    Set accessDB = CurrentDb
    If (accessDB Is Nothing) Then
        Exit Sub
    End If
   
   
    Dim sessionManager As New QBSessionManager
   
    Dim requestMsgSet As IMsgSetRequest
    Set requestMsgSet = sessionManager.CreateMsgSetRequest("US", 6, 0)
    requestMsgSet.Attributes.OnError = roeContinue
   
    If (requestMsgSet Is Nothing) Then
        Exit Sub
    End If
   
    Dim salesOrderQuery As ISalesOrderQuery
    Set salesOrderQuery = requestMsgSet.AppendSalesOrderQueryRq

   
    salesOrderQuery.ORTxnNoAccountQuery.TxnFilterNoAccount.ORDateRangeFilter.ModifiedDateRangeFilter.FromModifiedDate.SetValue ModDate, False
   
    salesOrderQuery.IncludeLineItems.SetValue True
   
    salesOrderQuery.IncludeRetElementList.Add ("TxnID")
    salesOrderQuery.IncludeRetElementList.Add ("TimeModified")
    salesOrderQuery.IncludeRetElementList.Add ("CustomerRef")
    salesOrderQuery.IncludeRetElementList.Add ("RefNumber")
    salesOrderQuery.IncludeRetElementList.Add ("PONumber")
 
    salesOrderQuery.IncludeRetElementList.Add ("SalesOrderLineRet.TxnLineID")

   
   
    sessionManager.OpenConnection "", "TT"
    sessionManager.BeginSession "", omDontCare
   
    Dim responseMsgSet As IMsgSetResponse
    Set responseMsgSet = sessionManager.DoRequests(requestMsgSet)
   
    sessionManager.EndSession
    sessionManager.CloseConnection
   
    If (responseMsgSet Is Nothing) Then
        Exit Sub
    End If
   
    Dim responseList As IResponseList
    Set responseList = responseMsgSet.responseList
    If (responseList Is Nothing) Then
        Exit Sub
    End If
   
   
    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
            If (Not response.Detail Is Nothing) Then
                Dim responsetype As Integer
                responsetype = response.Type.GetValue
                Dim j As Integer
                If (responsetype = rtSalesOrderQueryRs) Then
                    Dim salesOrderRetList As ISalesOrderRetList
                    Set salesOrderRetList = response.Detail
                    For j = 0 To salesOrderRetList.Count - 1
                        Dim salesOrderRet As ISalesOrderRet
                        Set salesOrderRet = salesOrderRetList.GetAt(j)
                        If (salesOrderRet Is Nothing) Then
                            Exit Sub
                        End If
                        Dim insSQL As String
                       
                        insSQL = "INSERT INTO SalesOrder" _
                            & "(SalesOrderID,DateModified,CustomerListID,CustomerFullName, ReferenceNumber,PurchaseOrderNumber)" _
                            & "VALUES" _
                            & "("
                   
                                                               
                            insSQL = insSQL & "'" & salesOrderRet.TxnID.GetValue & "',"
                            insSQL = insSQL & "'" & salesOrderRet.TimeModified.GetValue & "',"
                            insSQL = insSQL & "'" & salesOrderRet.CustomerRef.ListID.GetValue & "',"
                            insSQL = insSQL & "'" & salesOrderRet.CustomerRef.FullName.GetValue & "',"
                            insSQL = insSQL & "'" & salesOrderRet.RefNumber.GetValue & "',"
                            If (Not salesOrderRet.PONumber Is Nothing) Then
                                insSQL = insSQL & "'" & salesOrderRet.PONumber.GetValue & "');"
                            Else
                                insSQL = insSQL & "'');"
                            End If
                            accessDB.Execute insSQL
                   
                                If (Not salesOrderRet.ORSalesOrderLineRetList Is Nothing) Then
                                Dim m As Integer
                                    For m = 0 To salesOrderRet.ORSalesOrderLineRetList.Count - 1
                                        Dim orSalesOrderLineRet As IORSalesOrderLineRet
                                        Set orSalesOrderLineRet = salesOrderRet.ORSalesOrderLineRetList.GetAt(m)
                               
                                        If (Not orSalesOrderLineRet.SalesOrderLineRet Is Nothing) Then
                                            Dim insSQL2 As String
                                            insSQL2 = "INSERT INTO SalesOrderLine" _
                                                & "(SalesOrderLineID)" _
                                                & "VALUES" _
                                                & "("
                                           
                                                insSQL2 = insSQL2 & "'" & orSalesOrderLineRet.SalesOrderLineRet.TxnLineID.GetValue & "',"
                                           
                                                insSQL2 = insSQL2 & "'');"
                                         
                                           
                                            accessDB.Execute insSQL2
                                   
                                        End If
                                Next m
                                 
                            End If
                   
                    Next j
                                   
                End If
                           
            End If
                   
        End If
         
    Next i
       
End Sub
Comment
Watch Question

Print your insSQL2  variable to the Immediate window to see what the final constructed sql statement is.

Then post up the SQL so as we can examine it

Thnx

Author

Commented:
I apoligizie, I'm a novice, I'm not sure how to print the insSQL2 variable to the immediate window.  Can you please explain?    
1. Debug the code
2. Step past the the final line that assigns to insSQL2
3. Menu View | Immediate Window - A debug pane should appear possibly at bottom of screen
4 Type ? insSQL2 - This should give the content of this variable

Author

Commented:
Nothing is returned in the immediate window when I type ? insSQL2 and Enter
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
You need to step through this code, line by line, until you determine exactly where the code is failing. The QB SDK is very unforgiving ... for example, do you know if you are actually getting to the line where you build the SQL for the item insert?

Author

Commented:
Yes I believe code is running completely through.  The salesorder header information is being inserted correctly into the salesorder table and if I plug in a text value into the insSQL2 line (insSQL = "INSERT INTO SalesOrderline(SalesorderlineID) VALUES ("testtext") ) that value is inserted ino the salesorderline table as expected.  If I had to guess I think the problem may be with the salesorderline data not being returned from Quickbooks.    
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I forget to handle the different line types before  doing the SQL INSERT:
 If (salesOrderRet.orSalesOrderLineRetList.GetAt(m).ortype = orsolrSalesOrderLineRet) Then

Thanks for the help!
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Glad you got it straight ... I've found the QB SDK to be far from intuitive and have found that what I __think__ is happening often is not even close to what is actually occurring ...

Commented:
Hi Jadabair do you have the fixed code? Cause Im having the same problem. thank you
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Kio7: If you need assistance, please post a new question. This question has long been closed, and it's very possible that Jadabair will never see your comment. If you're having troubles using the Quickbooks SDK, then post a question in the Quickbooks forum detailing your exact problem.

Author

Commented:
Here's the function I use to query Quickbooks Sales Orders.   I hope it helps.  
Option Compare Database
 
Function DoSalesOrderQueryRq(country As String, majorVersion As Integer, minorVersion As Integer)
  
  On Error GoTo Errs
  
    '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
  
    ' Create the session manager object.
    Dim sessionManager As New QBSessionManager
  
    ' Create the message set request object for the specific version messages.
    Dim requestMsgSet As IMsgSetRequest
    Set requestMsgSet = sessionManager.CreateMsgSetRequest(country, majorVersion, minorVersion)
    requestMsgSet.Attributes.OnError = roeContinue
  
    BuildSalesOrderQueryRq requestMsgSet, country
  
    ' Connect to QuickBooks and begin a session.
    sessionManager.OpenConnection "", "TT"
    bConnectionOpen = True
    sessionManager.BeginSession "", omDontCare
    bSessionBegun = True
  
    ' Perform the request and obtain a response from QuickBooks.
    Dim responseMsgSet As IMsgSetResponse
    Set responseMsgSet = sessionManager.DoRequests(requestMsgSet)
  
    ' Close the session and connection with QuickBooks.
    sessionManager.EndSession
    bSessionBegun = False
    sessionManager.CloseConnection
    bConnectionOpen = False
  
    ParseSalesOrderQueryRs responseMsgSet, country
    
    Dim accessDB As Database
    Set accessDB = CurrentDb
    If (accessDB Is Nothing) Then
      Exit Function
    End If
        
    Dim updateSQL As String
    updateSQL = "UPDATE QuickbooksSyncLog SET QuickbooksSyncLog.LastSync = now() WHERE (((QuickbooksSyncLog.SyncID)='SalesOrderImport'));"
    accessDB.Execute updateSQL
  
    Exit Function
  
Errs:
    MsgBox "HRESULT = " & Err.Number & " (" & Hex(Err.Number) & ") " & vbCrLf & vbCrLf & Err.Description, vbOKOnly, "Error"
  
   
    ' Close the session and connection with QuickBooks.
    If (bSessionBegun) Then
      sessionManager.EndSession
    End If
    If (bConnectionOpen) Then
      sessionManager.CloseConnection
    End If
  
End Function
  
Public Sub BuildSalesOrderQueryRq(requestMsgSet As IMsgSetRequest, country As String)
 
Dim LastSalesOrderImport As String
LastSalesOrderImport = DLookup("LastSync", "QuickbooksSyncLog", "[SyncID] = 'SalesOrderImport'")
Dim ThisSalesOrderImport As String
ThisSalesOrderImport = DateAdd("n", -1, LastSalesOrderImport)
  
  If (requestMsgSet Is Nothing) Then
    Exit Sub
  End If
  
  'Add the request to the message set request object.
  Dim salesOrderQuery As ISalesOrderQuery
  Set salesOrderQuery = requestMsgSet.AppendSalesOrderQueryRq
  
  'Set the elements of ISalesOrderQuery.
  
             
  salesOrderQuery.ORTxnNoAccountQuery.TxnFilterNoAccount.ORDateRangeFilter.ModifiedDateRangeFilter.FromModifiedDate.SetValue ThisSalesOrderImport, False
  
  ' Set the value of the ISalesOrderQuery.IncludeLineItems element.
  salesOrderQuery.IncludeLineItems.SetValue True
  
 
  
End Sub
 
  
Public Sub ParseSalesOrderQueryRs(responseMsgSet As IMsgSetResponse, country As String)
  
  If (responseMsgSet Is Nothing) Then
    Exit Sub
  End If
  
  Dim responseList As IResponseList
  Set responseList = responseMsgSet.responseList
  If (responseList Is Nothing) Then
    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)
  
    ' Check the status returned for the response.
    If (response.StatusCode = 0) Then
  
      ' Check to make sure the response is of the type we are expecting.
      If (Not response.Detail Is Nothing) Then
        Dim responsetype As Integer
        responsetype = response.Type.GetValue
        Dim j As Integer
        ' Check for SalesOrderQueryRs.
        If (responsetype = rtSalesOrderQueryRs) Then
          Dim salesOrderRetList As ISalesOrderRetList
          Set salesOrderRetList = response.Detail
          For j = 0 To salesOrderRetList.Count - 1
            ParseSalesOrderRet salesOrderRetList.GetAt(j), country
          Next j
        End If
      End If
    End If
  Next i
End Sub
  
Private Sub ParseSalesOrderRet(salesOrderRet As ISalesOrderRet, country As String)
  
  If (salesOrderRet Is Nothing) Then
    Exit Sub
  End If
  
  'Go through all of the elements of ISalesOrderRet.
  
  ' Get the value of the ISalesOrderRet.TxnID element.
  Dim txnid1 As String
  txnid1 = salesOrderRet.TxnID.GetValue
 
  ' Get the value of the ISalesOrderRet.EditSequence element.
  Dim editSequence4 As String
  editSequence4 = salesOrderRet.EditSequence.GetValue
 
  ' Get the value of the ISalesOrderRet.CustomerRef element.
  ' Get the FullName value.
 
  Dim fullname6 As String
  fullname6 = Replace(salesOrderRet.CustomerRef.FullName.GetValue, "'", "''")
  
  ' Get the ListID value.
  Dim listID6 As String
  listID6 = salesOrderRet.CustomerRef.ListID.GetValue
  
 
  ' Get the value of the ISalesOrderRet.TxnDate element.
  Dim txnDate9 As Date
  txnDate9 = salesOrderRet.TxnDate.GetValue
  
  ' Get the value of the ISalesOrderRet.RefNumber element.
  If (Not salesOrderRet.RefNumber Is Nothing) Then
    Dim refNumber10 As String
    refNumber10 = Replace(salesOrderRet.RefNumber.GetValue, "'", "''")
  End If
  
    
  If (Not salesOrderRet.ClassRef Is Nothing) Then
    If (Not salesOrderRet.ClassRef.FullName Is Nothing) Then
        Dim classRef2 As String
        classRef2 = Replace(salesOrderRet.ClassRef.FullName.GetValue, "'", "''")
    End If
        
  End If
  
 
 
  ' Get the value of the ISalesOrderRet.ShipAddress element.
  If (Not salesOrderRet.ShipAddress Is Nothing) Then
    ' Get the value of the IAddress.Addr1 element.
    If (Not salesOrderRet.ShipAddress.Addr1 Is Nothing) Then
      Dim addr126 As String
      addr126 = Replace(salesOrderRet.ShipAddress.Addr1.GetValue, "'", "''")
    End If
  
    ' Get the value of the IAddress.Addr2 element.
    If (Not salesOrderRet.ShipAddress.Addr2 Is Nothing) Then
      Dim addr227 As String
      addr227 = Replace(salesOrderRet.ShipAddress.Addr2.GetValue, "'", "''")
    End If
  
    ' Get the value of the IAddress.Addr3 element.
    If (Not salesOrderRet.ShipAddress.Addr3 Is Nothing) Then
      Dim addr328 As String
      addr328 = Replace(salesOrderRet.ShipAddress.Addr3.GetValue, "'", "''")
    End If
  
  
    ' Get the value of the IAddress.City element.
    If (Not salesOrderRet.ShipAddress.City Is Nothing) Then
      Dim city31 As String
      city31 = Replace(salesOrderRet.ShipAddress.City.GetValue, "'", "''")
    End If
  
    ' Get the value of the IAddress.State element.
    If (Not salesOrderRet.ShipAddress.State Is Nothing) Then
      Dim state32 As String
      state32 = salesOrderRet.ShipAddress.State.GetValue
    End If
  
    ' Get the value of the IAddress.PostalCode element.
    If (Not salesOrderRet.ShipAddress.PostalCode Is Nothing) Then
      Dim postalCode33 As String
      postalCode33 = salesOrderRet.ShipAddress.PostalCode.GetValue
    End If
 
    ' Get the value of the IAddress.Note element.
    If (Not salesOrderRet.ShipAddress.Note Is Nothing) Then
      Dim note35 As String
      note35 = Replace(salesOrderRet.ShipAddress.Note.GetValue, "'", "''")
    End If
  
  End If
 
  ' Get the value of the ISalesOrderRet.PONumber element.
  If (Not salesOrderRet.PONumber Is Nothing) Then
    Dim pONumber41 As String
    pONumber41 = Replace(salesOrderRet.PONumber.GetValue, "'", "''")
  End If
 
  
  ' Get the value of the ISalesOrderRet.TotalAmount element.
  If (Not salesOrderRet.TotalAmount Is Nothing) Then
    Dim totalAmount52 As Double
    totalAmount52 = salesOrderRet.TotalAmount.GetValue
  End If
  
  ' Get the value of the ISalesOrderRet.IsManuallyClosed element.
  If (Not salesOrderRet.IsManuallyClosed Is Nothing) Then
    Dim isManuallyClosed53 As Boolean
    isManuallyClosed53 = salesOrderRet.IsManuallyClosed.GetValue
  End If
  
  ' Get the value of the ISalesOrderRet.IsFullyInvoiced element.
  If (Not salesOrderRet.IsFullyInvoiced Is Nothing) Then
    Dim isFullyInvoiced54 As Boolean
    isFullyInvoiced54 = salesOrderRet.IsFullyInvoiced.GetValue
  End If
  
  ' Get the value of the ISalesOrderRet.Memo element.
  If (Not salesOrderRet.Memo Is Nothing) Then
    Dim memo55 As String
    memo55 = Replace(salesOrderRet.Memo.GetValue, "'", "''")
  End If
 
   
  ' Get the value of the ISalesOrderRet.Other element.
  If (Not salesOrderRet.Other Is Nothing) Then
    Dim other61 As String
    other61 = Replace(salesOrderRet.Other.GetValue, "'", "''")
  End If
 
  If (Not salesOrderRet.TermsRef Is Nothing) Then
   If (Not salesOrderRet.TermsRef.FullName Is Nothing) Then
     Dim terms63 As String
     terms63 = Replace(salesOrderRet.TermsRef.FullName.GetValue, "'", "''")
   End If
  End If
  
  If (Not salesOrderRet.SalesRepRef Is Nothing) Then
    If (Not salesOrderRet.SalesRepRef.FullName Is Nothing) Then
      Dim SalesRep62 As String
      SalesRep62 = Replace(salesOrderRet.SalesRepRef.FullName.GetValue, "'", "''")
    End If
  End If
  
 
  Dim accessDB As Database
  Set accessDB = CurrentDb
  If (accessDB Is Nothing) Then
    Exit Sub
  End If
  
  Dim uSQL As String
    uSQL = "UPDATE SalesOrderQB "
    uSQL = uSQL & "SET EditSequence='" & editSequence4
    uSQL = uSQL & "', CustomerRefListID='" & listID6
    uSQL = uSQL & "', CustomerRefFullName='" & fullname6
    uSQL = uSQL & "', TxnDate='" & txnDate9
    uSQL = uSQL & "', RefNumber='" & refNumber10
    uSQL = uSQL & "', Class='" & classRef2
    uSQL = uSQL & "', ShipAddressAddr1='" & addr126
    uSQL = uSQL & "', ShipAddressAddr2='" & addr227
    uSQL = uSQL & "', ShipAddressAddr3='" & addr328
    uSQL = uSQL & "', ShipAddressCity='" & city31
    uSQL = uSQL & "', ShipAddressState='" & state32
    uSQL = uSQL & "', ShipAddressPostalCode='" & postalCode33
    uSQL = uSQL & "', ShipAddressNote='" & note35
    uSQL = uSQL & "', PONumber='" & pONumber41
    uSQL = uSQL & "', TotalAmount='" & totalAmount52
    uSQL = uSQL & "', IsManuallyClosed='" & isManuallyClosed53
    uSQL = uSQL & "', IsFullyInvoiced='" & isFullyInvoiced54
    uSQL = uSQL & "', Memo='" & memo55
    uSQL = uSQL & "', Other='" & other61
    uSQL = uSQL & "', Terms='" & terms63
    uSQL = uSQL & "', SalesRep='" & SalesRep62 & "' "
 
    uSQL = uSQL & "WHERE TxnID='" & txnid1 & "';"
    
    accessDB.Execute uSQL
 
  
  Dim insSQL As String
  
  insSQL = "INSERT INTO SalesOrderQB" _
  & "(TxnID, EditSequence, CustomerRefListID, CustomerRefFullName,  TxnDate, RefNumber, Class," _
  & "ShipAddressAddr1, ShipAddressAddr2, ShipAddressAddr3, ShipAddressCity, ShipAddressState," _
  & "ShipAddressPostalCode, ShipAddressNote, PONumber," _
  & "TotalAmount, IsManuallyClosed, IsFullyInvoiced, Memo, Other, Terms, SalesRep)" _
  & "VALUES" _
  & "("
  
  insSQL = insSQL & "'" & txnid1 & "',"
  insSQL = insSQL & "'" & editSequence4 & "',"
  insSQL = insSQL & "'" & listID6 & "',"
  insSQL = insSQL & "'" & fullname6 & "',"
  insSQL = insSQL & "'" & txnDate9 & "',"
  insSQL = insSQL & "'" & refNumber10 & "',"
  insSQL = insSQL & "'" & classRef2 & "',"
  insSQL = insSQL & "'" & addr126 & "',"
  insSQL = insSQL & "'" & addr227 & "',"
  insSQL = insSQL & "'" & addr328 & "',"
  insSQL = insSQL & "'" & city31 & "',"
  insSQL = insSQL & "'" & state32 & "',"
  insSQL = insSQL & "'" & postalCode33 & "',"
  insSQL = insSQL & "'" & note35 & "',"
  insSQL = insSQL & "'" & pONumber41 & "',"
  insSQL = insSQL & "'" & totalAmount52 & "',"
  insSQL = insSQL & "'" & isManuallyClosed53 & "',"
  insSQL = insSQL & "'" & isFullyInvoiced54 & "',"
  insSQL = insSQL & "'" & memo55 & "',"
  insSQL = insSQL & "'" & other61 & "',"
  insSQL = insSQL & "'" & terms63 & "',"
  insSQL = insSQL & "'" & SalesRep62 & "');"
  
  accessDB.Execute insSQL
  
  
   Dim uSQL_T As String
   uSQL_T = "UPDATE SalesOrderTracking "
   uSQL_T = uSQL_T & "SET RelatedCustomerJob='" & listID6 & "'"
 
   uSQL_T = uSQL_T & "WHERE TxnID='" & txnid1 & "';"
    
   accessDB.Execute uSQL_T
    
    
    
  
  Dim insSQL_T As String
  
  insSQL_T = "INSERT INTO SalesOrderTracking" _
  & "(TxnID, RelatedCustomerJob)" _
  & "VALUES" _
  & "("
  
  insSQL_T = insSQL_T & "'" & txnid1 & "',"
  insSQL_T = insSQL_T & "'" & listID6 & "');"
  
  accessDB.Execute insSQL_T
 
  
  ' Get the value of the ISalesOrderRet.ORSalesOrderLineRetList element.
  If (Not salesOrderRet.ORSalesOrderLineRetList Is Nothing) Then
    Dim m As Integer
    For m = 0 To salesOrderRet.ORSalesOrderLineRetList.Count - 1
      Dim orSalesOrderLineRet72 As IORSalesOrderLineRet
      Set orSalesOrderLineRet72 = salesOrderRet.ORSalesOrderLineRetList.GetAt(m)
      ' Get the value of the IORSalesOrderLineRet.SalesOrderLineRet element.
      If (Not orSalesOrderLineRet72.SalesOrderLineRet Is Nothing) Then
        ' Get the value of the ISalesOrderLineRet.TxnLineID element.
        Dim txnLineID73 As String
        txnLineID73 = orSalesOrderLineRet72.SalesOrderLineRet.TxnLineID.GetValue
  
        ' Get the value of the ISalesOrderLineRet.ItemRef element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.ItemRef Is Nothing) Then
          ' Get the FullName value.
          'If (Not orSalesOrderLineRet72.SalesOrderLineRet.ItemRef.FullName Is Nothing) Then
            Dim fullName74 As String
            fullName74 = Replace(orSalesOrderLineRet72.SalesOrderLineRet.ItemRef.FullName.GetValue, "'", "''")
            Else
            fullName74 = ""
          'End If
        End If
  
        ' Get the value of the ISalesOrderLineRet.Desc element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.Desc Is Nothing) Then
          Dim desc75 As String
          desc75 = Replace(orSalesOrderLineRet72.SalesOrderLineRet.Desc.GetValue, "'", "''")
          Else
          desc75 = ""
        End If
  
        ' Get the value of the ISalesOrderLineRet.Quantity element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.Quantity Is Nothing) Then
          Dim quantity76 As Double
          quantity76 = orSalesOrderLineRet72.SalesOrderLineRet.Quantity.GetValue
          Else
          quantity76 = 0
        End If
        ' Get the value of the ISalesOrderLineRet.UnitOfMeasure element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.UnitOfMeasure Is Nothing) Then
          Dim unitOfMeasure76 As String
          unitOfMeasure76 = orSalesOrderLineRet72.SalesOrderLineRet.UnitOfMeasure.GetValue
          Else
          unitOfMeasure76 = ""
        End If
  
        ' Get the value of the ISalesOrderLineRet.ORRate element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.ORRate Is Nothing) Then
          ' Get the value of the IORRate.Rate element.
          If (Not orSalesOrderLineRet72.SalesOrderLineRet.ORRate.Rate Is Nothing) Then
            Dim rate77 As Double
            rate77 = orSalesOrderLineRet72.SalesOrderLineRet.ORRate.Rate.GetValue
          End If
  
          ' Get the value of the IORRate.RatePercent element.
          If (Not orSalesOrderLineRet72.SalesOrderLineRet.ORRate.RatePercent Is Nothing) Then
            Dim ratePercent78 As Double
            ratePercent78 = orSalesOrderLineRet72.SalesOrderLineRet.ORRate.RatePercent.GetValue
          End If
  
        End If
  
        ' Get the value of the ISalesOrderLineRet.ClassRef element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.ClassRef Is Nothing) Then
          ' Get the FullName value.
          'If (Not orSalesOrderLineRet72.SalesOrderLineRet.ClassRef.FullName Is Nothing) Then
            Dim fullName79 As String
            fullName79 = Replace(orSalesOrderLineRet72.SalesOrderLineRet.ClassRef.FullName.GetValue, "'", "''")
            Else
            fullName79 = ""
          'End If
        End If
  
        ' Get the value of the ISalesOrderLineRet.Amount element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.Amount Is Nothing) Then
          Dim amount80 As Double
          amount80 = orSalesOrderLineRet72.SalesOrderLineRet.Amount.GetValue
        End If
  
        ' Get the value of the ISalesOrderLineRet.SalesTaxCodeRef element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.SalesTaxCodeRef Is Nothing) Then
          If (Not orSalesOrderLineRet72.SalesOrderLineRet.SalesTaxCodeRef.FullName Is Nothing) Then
          ' Get the FullName value.
            Dim fullName81 As String
            fullName81 = orSalesOrderLineRet72.SalesOrderLineRet.SalesTaxCodeRef.FullName.GetValue
          End If
        End If
  
        ' Get the value of the ISalesOrderLineRet.Invoiced element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.Invoiced Is Nothing) Then
          Dim invoiced82 As Double
          invoiced82 = orSalesOrderLineRet72.SalesOrderLineRet.Invoiced.GetValue
        End If
  
        ' Get the value of the ISalesOrderLineRet.IsManuallyClosed element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.IsManuallyClosed Is Nothing) Then
          Dim isManuallyClosed83 As Boolean
          isManuallyClosed83 = orSalesOrderLineRet72.SalesOrderLineRet.IsManuallyClosed.GetValue
        End If
  
        ' Get the value of the ISalesOrderLineRet.Other1 element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.Other1 Is Nothing) Then
          Dim other184 As String
          other184 = orSalesOrderLineRet72.SalesOrderLineRet.Other1.GetValue
          Else
          other184 = ""
        End If
  
        ' Get the value of the ISalesOrderLineRet.Other2 element.
        If (Not orSalesOrderLineRet72.SalesOrderLineRet.Other2 Is Nothing) Then
          Dim other285 As String
          other285 = orSalesOrderLineRet72.SalesOrderLineRet.Other2.GetValue
          Else
          other285 = ""
        End If
            
        Dim uSQL2 As String
        uSQL2 = "UPDATE SalesOrderLine "
        uSQL2 = uSQL2 & "SET TxnID='" & txnid1
        uSQL2 = uSQL2 & "', EditSequence='" & editSequence4
        uSQL2 = uSQL2 & "', ItemRefFullName='" & fullName74
        uSQL2 = uSQL2 & "', Description='" & desc75
        uSQL2 = uSQL2 & "', Quantity='" & quantity76
        uSQL2 = uSQL2 & "', UnitOfMeasure='" & unitOfMeasure76
        uSQL2 = uSQL2 & "', Rate='" & rate77
        uSQL2 = uSQL2 & "', RatePercent='" & ratePercent78
        uSQL2 = uSQL2 & "', ClassRefFullName='" & fullName79
        uSQL2 = uSQL2 & "', Amount='" & amount80
        uSQL2 = uSQL2 & "', SalesTaxCodeRefFullName='" & fullName81
        uSQL2 = uSQL2 & "', Invoiced='" & invoiced82
        uSQL2 = uSQL2 & "', IsManuallyClosed='" & isManuallyClosed83
        uSQL2 = uSQL2 & "', other1='" & other184
        uSQL2 = uSQL2 & "', other2='" & other285 & "' "
   
        uSQL2 = uSQL2 & "WHERE TxnLineID='" & txnLineID73 & "';"
        
        accessDB.Execute uSQL2
        
        
        Dim insSQL2 As String
  
        insSQL2 = "INSERT INTO SalesOrderLine" _
        & "(TxnLineID, TxnID, EditSequence, ItemRefFullName,Description,Quantity,UnitOfMeasure,Rate,RatePercent,ClassRefFullName,Amount,SalesTaxCodeRefFullName,Invoiced,IsManuallyClosed,Other1,Other2)" _
        & "VALUES" _
        & "("
        
        insSQL2 = insSQL2 & "'" & txnLineID73 & "',"
        insSQL2 = insSQL2 & "'" & txnid1 & "',"
        insSQL2 = insSQL2 & "'" & editSequence4 & "',"
        insSQL2 = insSQL2 & "'" & fullName74 & "',"
        insSQL2 = insSQL2 & "'" & desc75 & "',"
        insSQL2 = insSQL2 & "'" & quantity76 & "',"
        insSQL2 = insSQL2 & "'" & unitOfMeasure76 & "',"
        insSQL2 = insSQL2 & "'" & rate77 & "',"
        insSQL2 = insSQL2 & "'" & ratePercent78 & "',"
        insSQL2 = insSQL2 & "'" & fullName79 & "',"
        insSQL2 = insSQL2 & "'" & amount80 & "',"
        insSQL2 = insSQL2 & "'" & fullName81 & "',"
        insSQL2 = insSQL2 & "'" & invoiced82 & "',"
        insSQL2 = insSQL2 & "'" & isManuallyClosed83 & "',"
        insSQL2 = insSQL2 & "'" & other184 & "',"
        insSQL2 = insSQL2 & "'" & other285 & "');"
                
        accessDB.Execute insSQL2
 Else
  
      ' Get the value of the IORSalesOrderLineRet.SalesOrderLineGroupRet element.
      If (Not orSalesOrderLineRet72.SalesOrderLineGroupRet Is Nothing) Then
        ' Get the value of the ISalesOrderLineGroupRet.TxnLineID element.
        Dim txnLineID91 As String
        txnLineID91 = orSalesOrderLineRet72.SalesOrderLineGroupRet.TxnLineID.GetValue
  
        ' Get the value of the ISalesOrderLineGroupRet.ItemGroupRef element.
        ' Get the FullName value.
        If (Not orSalesOrderLineRet72.SalesOrderLineGroupRet.ItemGroupRef Is Nothing) Then
          'If (Not orSalesOrderLineRet72.SalesOrderLineGroupRet.ItemGroupRef.FullName Is Nothing) Then
            Dim fullName92 As String
            fullName92 = Replace(orSalesOrderLineRet72.SalesOrderLineGroupRet.ItemGroupRef.FullName.GetValue, "'", "''")
            Else
            fullName92 = ""
          'End If
        End If
        
  
        ' Get the value of the ISalesOrderLineGroupRet.Desc element.
        If (Not orSalesOrderLineRet72.SalesOrderLineGroupRet.Desc Is Nothing) Then
          Dim desc93 As String
          desc93 = Replace(orSalesOrderLineRet72.SalesOrderLineGroupRet.Desc.GetValue, "'", "''")
          Else
          desc93 = ""
        End If
  
        ' Get the value of the ISalesOrderLineGroupRet.Quantity element.
        If (Not orSalesOrderLineRet72.SalesOrderLineGroupRet.Quantity Is Nothing) Then
          Dim quantity94 As Double
          quantity94 = orSalesOrderLineRet72.SalesOrderLineGroupRet.Quantity.GetValue
          Else
          quantity94 = 0
        End If
        ' Get the value of the ISalesOrderLineGroupRet.UnitOfMeasure element.
        If (Not orSalesOrderLineRet72.SalesOrderLineGroupRet.UnitOfMeasure Is Nothing) Then
          Dim unitOfMeasure94 As String
          unitOfMeasure94 = orSalesOrderLineRet72.SalesOrderLineGroupRet.UnitOfMeasure.GetValue
          Else
          unitOfMeasure94 = ""
        End If
  
        ' Get the value of the ISalesOrderLineGroupRet.TotalAmount element.
        Dim totalAmount96 As Double
        totalAmount96 = orSalesOrderLineRet72.SalesOrderLineGroupRet.TotalAmount.GetValue
        
        Dim uSQL3 As String
        uSQL3 = "UPDATE SalesOrderLine "
        uSQL3 = uSQL3 & "SET TxnID='" & txnid1
        uSQL3 = uSQL3 & "', GroupID='" & txnLineID91
        uSQL3 = uSQL3 & "', EditSequence='" & editSequence4
        uSQL3 = uSQL3 & "', ItemRefFullName='" & fullName92
        uSQL3 = uSQL3 & "', Description='" & desc93
        uSQL3 = uSQL3 & "', Quantity='" & quantity94
        uSQL3 = uSQL3 & "', UnitOfMeasure='" & unitOfMeasure94
        uSQL3 = uSQL3 & "', Amount='" & totalAmount96 & "' "
                
        uSQL3 = uSQL3 & "WHERE TxnLineID='" & txnLineID91 & "';"
      
        accessDB.Execute uSQL3
        
        Dim insSQL3 As String
  
        insSQL3 = "INSERT INTO SalesOrderLine" _
        & "(TxnLineID, TxnID, GroupID, EditSequence, ItemRefFullName,Description,Quantity,UnitofMeasure,Amount)" _
        & "VALUES" _
        & "("
        
        insSQL3 = insSQL3 & "'" & txnLineID91 & "',"
        insSQL3 = insSQL3 & "'" & txnid1 & "',"
        insSQL3 = insSQL3 & "'" & txnLineID91 & "',"
        insSQL3 = insSQL3 & "'" & editSequence4 & "',"
        insSQL3 = insSQL3 & "'" & fullName92 & "',"
        insSQL3 = insSQL3 & "'" & desc93 & "',"
        insSQL3 = insSQL3 & "'" & quantity94 & "',"
        insSQL3 = insSQL3 & "'" & unitOfMeasure94 & "',"
        insSQL3 = insSQL3 & "'" & totalAmount96 & "');"
        
        accessDB.Execute insSQL3
        
  
        ' Get the value of the ISalesOrderLineGroupRet.SalesOrderLineRetList element.
        If (Not orSalesOrderLineRet72.SalesOrderLineGroupRet.SalesOrderLineRetList Is Nothing) Then
          Dim p As Integer
          For p = 0 To orSalesOrderLineRet72.SalesOrderLineGroupRet.SalesOrderLineRetList.Count - 1
            Dim salesOrderLineRet97 As ISalesOrderLineRet
            Set salesOrderLineRet97 = orSalesOrderLineRet72.SalesOrderLineGroupRet.SalesOrderLineRetList.GetAt(p)
            ' Get the value of the ISalesOrderLineRet.TxnLineID element.
            Dim txnLineID98 As String
            txnLineID98 = salesOrderLineRet97.TxnLineID.GetValue
  
            ' Get the value of the ISalesOrderLineRet.ItemRef element.
            If (Not salesOrderLineRet97.ItemRef Is Nothing) Then
              'If (Not salesOrderLineRet97.ItemRef.FullName Is Nothing) Then
              ' Get the FullName value.
                Dim fullName99 As String
                fullName99 = Replace(salesOrderLineRet97.ItemRef.FullName.GetValue, "'", "''")
                Else
                fullName99 = ""
              'End If
            End If
  
            ' Get the value of the ISalesOrderLineRet.Desc element.
            If (Not salesOrderLineRet97.Desc Is Nothing) Then
              Dim desc100 As String
              desc100 = Replace(salesOrderLineRet97.Desc.GetValue, "'", "''")
              Else
              desc100 = ""
            End If
  
            ' Get the value of the ISalesOrderLineRet.Quantity element.
            If (Not salesOrderLineRet97.Quantity Is Nothing) Then
              Dim quantity101 As Double
              quantity101 = salesOrderLineRet97.Quantity.GetValue
              Else
              quantity101 = 0
            End If
            ' Get the value of the ISalesOrderLineRet.UnitOfMeasure element.
            If (Not salesOrderLineRet97.UnitOfMeasure Is Nothing) Then
              Dim unitOfMeasure101 As String
              unitOfMeasure101 = salesOrderLineRet97.UnitOfMeasure.GetValue
              Else
              unitOfMeasure101 = ""
            End If
  
            ' Get the value of the ISalesOrderLineRet.ORRate element.
            If (Not salesOrderLineRet97.ORRate Is Nothing) Then
              ' Get the value of the IORRate.Rate element.
              If (Not salesOrderLineRet97.ORRate.Rate Is Nothing) Then
                Dim rate102 As Double
                rate102 = salesOrderLineRet97.ORRate.Rate.GetValue
              End If
  
              ' Get the value of the IORRate.RatePercent element.
              If (Not salesOrderLineRet97.ORRate.RatePercent Is Nothing) Then
                Dim ratePercent103 As Double
                ratePercent103 = salesOrderLineRet97.ORRate.RatePercent.GetValue
              End If
  
            End If
  
            ' Get the value of the ISalesOrderLineRet.ClassRef element.
            If (Not salesOrderLineRet97.ClassRef Is Nothing) Then
              ' Get the FullName value.
              'If (Not salesOrderLineRet97.ClassRef.FullName Is Nothing) Then
                Dim fullName104 As String
                fullName104 = Replace(salesOrderLineRet97.ClassRef.FullName.GetValue, "'", "''")
                Else
                fullName104 = ""
              'End If
            End If
  
            ' Get the value of the ISalesOrderLineRet.Amount element.
            If (Not salesOrderLineRet97.Amount Is Nothing) Then
              Dim amount105 As Double
              amount105 = salesOrderLineRet97.Amount.GetValue
            End If
  
            ' Get the value of the ISalesOrderLineRet.SalesTaxCodeRef element.
            If (Not salesOrderLineRet97.SalesTaxCodeRef Is Nothing) Then
              ' Get the FullName value.
              If (Not salesOrderLineRet97.SalesTaxCodeRef.FullName Is Nothing) Then
                Dim fullName106 As String
                fullName106 = Replace(salesOrderLineRet97.SalesTaxCodeRef.FullName.GetValue, "'", "''")
              End If
            End If
  
            ' Get the value of the ISalesOrderLineRet.Invoiced element.
            If (Not salesOrderLineRet97.Invoiced Is Nothing) Then
              Dim invoiced107 As Double
              invoiced107 = salesOrderLineRet97.Invoiced.GetValue
            End If
  
            ' Get the value of the ISalesOrderLineRet.IsManuallyClosed element.
            If (Not salesOrderLineRet97.IsManuallyClosed Is Nothing) Then
              Dim isManuallyClosed108 As Boolean
              isManuallyClosed108 = salesOrderLineRet97.IsManuallyClosed.GetValue
            End If
  
            ' Get the value of the ISalesOrderLineRet.Other1 element.
            If (Not salesOrderLineRet97.Other1 Is Nothing) Then
              Dim other1109 As String
              other1109 = Replace(salesOrderLineRet97.Other1.GetValue, "'", "''")
              Else
              other1109 = ""
              
            End If
  
            ' Get the value of the ISalesOrderLineRet.Other2 element.
            If (Not salesOrderLineRet97.Other2 Is Nothing) Then
              Dim other2110 As String
              other2110 = Replace(salesOrderLineRet97.Other2.GetValue, "'", "''")
              Else
              other2110 = ""
            End If
            
             Dim uSQL4 As String
             uSQL4 = "UPDATE SalesOrderLine "
             uSQL4 = uSQL4 & "SET TxnID='" & txnid1
             uSQL4 = uSQL4 & "', GroupID='" & txnLineID91
             uSQL4 = uSQL4 & "', EditSequence='" & editSequence4
             uSQL4 = uSQL4 & "', ItemRefFullName='" & fullName99
             uSQL4 = uSQL4 & "', Description='" & desc100
             uSQL4 = uSQL4 & "', Quantity='" & quantity101
             uSQL4 = uSQL4 & "', UnitOfMeasure='" & unitOfMeasure101
             uSQL4 = uSQL4 & "', Rate='" & rate102
             uSQL4 = uSQL4 & "', RatePercent='" & ratePercent103
             uSQL4 = uSQL4 & "', ClassRefFullName='" & fullName104
             uSQL4 = uSQL4 & "', Amount='" & amount105
             uSQL4 = uSQL4 & "', SalesTaxCodeRefFullName='" & fullName106
             uSQL4 = uSQL4 & "', Invoiced='" & invoiced107
             uSQL4 = uSQL4 & "', IsManuallyClosed='" & isManuallyClosed108
             uSQL4 = uSQL4 & "', other1='" & other1109
             uSQL4 = uSQL4 & "', other2='" & other2110 & "' "
        
             uSQL4 = uSQL4 & "WHERE TxnLineID='" & txnLineID98 & "';"
             
             accessDB.Execute uSQL4
            
            Dim insSQL4 As String
  
            insSQL4 = "INSERT INTO SalesOrderLine" _
            & "(TxnLineID, TxnID, GroupID, EditSequence, ItemRefFullName,Description,Quantity,UnitOfMeasure,Rate,RatePercent,ClassRefFullName,Amount,SalesTaxCodeRefFullName,Invoiced,IsManuallyClosed,Other1,Other2)" _
            & "VALUES" _
            & "("
            
            insSQL4 = insSQL4 & "'" & txnLineID98 & "',"
            insSQL4 = insSQL4 & "'" & txnid1 & "',"
            insSQL4 = insSQL4 & "'" & txnLineID91 & "',"
            insSQL4 = insSQL4 & "'" & editSequence4 & "',"
            insSQL4 = insSQL4 & "'" & fullName99 & "',"
            insSQL4 = insSQL4 & "'" & desc100 & "',"
            insSQL4 = insSQL4 & "'" & quantity101 & "',"
            insSQL4 = insSQL4 & "'" & unitOfMeasure101 & "',"
            insSQL4 = insSQL4 & "'" & rate102 & "',"
            insSQL4 = insSQL4 & "'" & ratePercent103 & "',"
            insSQL4 = insSQL4 & "'" & fullName104 & "',"
            insSQL4 = insSQL4 & "'" & amount105 & "',"
            insSQL4 = insSQL4 & "'" & fullName106 & "',"
            insSQL4 = insSQL4 & "'" & invoiced107 & "',"
            insSQL4 = insSQL4 & "'" & isManuallyClosed108 & "',"
            insSQL4 = insSQL4 & "'" & other1109 & "',"
            insSQL4 = insSQL4 & "'" & other2110 & "');"
                    
            accessDB.Execute insSQL4
              
          Next p
  
        End If
        End If
        End If
  
    Next m
 
  End If
  
 
End Sub
  

Open in new window

Commented:
Thanks Jadaiber

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.