How to add an invoice line item to an existing Quickbooks invoice using QBFC and VBA

Question

Does anyone have any sample vba code using QuickBooks QBFC to add an invoice line item to an existing QuickBooks invoice? I have no problem querying the invoice that I wish to edit, and I am also able to create a new invoice and its line items into one request/response transaction. However, I cannot seem to get the code right to simply add an invoice line item to one of my existing invoices. Here is some of my code. You will see the QBFC_GetInvoice function...this is where I am retrieving the invoice I wish to add the line items on;

Dim SessionManager As New QBSessionManager
SessionManager.OpenConnection "xyz", "Test" 
SessionManager.BeginSession "", omDontCare     

Dim objMsgSetRequest As IMsgSetRequest
Set objMsgSetRequest = GetLatestMsgSetRequest(SessionManager)

QBFC_GetInvoice (TxnID)

Dim myinvoice As IInvoiceRet
Set myinvoice = objSavedInvoiceRet
Dim sEditSeq As String
sEditSeq = myinvoice.EditSequence.GetValue
Dim objInvoicemod As IInvoiceMod
objInvoicemod.ORInvoiceLineModList.Append.invoiceLineMod.ItemRef.ListID.SetValue TxnID     
Dim invoiceLineAdd As IInvoiceLineAdd
Set invoiceLineAdd = myinvoice.orInvoiceLineRetList.Append.InvoiceLineRet  

invoiceLineAdd.TxnLineID.SetValue "-1"
invoiceLineAdd.ItemRef.ListID.SetValue sLineItemid
invoiceLineAdd.Quantity.SetValue iQty

Dim objInvoiceMsgSetResponse As IMsgSetResponse

score 1 · Answer 1 · edited Aug 07 '16 at 14:47

(This is my first post - please forgive/straighten me out on any protocol faux pas)

Some issues that I see (in addition to those already pointed out) are:

You cannot (or at least I could not) set an xLineAdd object (e.g. IInvoiceLineAdd, IEstimateLineAdd) to an xLineRet object (e.g. IInvoiceLineRet, IEstimateLineRet) - when you look at the underlying xml it makes sense that you would not be able to.
the xLineAdd object (e.g. IInvoiceLineAdd, IEstimateLineAdd) does not have the TxnLineID property. The xLineMod (e.g. IInvoiceLineMod, IEstimateLineMod) does support the TxnLineID property.

Another issue to be aware of when adding (or modifying) lines to an existing transaction (e.g. Invoice, Estimate) is that you need to specify by TxnLineID all the existing lines that you want to keep after the line add/mod process. Otherwise, all lines that are not explicitly specified in this manner are deleted from the transaction during the modification process.

From the QBSDK Programmers Guide:

"In a Mod request, you can add a new line by supplying a TxnLineID value of -1. Remember: if you modify any line item, you need to specify all of the other line items or else they will be dropped as a result of the Mod. However, you need not fully specify the line item with all its ItemRef, Quantity, and other elements. You need only specify its TxnLineID as follows: <PurchaseOrderLineMod> <TxnLineID>101</TxnLineID> </PurchaseOrderLineMod>

This will retain the line item exactly as it was prior to the Mod."

Here is some code I have for adding a line to an existing estimate (should be the same basic concept for adding invoices).

Sub AddLine()
  Dim SessMgr As New QBSessionManager
  Dim MsgReqObj As IMsgSetRequest
  Set MsgReqObj = SessMgr.CreateMsgSetRequest("US", 13, 0)

  Dim EstimateModObj As IEstimateMod
  Dim ExistingEstimateObj As IEstimateRet

  Set EstimateModObj = MsgReqObj.AppendEstimateModRq
  Set ExistingEstimateObj = GetEstimateByRef("12")  'function that takes a Reference Number and returns an IEstimateRet object corresponding existing estimate (not included here).

  EstimateModObj.TxnID.SetValue ExistingEstimateObj.TxnID.GetValue
  EstimateModObj.EditSequence.SetValue  ExistingEstimateObj.EditSequence.GetValue

'loop thru line items on existing transaction and add TxnLineIDs to the Mod Object (so they don't get deleted).
  For i = 0 To ExistingEstimateObj.OREstimateLineRetList.Count - 1
      EstimateModObj.OREstimateLineModList.Append.EstimateLineMod.TxnLineID.SetValue _
        ExistingEstimateObj.OREstimateLineRetList.GetAt(i).EstimateLineRet.TxnLineID.GetValue
  Next

  Dim EstLineAdd As IEstimateLineMod
  Set EstLineAdd = EstimateModObj.OREstimateLineModList.Append.EstimateLineMod

  EstLineAdd.TxnLineID.SetValue "-1"
  EstLineAdd.ItemRef.FullName.SetValue "Drywall - cost"
  EstLineAdd.ORRate.Rate.SetValue "150"

  SessMgr.OpenConnection "", "GetVendorTest"
  SessMgr.BeginSession "", omDontCare

  Set MsgRespObj = SessMgr.DoRequests(MsgReqObj)

  SessMgr.EndSession
  SessMgr.CloseConnection
End Sub

score 0 · Answer 2 · answered Feb 09 '15 at 15:09

When you modify anything in QuickBooks you must provide the TxnID of the object you are modifying as well as a the EditSequence. It looks like you are getting the EditSequence, but never setting it in your mod request. It also looks like you are setting the TxnID to the line, and not the transaction as a whole.

Dim objInvoicemod As IInvoiceMod

// Set the TxnID for the invoice
objInvoicemod.TxnID.SetValue TxnID  

// Set the EditSequence for the invoice
objInvoicemod.EditSequence.SetValue sEditSeq

Dim invoiceLineAdd As IInvoiceLineAdd
Set invoiceLineAdd = myinvoice.orInvoiceLineRetList.Append.InvoiceLineRet  

invoiceLineAdd.TxnLineID.SetValue "-1"
invoiceLineAdd.ItemRef.ListID.SetValue sLineItemid
invoiceLineAdd.Quantity.SetValue iQty

Dim objInvoiceMsgSetResponse As IMsgSetResponse

How to add an invoice line item to an existing Quickbooks invoice using QBFC and VBA

2 Answers2