Changing the Vat rates in Sage Line 50

After we changed the vat for the second time in a year I amended this program to allow the users to change the vat code across customers and products. This version was written for Sage 2009. for the example I have hard coded the tax code but you would use a variable 🙂

Function ChangeVat()
On Error GoTo Error_Handler

DoCmd.Hourglass True
'check defaults
Application.Echo True, "Updating Program Data"

Dim oSDO As SageDataObject150.SDOEngine
Dim oWS As SageDataObject150.Workspace
Dim strDataPath As String
Dim oSalesRecord As SageDataObject150.SalesRecord
Dim oSalesDeliveryRecord As SageDataObject150.SalesDeliveryRecord
Dim bFlag As Boolean
Dim i As Integer, tmpInt As Long, tmpProg As Long, tmpCount, tmpLetter As String, tmpType As Double

Application.Echo True, "Checking for Sage Preferences to Add"

If ChkPrefs = False Then
    GoTo Sage_ExitImport
End If

' Create the SDOEngine Object
Set oSDO = New SageDataObject150.SDOEngine
' Create the Workspace
Set oWS = oSDO.Workspaces.Add("Example")
' Select company the select company method

' Connect to Data Files
oWS.Connect "Line50 Directory", "Login Name", "Login Password", "Example"

' Create Instance of Sales Record Object
Set oSalesRecord = oWS.CreateObject("SalesRecord")
' goto the first sales ledger record

oSalesRecord.MoveFirst

Do
        ' Edit the Record
        If oSalesRecord.Edit Then
            ' Change the Account Name
            oSalesRecord.Fields.Item("DEf_TAX_CODE").Value = 2
        ' Update the Record
            If oSalesRecord.Update Then
                ' The Update was Successful
                Application.Echo True, "Account " & oSalesRecord.Fields.Item("ACCOUNT_REF").Value &" was edited successfully."
            Else
                ' The Update was Unsuccessful
                MsgBox "The account could not be edited."
            End If
        End If

 Loop Until (Not oSalesRecord.MoveNext)
'--------------------
'Export the Products
'------------------
Dim oStockRecord As SageDataObject150.StockRecord
Dim oPriceRecord As SageDataObject150.PriceRecord
Dim oControlData As SageDataObject150.ControlData

' Create Instance of StockRecord Object
Set oStockRecord = oWS.CreateObject("StockRecord")
Set oPriceRecord = oWS.CreateObject("PriceRecord")
tmpCount = oStockRecord.Count
tmpProg = 1
oStockRecord.MoveFirst
Do

        ' Edit the Record
        If oStockRecord.Edit Then
            oStockRecord.Fields.Item("TAX_CODE").Value = 2
        ' Update the Record
            If oStockRecord.Update Then
                ' The Update was Successful
                Application.Echo True, "Account " & oStockRecord.Fields.Item("STOCK_CODE").Value & " was edited successfully."
            Else
                ' The Update was Unsuccessful
                MsgBox "The account could not be edited."
            End If
        End If

Loop Until (Not oStockRecord.MoveNext)

'Close connections
Set oStockRecord = Nothing
Set oControlData = Nothing

Sage_ExitImport:

' Disconnect and Destroy the Objects
oWS.Disconnect
Set oSalesRecord = Nothing
Set oSDO = Nothing
Set oWS = Nothing
DoCmd.Hourglass False

Exit Function

' Error Handling Code
Error_Handler:
Call SageError(oSDO.LastError.Code, oSDO.LastError.text, Err.Number, Err.Description, "Sage Import")

DoCmd.Hourglass False
Resume Sage_ExitImport

End Function