Sage 2011 integration for MASC and Wrap

We have updated our programs to support Sage 2011 version 17

Please call us before you upgrade to ensure you have the correct software version

SAP BOM Upload File

SAP BOM Upload File
To load a BOM into SAP we needed to create a XML file with a hierarchy of the BOM.

To start this project in Access you will need to set a reference to ChilkatXML and have a recordset for the BOM items.

Function Make_SAP_XML()

Dim xml As New ChilkatXml
Dim MT_TPC_BOM As ChilkatXml
Dim ProductHeader As ChilkatXml
Dim Systems As ChilkatXml
Dim System As ChilkatXml
Dim SystemItems As ChilkatXml
Dim SystemItem As ChilkatXml
Dim ATOComponents As ChilkatXml
Dim ATOComponent As ChilkatXml

Dim rst As Recordset
Dim tmpSystemLine As Integer
Dim tmpSystem As String, tmpOrder
Dim tmpItemNo As Integer, tmpOrderLine As Integer

xml.Tag = "MT_TPC_BOM"
xml.AddAttribute "xmlns:nr1", ""

Set ProductHeader = xml.NewChild("ProductHeader", "")

'set the header values
ProductHeader.NewChild2 "ConfigSource", "TPC"
ProductHeader.NewChild2 "TPCVersion", "7.3k"
ProductHeader.NewChild2 "TPCInternalDate", Format(Date, "mm/dd/yyyy")
ProductHeader.NewChild2 "TPCEngInfoDate", Format(Date, "mm/dd/yyyy")
ProductHeader.NewChild2 "TPCEngInfoFileVersion", "1.0"
ProductHeader.NewChild2 "CommerciallyComplete", "Y"
ProductHeader.NewChild2 "CurrencyCode", "EUR"

Set Systems = ProductHeader.NewChild("Systems", "")

Set rst = CurrentDb.OpenRecordset("tblBOM_Records")
If rst.RecordCount = 0 Then
    MsgBox "Nothing to Process"
    GoTo Exit_Func
    Exit Function
End If

tmpSystemLine = 0
tmpSystem = ""
Do While Not rst.EOF

    If tmpOrder <> rst![Order Number] Then  ' new section
        tmpOrderLine = 1

        tmpItemNo = 1
        Set System = Systems.NewChild("System", "")
        System.NewChild2 "SystemNumber", tmpSystemLine
        System.NewChild2 "SystemName", "SYSTEM_ASSY"
        System.NewChild2 "SystemIDNumber", rst![Order Number] & "." & tmpSystemLine
        System.NewChild2 "Quantity", "1"
            Set SystemItems = System.NewChild("SystemItems", "")

    End If

    If tmpSystem <> rst![Lot_code_Sap] Then
        tmpSystemLine = tmpSystemLine + 1
        Set SystemItem = SystemItems.NewChild("SystemItem", "")

        SystemItem.NewChild2 "OriginalItemNumber", tmpOrderLine & "." & tmpSystemLine
        SystemItem.NewChild2 "Product", rst![Lot_code_Sap]
        SystemItem.NewChild2 "Description", rst![Order Number]
        SystemItem.NewChild2 "Quantity", 1

        Set ATOComponents = SystemItem.NewChild("ATOComponents", "")
        tmpItemNo = 1

    End If

                    Set ATOComponent = ATOComponents.NewChild("ATOComponent", "")

                    ATOComponent.NewChild2 "ATOItemNumber", tmpOrderLine & "." & tmpSystemLine & "." & tmpItemNo
                    ATOComponent.NewChild2 "Product", rst!Product
                    ATOComponent.NewChild2 "Description", rst![Product type]
                    ATOComponent.NewChild2 "Quantity", rst!Qty
                    ATOComponent.NewChild2 "UnitCost", "0"

        tmpOrder = rst![Order Number]
        tmpSystem = rst![Lot_code_Sap]

        tmpItemNo = tmpItemNo + 1

rst.MoveNext ' goto the next line


'  Save the XML:
Dim success As Long
success = xml.SaveXml("c:SAP_XML_Test.xml")
If (success <> 1) Then
    MsgBox xml.LastErrorText
End If

Set rst = Nothing
Set ProductHeader = Nothing
Set Systems = Nothing
Set System = Nothing
Set SystemItems = Nothing
Set ATOComponents = Nothing
Set ATOComponent = Nothing

End Function

Catex Exhibition 8-10 Feb

We will be attending the Catex Exhibition in the RDS on the 8, 9 and 10th of February, presenting the latest features of our Van sales system and demonstrating the integration to Sage and Intact.

You can visit us at Stand C34

Oracle ODBC Timeout Issues

We have had a number of issues with Access database giving the following error

[ODBC][Ora]ORA-01013: user requested cancel of current operation

Assuming you can access the ODBC database source directly the issue can be fixed by changing the Access query properties. Click on the query and select design , right click in the query and select properties, change the value for ODBC Timeout from 60 to 0( 0 is no timeout)

Save the query and retry.

2 Digit Year Isue

2 Digit Year Issue

Some of our clients were having issues with date entry in various applications since the 1st of Jan

The issue relates to how windows interprets a 2 digit year. Under control panel, regional and language, click on customize , then click on the date tab.  The first section the calendar shows the range of years used to interpret the 2 digit year. This was set to 1911 to 2010 on some PC’s- when they entered a date for 2011 as 2 digits windows converted the year to 1911.

To fix this simply move the year to 2050.

Sage Load speed increased

We have updated our sage links for MASC and Wrap. The invoice upload routine has a number of new options which can increase the upload speed of your invoices into sage dramatically.

For more details contact our office

Aceeca quick start guide

This is a link to the Aceeca user guide

See page 2 for the hard reset command.

Searching for text within files

I was first introduced to GREP in the early 90’s by Joe Sims! very useful

Reading the registry

Our custom PDF printer has a flaw which requires the user to configure the printer to print directly to the file otherwise the reports are spooled and printed into the incorrect name.

I used this function to check the setting and warn the user

Function GetPrinter()
If fReturnRegKeyValue(HKEY_LOCAL_MACHINE, "SOFTWAREMicrosoftWindows NTCurrentVersionPrintPrintersEMS PDFDsSpooler", "printSpooling") = "PrintWhileSpooling" Then
    MsgBox "Your printer is not configured correctly. " & vbCrLf & "change the advanced setting to print directly to the printer"
    MsgBox "Your Setting is correct:" & fReturnRegKeyValue(HKEY_LOCAL_MACHINE, "SOFTWAREMicrosoftWindows NTCurrentVersionPrintPrintersEMS PDFDsSpooler", "printSpooling")
End If

End Function

This uses the function from the access web

Excel / IE links not working

If you received this error after uninstalling Chrome (or Firefox) browser you may also need to change the HTM/HTML association in the registry.

1. Start, click Run, type Regedit in the Open box, and then click OK.
2. Browse to HKEY_CURRENT_USERSoftwareClasses.html
3. Right click the value for the .html key and select Modify…
4. Change the value from “ChromeHTML” to “htmlfile” (or from FireFoxHTML to htmlfile)
Repeat these steps for htm and .shtml keys if they exist.