Example Code of Update QuickBooks from MS Access

Requirements of Update QB from MS Access

Now take below as an example:

     All I am trying to do is update the account number in QB from a table I built in Access. In Access, I linked the QB Account table and did a join to my new table that has the Account.FullName field in it. Below are my queries tried in SQL:

UPDATE [QB Account Master] RIGHT JOIN Account ON [QB Account Master].FullName = Account.FullName SET Account.AccountNumber = [QB Account Master]![GP Account Number] WHERE (([Account]![FullName]=[QB Account Master]![FullName]));

or

UPDATE Account, [Copy of QB Account Master] SET Account.AccountNumber = [Copy of QB Account Master]![AccountNumber]
WHERE (((Account.AccountNumber) Is Null) AND ((Account.ListID)=[Copy of QB Account Master]![ListID]));

But always get a QuickBooks error message: cannot merge list elements

Possible Solutions

Try changing the SET Account.AccountNumber to SET AccountNumber and drop the Access table:

UPDATE Account SET AccountNumber = [Copy of QB Account Master]![AccountNumber]
WHERE (((Account.AccountNumber) Is Null) AND ((Account.ListID)=[Copy of QB Account Master]![ListID]));

And place the statement within a loop of the MS Access Copy of QB Account Master table rows. This means you need to write some VBA code, or you can execute DoCmd.RunSQL commands, see: Inserting invoice lines built from external data using MS Access or create a sub-procedure.

Here is an Invoice example:

----------------------------------------------------------------------------------

Private Sub Send_Orders_QB_INS(iLogNum As Integer)

On Error GoTo ERR_SEND

Dim sSQL_OrderHeaders As String
Dim sSQL_OrderLabor As String
Dim sSQL_OrderMaterial As String
Dim sSQL As String

Dim rsOrder_Headers As Recordset
Dim rsOrder_Labor As Recordset
Dim rsOrder_Material As Recordset

Dim sLocalCustName As String
Dim sQBCustomerID As String
Dim sQBTermsID As Variant
Dim sQBItemTaxID As Variant
Dim sQBCustTaxID As Variant
Dim sQBCustAddr1 As Variant
Dim sQBCustAddr2 As Variant
Dim sQBCustCity As Variant
Dim sQBCustState As Variant
Dim sQBCustZip As Variant
Dim iLaborRowCount As Integer
Dim iMaterialsRowCount As Integer

Dim sSQL_INVOICE_LINE_INSERT As String
Dim sSQL_INVOICE_INSERT As String

Dim sCustomerRefListID As String
Dim sCustomerRefFullName As String
Dim sClassRefListID As String
Dim sClassRefFullName As String
Dim sARAccountRefListID As String
Dim sARAccountRefFullName As String
Dim sTxnDate As String
Dim sRefNumber As String
Dim sBillAddressAddr1 As String
Dim sBillAddressAddr2 As String
Dim sBillAddressCity As String
Dim sBillAddressState As String
Dim sBillAddressPostalCode As String
Dim sTermsRefListID As String
Dim sTermsRefFullName As String
Dim sDueDate As String
Dim sShipDate As String
Dim sItemSalesTaxRefListID As String
Dim sItemSalesTaxRefFullName As String
Dim sCustomerMsgRefListID As String
Dim sCustomerMsgRefFullName As String
Dim sCustomerSalesTaxCodeRefListID As String
Dim sCustomerSalesTax As String
Dim sCodeRefFullName As String
Dim sInvoiceLineItemRefListID As String
Dim sInvoiceLineDesc As String
Dim dInvoiceLineQuantity As Double
Dim cInvoiceLineAmount As Currency
Dim cInvoiceLineRate As Currency
Dim sInvoiceLineSalesTaxCodeRefListID As String
Dim sInvoiceLineSalesTaxCodeRefFullName As String

Dim iServiceOrderMatchCnt As Integer
Dim bDupError As Boolean

DoCmd.SetWarnings False

' ***** ADDED 05/31/2004 *******************
' only allow orders that have an invoice number
sSQL_OrderHeaders = "SELECT dtl.* FROM dtlServiceOrder AS dtl, tblQB_E xports_Detail_Log AS log " + _
"WHERE dtl.ServiceOrderNumber = log.ServiceOrderNumber AND dtl.ServiceOrderStatus = 'Complete' AND log.IsSendable = True AND log.ExportLogNum = " + CStr(iLogNum)

Set rsOrder_Headers = CurrentDb.OpenRecordset(sSQL_OrderHeaders)

Do While Not rsOrder_Headers.EOF
' add labor lines to QB Invoice Line table
iLaborRowCount = 0
' ***** ADDED 05/31/2004 *******************
' only allow lines that have labor hours included. This is to prevent the import of blank lines.
sSQL_OrderLabor = "SELECT * FROM dtlServiceOrderLbr WHERE ServiceOrderNumber = '" + rsOrder_Headers!ServiceOrderNumber + "' AND (LbrHoursRG > 0 OR LbrHoursOT > 0 OR LbrHoursDT > 0)"
Set rsOrder_Labor = CurrentDb.OpenRecordset(sSQL_OrderLabor)
Do While Not rsOrder_Labor.EOF
sInvoiceLineItemRefListID = id_INVOICE_LINE_ITEM_REF_Labor
sInvoiceLineDesc = "Labor"
dInvoiceLineQuantity = rsOrder_Labor!LbrHoursRG + rsOrder_Labor!LbrHoursOT + rsOrder_Labor!LbrHoursDT
cInvoiceLineAmount = rsOrder_Labor!LbrTotalCost
If cInvoiceLineAmount = 0 And dInvoiceLineQuantity = 0 Then
cInvoiceLineRate = 0
Else
cInvoiceLineRate = cInvoiceLineAmount / dInvoiceLineQuantity
End If
sInvoiceLineSalesTaxCodeRefListID = id_INVOICE_LINE_SALES_TAX_CODE_Non
sInvoiceLineSalesTaxCodeRefFullName = "Non"
sFQSaveToCache = 1

sSQL_INVOICE_LINE_INSERT = "INSERT INTO QB_INVOICE_LINE " + _
"(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineQuantity," + _
"InvoiceLineAmount,InvoiceLineRate,InvoiceLineSalesTaxCodeRefListID," + _
"InvoiceLineSalesTaxCodeRefFullName,FQSaveToCache) " + _
"VALUES " + _
"('" + sInvoiceLineItemRefListID + "','" + sInvoiceLineDesc + "'," + CStr(dInvoiceLineQuantity) + "," + _
&nb sp; "" + CStr(cInvoiceLineAmount) + "," + CStr(cInvoiceLineRate) + ",'" + sInvoiceLineSalesTaxCodeRefListID + "'," + _
"'" + sInvoiceLineSalesTaxCodeRefFullName + "'," + CStr(1) + ")"

CurrentDb.Execute sSQL_INVOICE_LINE_INSERT

iLaborRowCount = iLaborRowCount + 1

rsOrder_Labor.MoveNext
Loop

' add material lines to QB Invoice Line table
iMaterialsRowCount = 0
' ***** ADDED 05/31/2004 *******************
' only allow lines that have material quantities included. This is to prevent the import of blank lines.
sSQL_OrderMaterial = "SELECT * FROM dtlServiceOrderMtl WHERE ServiceOrderNumber = '" + rsOrder_Headers!ServiceOrderNumber + "' AND MtlQuantity > 0"
Set rsOrder_Material = CurrentDb.OpenRecordset(sSQL_OrderMaterial)
Do While Not rsOrder_Material.EOF
sInvoiceLineItemRefListID = id_INVOICE_LINE_ITEM_REF_Misc
sInvoiceLineDesc = rsOrder_Material!MtlItemDescription
dInvoiceLineQuantity = rsOrder_Material!MtlQuantity
cInvoiceLineAmount = rsOrder_Material!MtlPrice
cInvoiceLineRate = rsOrder_Material!MtlExtend
If (rsOrder_Material!MtlTaxable) = True Or (rsOrder_Material!MtlTaxable2 = True) Then
sInvoiceLineSalesTaxCodeRefListID = id_INVOICE_LINE_SALES_TAX_CODE_Tax
sInvoiceLineSalesTaxCodeRefFullName = "Tax"
Else
sInvoiceLineSalesTaxCodeRefListID = id_INVOICE_LINE_SALES_TAX_CODE_Non
sInvoiceLineSalesTaxCodeRefFullName = "Non"
End If

sSQL_INVOICE_LINE_INSERT = "INSERT INTO QB_INVOICE_LINE " + _
"(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineQuantity," + _
"InvoiceLineAmount,InvoiceLineRate,InvoiceLineSalesTaxCodeRefListID," + _
"InvoiceLineSalesTaxCodeRefFullName,FQSaveToCache) " + _
"VALUES " + _
"('" + sInvoiceLineItemRefListID + "','" + sInvoiceLineDesc + "'," + CStr(dInvoiceLineQuantity) + "," + _
"" + CStr(cInvoiceLineAmount) + "," + CStr(cInvoiceLineRate) + ",'" + sInvoiceLineSalesTaxCodeRefListID + "'," + _
"'" + sInvoiceLineSalesTaxCodeRefFullName + "'," + CStr(1) + ")"

CurrentDb.Execute sSQL_INVOICE_LINE_INSERT

iMaterialsRowCount = iMaterialsRowCount + 1

rsOrder_Material.MoveNext
Loop

' add a record to QB Invoice table
sLocalCustName = DLookup("[CustSort]", "mstCustomers", "[CustNumber] = '" + rsOrder_Headers!CustNumber + "'")
sQBCustomerID = GetCustomerID(rsOrder_Headers!CustNumber, sLocalCustName)
sCustomerRefListID = sQBCustomerID
sCustomerRefFullName = sLocalCustName
sARAccountRefListID = id_ARA_ACCOUNT_REF_Accounts_Receivable
sARAccountRefFullName = "Accounts Receivable"
sTxnDate = CStr(rsOrder_Headers!ServiceOrderDate)
'sRefNumber = GetNextQBRefNum()
sRefNumber = rsOrder_Headers!ServiceOrderInvNum
sClassRefListID = id_CLASS_REF_landscaping
sClassRefListID = id_CLASS_REF_landscaping
sClassRefFullName = "Commerical Landscaping"
sQBCustAddr1 = DLookup("[BillAddressAddr1]", "QB_CUSTOMER", "[ListID] = '" + sCustomerRefListID + "'")
If IsNull(sQBCustAddr1) Then
sBillAddressAddr1 = ""
Else
sBillAddressAddr1 = sQBCustAddr1
End If
sQBCustAddr2 = DLookup("[BillAddressAddr2]", "QB_CUSTOMER", "[ListID] = '" + sCustomerRefListID + "'")
If IsNull(sQBCustAddr2) Then
sBillAddressAddr2 = ""
Else
sBillAddressAddr2 = sQBCustAddr2
End If
sQBCustCity = DLookup("[BillAddressCity]", "QB_CUSTOMER", "[ListID] = '" + sCustomerRefListID + "'")
If IsNull(sQBCustCity) Then
sBillAddressCity = ""
Else
sBillAddressCity = sQBCustCity
End If
sQBCustState = DLookup("[BillAddressState]", "QB_CUSTOMER", "[ListID] = '" + sCustomerRefListID + "'")
If IsNull(sQBCustState) Then
sBillAddressState = ""
Else
sBillAddressState = sQBCustState
End If
sQBCustZip = DLookup("[BillAddressPostalCode]", "QB_CUSTOMER", "[ListID] = '" + sCustomerRefListID + "'")
If IsNull(sQBCustZip) Then
sBillAddressPostalCode = ""
Else
sBillAddressPostalCode = sQBCustZip
End If
sQBTermsID = DLookup("[TermsRefListID]", "QB_CUSTOMER", "[ListID] = '" + sQBCustomerID + "'")
If IsNull(sQBTermsID) Or (sQBTermsID = "") Then
sTermsRefListID = id_TERMS_REF_Due_on_receipt
sTermsRefFullName = "Due on receipt"
Else
sTermsRefListID = sQBTermsID
sTermsRefFullName = DLookup("[TermsRefFullName]", "QB_CUSTOMER", "[ListID] = '" + sQBCustomerID + "'")
End If
sDueDate = CStr(rsOrder_Headers!ServiceOrderDate)
sShipDate = CStr(rsOrder_Headers!ServiceOrderDate)
sQBItemTaxID = DLookup("[ItemSalesTaxRefListID]", "QB_CUSTOMER", "[ListID] = '" + sQBCustomerID + "'")
If IsNull(sQBItemTaxID) Or (sQBItemTaxID = "") Then
sItemSalesTaxRefListID = id_ITEM_SALES_TAX_REF_6
sItemSalesTaxRefFullName = "6% Sales Tax"
Else
sItemSalesTaxRefListID = sQBItemTaxID
sItemSalesTaxRefFullName = DLookup("[ItemSalesTaxRefFullName]", "QB_CUSTOMER", "[ListID] = '" + sQBCustomerID + "'")
End If
sCustomerMsgRefListID = id_CUSTOMER_MSG_REF_Thank_you
sCustomerMsgRefFullName = " Thank you for your business."
sQBCustTaxID = DLookup("[SalesTaxCodeRefListID]", "QB_CUSTOMER", "[ListID] = '" + sQBCustomerID + "'")
If IsNull(sQBItemTaxID) Or (sQBItemTaxID = "") Then
sCustomerSalesTaxCodeRefListID = id_INVOICE_LINE_SALES_TAX_CODE_Tax
sCustomerSalesTaxCodeRefFullName = "Tax"
Else
sCustomerSalesTaxCodeRefListID = sQBCustTaxID
sCustomerSalesTaxCodeRefFullName = DLookup("[SalesTaxCodeRefFullName]", "QB_CUSTOMER", "[ListID] = '" + sQBCustomerID + "'")
End If

sSQL_INVOICE_INSERT = "INSERT INTO QB_INVOICE " + _
& nbsp; "(CustomerRefListID,CustomerRefFullName,ClassRefListID,ClassRefFullName," + _
"ARAccountRefListID,ARAccountRefFullName,TxnDate,RefNumber,BillAddressAddr1," + _
"BillAddressAddr2,BillAddressCity,BillAddressState,BillAddressPostalCode," + _
"IsPending,TermsRefListID,TermsRefFullName,DueDate,ShipDate,ItemSalesTaxRefListID," + _
"ItemSalesTaxRefFullName,CustomerMsgRefListID,CustomerMsgRefFullName,IsToBePrinted," + _
"CustomerSalesTaxCodeRefListID) " + _
"VALUES " + _
"('" + sCustomerRefListID + "','" + sCustomerRefFullName + "','" + sClassRefListID + "','" + sClassRefFullName + "'," + _
"'" + sARAccountRefListID + "','" + sARAccountRefFullName + "','" + sTxnDate + "','" + sRefNumber + "','" + sBillAddressAddr1 + "'," + _
"'" + sBillAddressAddr2 + "','" + sBillAddressCity + "','" + sBillAddressState + "','" + sBillAddressPostalCode + "'," + _
"" + CStr(0) + ",'" + sTermsRefListID + "','" + sTermsRefFullName + "','" + sDueDate + "','" + sShipDate + "','" + sItemSalesTaxRefListID + "'," + _
"'" + sItemSalesTaxRefFullName + "','" + sCustomerMsgRefListID + "','" + sCustomerMsgRefFullName + "'," + CStr(0) + "," + _
"'" + sCustomerSalesTaxCodeRefListID + "')"

If (iLaborRowCount + iMaterialsRowCount > 0) Then
CurrentDb.Execute sSQL_INVOICE_INSERT

If Not bDupError Then
iOrdersExported = iOrdersExported + 1
End If
bDupError = False
End If

' ***** ADDED 05/31/2004 *******************
' add to detail log
sSQL = "UPDATE tblQB_Exports_Detail_Log SET OrderSent = 1 WHERE ExportLogNum = " + CStr(iLogNum) + " AND " + _
"ServiceOrderNumber = " + MakeString(rsOrder_Headers!ServiceOrderNumber, True)
CurrentDb.Execute sSQL

' update status
sSQL = "UPDATE dtlServiceOrder " + _
"SET ServiceOrderStatus = 'Exported', " + _
"Export_batch_number = 'QB" + CStr(iLogNum) + "', " + _
"Export_date = '" + CStr(Date) + "' " + _
"WHERE ServiceOrderNumber = '" + rsOrder_Headers!ServiceOrderNumber + "'"
CurrentDb.Execute sSQL

MOVE_NEXT:
rsOrder_Headers.MoveNext
Loop

DoCmd.SetWarnings True

Exit Sub

ERR_SEND:
Select Case Err.Number
Case 3155
MsgBox "Invoice '" + sRefNumber + "' failed." + vbCrLf + "This Order is likely already in QuickBooks." + vbCrLf + "Please write down the order number and research it after this process is complete.", vbExclamation
bDupError = True
Resume Next
Case Else
MsgBox CStr(Err.Number) + "--" + Err.Description
Exit Sub
End Select

End Sub