In our sample application we have loaded a number of samples to work with Sage Line 50. The sample below was used to create invoices. The sources for the header and transaction will vary depending on the source but this sample will get you started. You will need to create a link to the Sage Library – this version was for Sage 22, any new fields added since then would need to be updated.
Function fncCreateInvoices_22(ByVal tmpDate As Date, tmpCompany, tmpLogin, tmpPassword, tmpDefaultNom, tmpCustomerOrder, tmpDefaultDocket)
On Error GoTo Error_Handler
If Not IsDate(tmpDate) Then
MsgBox "Please enter a valid date"
Exit Function
End If
DoCmd.Hourglass True
' Declare Objects
Dim oSDO As SageDataObject220.SDOEngine
Dim oWS As SageDataObject220.Workspace
Dim oInvoicePost As SageDataObject220.InvoicePost
Dim oInvoiceItem As SageDataObject220.InvoiceItem
Dim oSalesRecord As SageDataObject220.SalesRecord
Dim oStockRecord As SageDataObject220.StockRecord
Dim oSalesDeliveryRecord As SageDataObject220.SalesDeliveryRecord
Dim tmpDel(6), bEnd
Dim db As Database
Dim rstSource As Recordset, rstTrans As Recordset, strAccount, tmpPOD
Dim tmpTranCust, tmpUseON As Boolean, tmpTranDD As String, tmpUseCPO As Boolean
Dim tmpSageServiceCodes, tmpUseSageStockNominal, tmpLoadTrackingNo
tmpSageServiceCodes = IIf(GetPref("Sage Service Codes") = "Yes", -1, 0)
tmpUseSageStockNominal = IIf(GetPref("Use Sage Nominal") = "Yes", -1, 0)
tmpLoadTrackingNo = IIf(GetPref("Sage Load Tracking") = "Yes", -1, 0)
Set db = CurrentDb
tmpUseON = tmpDefaultDocket
tmpUseCPO = tmpCustomerOrder
' Declare Variables
Dim strDataPath As String
Dim bFlag As Boolean
Dim iCtr As Integer
'sage initialise
' Create the SDO Engine Object
Set oSDO = New SageDataObject220.SDOEngine
' Create the Workspace
Set oWS = oSDO.Workspaces.Add("Example")
'Check that the selected invoices have a customer record
Set rstSource = db.OpenRecordset("select * from QryCheckInvDates where tDate<=#" & ActDate(tmpDate) & "#", dbOpenDynaset, dbSeeChanges)
Application.Echo True, "Checking Customers"
If rstSource.RecordCount > 0 Then
If MsgBox("Some customer records are missing in sage, print a listing ?", vbYesNo) = vbYes Then
DoCmd.OpenReport "rptMissingCustomers", acViewPreview
GoTo Exit_Function
Else
MsgBox "Add the new customers to proceed"
GoTo Exit_Function
End If
End If
Application.Echo True, "Checking for Invoices to Add"
' create export code
Set rstSource = db.OpenRecordset("select * from qryInvoicestoExport" & IIf(Form_frmInvExport.txtSageCompany = 1, "", "_STG") & " where Value>0 and tDate<=#" & ActDate(tmpDate) & "# ORDER by Ref ASC", dbOpenDynaset, dbSeeChanges)
If rstSource.RecordCount = 0 Then
MsgBox "Nothing to process"
GoTo Exit_Function
Else
rstSource.MoveFirst
End If
Application.Echo True, "Checking for Sage Preferences to Add"
' Connect to Data Files
oWS.Connect tmpCompany, tmpLogin, tmpPassword, "Example"
Application.Echo True, "Connected to Sage"
' Create an instance of InvoicePost & Record object's
Set oSalesRecord = oWS.CreateObject("SalesRecord")
Set oStockRecord = oWS.CreateObject("StockRecord")
'loop the record source
Do While Not rstSource.EOF
Set oInvoicePost = oWS.CreateObject("InvoicePost")
' Set the type of invoice for the next available number
oInvoicePost.Type = sdoLedgerInvoice
If tmpSageServiceCodes = False Then
'get the transactions
Set rstTrans = db.OpenRecordset("Select * from qryTrans Where REF=" & rstSource!REF, dbOpenDynaset, dbSeeChanges)
Else
Set rstTrans = db.OpenRecordset("Select * from qryTransSageService Where REF=" & rstSource!REF, dbOpenDynaset, dbSeeChanges)
End If
If rstTrans.RecordCount = 0 Then
MsgBox "No Transactions for invoice " & rstSource!REF
GoTo loop_routine
End If
'get delivery address
tmpDel(1) = rstTrans!CustD_Companyname
tmpDel(2) = rstTrans!CustD_add1
tmpDel(3) = rstTrans!CustD_add2
tmpDel(4) = rstTrans!CustD_add3
tmpDel(5) = rstTrans!CustD_add4
tmpDel(6) = rstTrans!CustD_add5
Application.Echo True, "Processing Invoice " & rstSource!REF
' Use the invoice number assigned from masc
oInvoicePost.Header("Invoice_Number") = rstSource!REF
' Loop for Number of Items on the Invoice
iCtr = 0
tmpTranCust = ""
Do While Not rstTrans.EOF
Set oInvoiceItem = oInvoicePost.Items.Add()
iCtr = iCtr + 1
If tmpUseSageStockNominal And tmpSageServiceCodes = False Then
' Initialise Index Field with value to search
oStockRecord("Stock_CODE") = CStr(rstTrans!HprodC)
If oStockRecord.Find(False) Then
oInvoiceItem("Stock_Code") = CStr(oStockRecord("Stock_Code"))
oInvoiceItem("Description") = nullCstr(Left(rstTrans!HInvText, 60))
If (tmpLoadTrackingNo) Then
oInvoiceItem("Comment_1") = nullCstr(Left(rstTrans!Htrackno, 60))
Else
oInvoiceItem("Comment_1") = nullCstr(Left(rstTrans!HInvText, 60))
End If
oInvoiceItem("Nominal_Code") = CStr(oStockRecord("Nominal_Code"))
oInvoiceItem("Tax_Code") = CInt(Trim(checkVatLetter(rstTrans!HVatRate)))
Else
oInvoiceItem("Stock_Code") = CStr(rstTrans!HprodC)
oInvoiceItem("Description") = nullCstr(Left(rstTrans!HInvText, 60))
If (tmpLoadTrackingNo) Then
oInvoiceItem("Comment_1") = nullCstr(Left(rstTrans!Htrackno, 60))
Else
oInvoiceItem("Comment_1") = nullCstr(Left(rstTrans!HInvText, 60))
End If
oInvoiceItem("Nominal_Code") = CStr(tmpDefaultNom)
oInvoiceItem("Tax_Code") = CInt(Trim(checkVatLetter(rstTrans!HVatRate)))
End If
Else
If tmpSageServiceCodes = False Then
oInvoiceItem("Stock_Code") = CStr(rstTrans!HprodC)
oInvoiceItem("Description") = nullCstr(Left(rstTrans!HInvText, 60))
If (tmpLoadTrackingNo) Then
oInvoiceItem("Comment_1") = nullCstr(Left(rstTrans!Htrackno, 60))
Else
oInvoiceItem("Comment_1") = nullCstr(Left(rstTrans!HInvText, 60))
End If
Else
'use the nominal we have in masc
oInvoiceItem("Stock_Code") = CStr(rstTrans!Prod)
If rstTrans!CustFreq = "Docket" Then
oInvoiceItem("Description") = nullCstr(Left(rstTrans!AltDesc, 60))
Else
oInvoiceItem("Description") = nullCstr(Left(rstTrans!ProdDetails, 60))
End If
End If
oInvoiceItem("Nominal_Code") = CStr(tmpDefaultNom)
oInvoiceItem("Tax_Code") = CInt(Trim(checkVatLetter(rstTrans!HVatRate)))
End If
' Populate other fields required for Invoice Item
If tmpSageServiceCodes = False Then
oInvoiceItem("Qty_Order") = CDbl(rstTrans!Hqty)
oInvoiceItem("Unit_Price") = CDbl(rstTrans!HPrice)
oInvoiceItem("Net_Amount") = CDbl(rstTrans!HLineValue)
oInvoiceItem("Tax_Amount") = CDbl(rstTrans!HVatVal)
oInvoiceItem("Full_Net_Amount") = CDbl(rstTrans!HVatVal + rstTrans!HLineValue)
Else
oInvoiceItem("Qty_Order") = CDbl(rstTrans!QTY)
oInvoiceItem("Unit_Price") = CDbl(rstTrans!LTotal)
oInvoiceItem("Net_Amount") = CDbl(rstTrans!LTotal)
oInvoiceItem("Tax_Amount") = CDbl(rstTrans!Vat)
oInvoiceItem("Full_Net_Amount") = CDbl(rstTrans!Vat + rstTrans!LTotal)
End If
oInvoiceItem("Tax_Rate") = IIf(IsNull(rstTrans!VT_Rate), CDbl("0.00"), CDbl(rstTrans!VT_Rate)) * 100
'oInvoiceItem("Tax_Rate") = CDbl(rstTrans!VT_Rate)
tmpTranCust = rstTrans!HCustCode
If tmpSageServiceCodes = False Then
If (tmpLoadTrackingNo) = False Then
oInvoiceItem("Comment_2") = CStr("Date:" & Format(rstTrans!HDATE, "dd/mm/yy"))
End If
oInvoiceItem("Unit_Of_Sale") = CStr("")
tmpTranDD = nullCstr(rstTrans!HSuppref)
tmpPOD = nullCstr(rstTrans!HPOD)
End If
rstTrans.MoveNext
Loop ' on trans
' Populate Invoice Header Information
oInvoicePost.Header("Invoice_Date") = CDate(rstSource!TDate)
oInvoicePost.Header("Notes_1") = CStr("")
oInvoicePost.Header("Notes_2") = CStr("")
oInvoicePost.Header("Notes_3") = CStr("")
oInvoicePost.Header("Taken_By") = CStr("")
oInvoicePost.Header("Order_Number") = IIf(tmpUseON, Left(CStr(tmpTranDD), 7), "")
oInvoicePost.Header("Cust_Order_Number") = IIf(tmpUseCPO, Left(CStr(tmpTranDD), 7), "")
If tmpUsePODasSO Then
oInvoicePost.Header("Cust_Order_Number") = IIf(tmpUseCPO, Left(CStr(tmpPOD), 7), "")
End If
oInvoicePost.Header("Payment_Ref") = CStr("")
oInvoicePost.Header("Global_Nom_Code") = CStr("")
oInvoicePost.Header("Global_Details") = CStr("")
oInvoicePost.Header("Invoice_Type_Code") = CByte(sdoProductInvoice)
oInvoicePost.Header("Items_Net") = CDbl(rstSource!InvNet)
oInvoicePost.Header("Items_Tax") = CDbl(rstSource!InvVat)
' Read the first customer
strAccount = CStr(rstSource!ID)
strAccount = strAccount & String(8 - Len(strAccount), 32)
oSalesRecord("Account_Ref") = strAccount
bFlag = oSalesRecord.Find(False)
If bFlag Then
oInvoicePost.Header("Currency") = oSalesRecord("Currency")
oInvoicePost.Header("Foreign_Rate") = 1
oInvoicePost.Header("Euro_Rate") = 1
End If
oInvoicePost.Header("Account_Ref") = CStr(rstSource!ID) 'oSalesRecord("Account_Ref"))
oInvoicePost.Header("Name") = nullCstr(rstSource!CompanyName)
oInvoicePost.Header("Address_1") = nullCstr(rstSource!Add1)
oInvoicePost.Header("Address_2") = nullCstr(rstSource!Add2)
oInvoicePost.Header("Address_3") = nullCstr(rstSource!Add3)
oInvoicePost.Header("Address_4") = nullCstr(rstSource!Town)
oInvoicePost.Header("Address_5") = nullCstr(rstSource!County)
If tmpUseDeliveryAddress Then
If tmpTranCust <> rstSource!ID Then
If Not IsNull(tmpTranCust) Or Len(tmpTranCust) <> 0 Then
Set oSalesDeliveryRecord = oWS.CreateObject("SalesDeliveryRecord")
bEnd = False
oSalesDeliveryRecord.MoveFirst
Do
If oSalesDeliveryRecord("DESCRIPTION") = tmpTranCust Then
bEnd = True
oInvoicePost.Header("DELIVERY_NAME") = CStr(oSalesDeliveryRecord("NAME"))
oInvoicePost.Header("Del_Address_1") = CStr(oSalesDeliveryRecord("Address_1"))
oInvoicePost.Header("Del_Address_2") = CStr(oSalesDeliveryRecord("Address_2"))
oInvoicePost.Header("Del_Address_3") = CStr(oSalesDeliveryRecord("Address_3"))
oInvoicePost.Header("Del_Address_4") = CStr(oSalesDeliveryRecord("Address_4"))
oInvoicePost.Header("Del_Address_5") = CStr(oSalesDeliveryRecord("Address_5"))
End If
Loop Until (bEnd Or Not oSalesDeliveryRecord.MoveNext)
End If
End If
Else
'just use the address on the record
oInvoicePost.Header("DELIVERY_NAME") = nullCstr(tmpDel(1))
oInvoicePost.Header("Del_Address_1") = nullCstr(tmpDel(2))
oInvoicePost.Header("Del_Address_2") = nullCstr(tmpDel(3))
oInvoicePost.Header("Del_Address_3") = nullCstr(tmpDel(4))
oInvoicePost.Header("Del_Address_4") = nullCstr(tmpDel(5))
oInvoicePost.Header("Del_Address_5") = nullCstr(tmpDel(6))
End If
' Update the Invoice
bFlag = oInvoicePost.Update
If bFlag Then
Application.Echo True, "Invoice Created Successfully :" & rstSource!REF
db.Execute "Update tblbillings set ar_PRocessed=-1 where ref=" & rstSource!REF, dbSeeChanges
Else
Application.Echo True, "Invoice Not Created"
End If
loop_routine:
rstSource.MoveNext
Set oInvoiceItem = Nothing
Set oInvoicePost = Nothing
Loop ' on rstsource
Set oSalesRecord = Nothing
Set oSalesDeliveryRecord = Nothing
Exit_Function:
' Disconnect and Destroy Objects
oWS.Disconnect
Set oSDO = Nothing
Set oWS = Nothing
Set db = Nothing
Set rstSource = Nothing
Set rstTrans = Nothing
DoCmd.Hourglass False
Exit Function
' Error Handling Code
Error_Handler:
Call SageError(oSDO.LastError.Code, oSDO.LastError.Text, Err.Number, Err.Description, "Sage Invoice Export")
DoCmd.Hourglass False
Resume Exit_Function
End Function