Plus Community - for users of ACCPAC & Adagio accounting software

Welcome to the PlusCommunity forums! PlusCommunity is a gathering place for companies running Simply Accounting, ACCPAC Plus, Sage Accpac ERP (ACCPAC Advantage/ACCPAC for Windows), and Adagio Accounting.
New visitors, please click here!

Page 1 of 1 1
Topic Options
#53344 - 12/28/17 06:26 AM Macro not updating OESHIH.PRINTSTAT Field
S.Herlick
Member


Registered: 11/21/03
Posts: 62
Loc: Canada
Hi All;

I have written a macro that reads a CSV file and creates an OE Order from it. It also sets the quantity shipped equal to the quantity ordered, creates a Shipment document and prints the OE Picking Slip. Everything works but the field OESHIH.PRINTSTAT field is not getting set to 3 to indicate that the Picking Slip has been printed. This means there is the potential to print duplicate paperwork. I have recorded the Picking Slip printing process. When I run it with hard coded values the issue happens. I have also tried to set the PRINTSTAT to 3 a macro directly. The macro updates the text field (used to make sure the macro was running) but it won't update the PRINTSTAT field as illustrated below. When I print the picking Slip directly using the SAGE standard Picking Slip Print Form icon the PRINTSTAT field is updated. I am missing something in the macro but I don't know what. Below are simple versions of the macros I am using. Any suggestions/guidance is welcomed.

Macro to print Picking Slip:

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

Dim OEFLAG As AccpacCOMAPI.AccpacView
mDBLinkCmpRW.OpenView "OE0270", OEFLAG

Dim lSession As Long

With OEFLAG
.Init
.Fields("OPTYPE").Value = 15 'If Header Location selected
.Fields("AUDTYPE").Value = 3
.Fields("FROMDAYEND").Value = 0
.Fields("TODAYEND").Value = 9999
.Fields("FROMSHI").Value = "SH1045191" 'From Location
.Fields("TOSHI").Value = "SH1045191" 'To Location
.Fields("FROMLOC").Value = " " 'From Location
.Fields("TOLOC").Value = "ZZZZZZ" 'To Location
.Fields("LBLREQUIRE").Value = 0
.Fields("LBLPRINTED").Value = 0
' .Fields("REPRINT").PutWithoutVerification (0)
.Process
lSession = .Fields(“SESHNDL”).Value
End With

Dim temp As Boolean
Dim rpt As AccpacCOMAPI.AccpacReport
Set rpt = ReportSelect("OEPICK01[C:\SAGE300\OE63A\ENG\TOR_PICKSLIP_LASER.RPT]", " ", " ")
Dim rptPrintSetup As AccpacCOMAPI.AccpacPrintSetup
Set rptPrintSetup = GetPrintSetup(" ", " ")
rptPrintSetup.DeviceName = "\\dctor02\TOR-HP3"
rptPrintSetup.OutputName = "TOR-HP3"
rptPrintSetup.Orientation = 1
rptPrintSetup.PaperSize = 1
rptPrintSetup.PaperSource = 15
rpt.PrinterSetup rptPrintSetup
rpt.SetParam "SELECTBY", "1" ' Report parameter: 2
rpt.SetParam "SORTBY", "0" ' Report parameter: 3
rpt.SetParam "FROMSELECT", "SH1045191" ' Report parameter: 4
rpt.SetParam "TOSELECT", "SH1045191" ' Report parameter: 5
rpt.SetParam "FROMLOC", " " ' Report parameter: 6
rpt.SetParam "TOLOC", "ZZZZZZ" ' Report parameter: 7
rpt.SetParam "PRINTBY", "0" ' Report parameter: 14
rpt.SetParam "SERIALLOTNUMBERS", "0" ' Report parameter: 15
rpt.SetParam "PRINTKIT", "0" ' Report parameter: 11
rpt.SetParam "PRINTBOM", "0" ' Report parameter: 12
rpt.SetParam "REPRINT", "0" ' Report parameter: 8
rpt.SetParam "QTYDEC", "4" ' Report parameter: 9 - O/E Sales History:Detail,Sort by Item Number
rpt.SetParam "COMPLETED", "0" ' Report parameter: 10
rpt.SetParam "SESHNDL", OEFLAG.Fields("SESHNDL").Value ' Report parameter: 13
rpt.NumOfCopies = 1
rpt.Destination = PD_PREVIEW
rpt.PrintDir = ""
rpt.PrintReport

Exit Sub

Macro to directly update PRINTSTAT field:

On Error GoTo ACCPACErrorHandler

' TODO: To increase efficiency, comment out any unused DB links.
Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

Dim temp As Boolean
Dim OESHI1header As AccpacCOMAPI.AccpacView
Dim OESHI1headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0692", OESHI1header
Set OESHI1headerFields = OESHI1header.Fields

Dim OESHI1detail1 As AccpacCOMAPI.AccpacView
Dim OESHI1detail1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0691", OESHI1detail1
Set OESHI1detail1Fields = OESHI1detail1.Fields

Dim OESHI1detail2 As AccpacCOMAPI.AccpacView
Dim OESHI1detail2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0745", OESHI1detail2
Set OESHI1detail2Fields = OESHI1detail2.Fields

Dim OESHI1detail3 As AccpacCOMAPI.AccpacView
Dim OESHI1detail3Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0190", OESHI1detail3
Set OESHI1detail3Fields = OESHI1detail3.Fields

Dim OESHI1detail4 As AccpacCOMAPI.AccpacView
Dim OESHI1detail4Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0694", OESHI1detail4
Set OESHI1detail4Fields = OESHI1detail4.Fields

Dim OESHI1detail5 As AccpacCOMAPI.AccpacView
Dim OESHI1detail5Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0704", OESHI1detail5
Set OESHI1detail5Fields = OESHI1detail5.Fields

Dim OESHI1detail6 As AccpacCOMAPI.AccpacView
Dim OESHI1detail6Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0708", OESHI1detail6
Set OESHI1detail6Fields = OESHI1detail6.Fields

Dim OESHI1detail7 As AccpacCOMAPI.AccpacView
Dim OESHI1detail7Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0709", OESHI1detail7
Set OESHI1detail7Fields = OESHI1detail7.Fields

Dim OESHI1detail8 As AccpacCOMAPI.AccpacView
Dim OESHI1detail8Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0702", OESHI1detail8
Set OESHI1detail8Fields = OESHI1detail8.Fields

Dim OESHI1detail9 As AccpacCOMAPI.AccpacView
Dim OESHI1detail9Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0703", OESHI1detail9
Set OESHI1detail9Fields = OESHI1detail9.Fields

Dim OESHI1detail10 As AccpacCOMAPI.AccpacView
Dim OESHI1detail10Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0706", OESHI1detail10
Set OESHI1detail10Fields = OESHI1detail10.Fields

Dim OESHI1detail11 As AccpacCOMAPI.AccpacView
Dim OESHI1detail11Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0707", OESHI1detail11
Set OESHI1detail11Fields = OESHI1detail11.Fields

Dim OESHI1detail12 As AccpacCOMAPI.AccpacView
Dim OESHI1detail12Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0705", OESHI1detail12
Set OESHI1detail12Fields = OESHI1detail12.Fields

OESHI1header.Compose Array(OESHI1detail1, Nothing, OESHI1detail3, OESHI1detail2, OESHI1detail4, OESHI1detail5)

OESHI1detail1.Compose Array(OESHI1header, Nothing, OESHI1detail8, OESHI1detail12, OESHI1detail9, OESHI1detail7, OESHI1detail6)

OESHI1detail2.Compose Array(OESHI1header)

OESHI1detail3.Compose Array(OESHI1header, OESHI1detail1)

OESHI1detail4.Compose Array(OESHI1header)

OESHI1detail5.Compose Array(OESHI1header)

OESHI1detail6.Compose Array(OESHI1detail1, Nothing)

OESHI1detail7.Compose Array(OESHI1detail1, Nothing)

OESHI1detail8.Compose Array(OESHI1detail1)

OESHI1detail9.Compose Array(OESHI1detail1, OESHI1detail10, Nothing, OESHI1detail11)

OESHI1detail10.Compose Array(OESHI1detail9, Nothing)

OESHI1detail11.Compose Array(OESHI1detail9, Nothing)

OESHI1detail12.Compose Array(OESHI1detail1)

OESHI1headerFields("DRIVENBYUI").Value = "1" ' Driven by UI

OESHI1detail1Fields("DRIVENBYUI").Value = "1" ' Driven by UI
OESHI1header.Cancel
OESHI1header.Init

OESHI1headerFields("PROCESSCMD").PutWithoutVerification ("1") ' Process OIP Command

OESHI1header.Process
temp = OESHI1header.Exists

OESHI1headerFields("SHINUMBER").Value = "SH1045195" ' Shipment Number

OESHI1header.Order = 1
temp = OESHI1header.Exists
OESHI1header.Read
OESHI1header.Order = 0

OESHI1headerFields("DESC").Value = "TEST 9" ' Shipment Description
OESHI1headerFields("PRINTSTAT").PutWithoutVerification (3)
OESHI1headerFields("OECOMMAND").Value = "4" ' Process O/E Command

OESHI1header.Process
temp = OESHI1header.Exists
OESHI1header.Update
OESHI1header.Read


Exit Sub


Thanks

Stephen

Top
#53345 - 12/28/17 06:30 AM Re: Macro not updating OESHIH.PRINTSTAT Field [Re: S.Herlick]
Jay Converse Administrator
Member


Registered: 07/31/00
Posts: 7663
Loc: Location, Location
Use the CS0120 view and write the value directly with SQL.
_________________________
Jay Converse
Sage 300 Whisperer

Top
#53346 - 12/28/17 06:34 AM Re: Macro not updating OESHIH.PRINTSTAT Field [Re: Jay Converse]
S.Herlick
Member


Registered: 11/21/03
Posts: 62
Loc: Canada
Hi Jay;

Thanks for the quick reply. I am trying to update the field in VBA code in SAGE as the macro that imports OE orders will be run many times during the day. Can I update the CS0120 view in SAGE VBA? I am googling how to use it now.

Stephen

Top
#53347 - 12/28/17 06:39 AM Re: Macro not updating OESHIH.PRINTSTAT Field [Re: S.Herlick]
Jay Converse Administrator
Member


Registered: 07/31/00
Posts: 7663
Loc: Location, Location
cs0120.Browse "YOUR PURE SQL CODE HERE", True
_________________________
Jay Converse
Sage 300 Whisperer

Top
#53348 - 12/28/17 06:55 AM Re: Macro not updating OESHIH.PRINTSTAT Field [Re: Jay Converse]
S.Herlick
Member


Registered: 11/21/03
Posts: 62
Loc: Canada
EXCELLENT! Works.

Thank you for being online during the winter break.

Top
Page 1 of 1 1


Moderator:  Jay Converse 
Hop to: