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
Leave a Reply
Want to join the discussion?Feel free to contribute!