Here's an example Sub-procedure for MS Access that creates Invoices that a developer sent me. You can modifiy it to do what you need to do:-
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_Exports_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) + "," + _
"" + 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 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 " + _
"(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 + "Most likely this Order is 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 |
