Hi everyone, would you please have a look at this and see why this is running erratically? I've seen some sample scripts here and there, and the bulk of my script is from the other posts on this forum. I must be misunderstanding what objects and what properties/values must be set in order to achieve a successful print job, and I'm a bit lost now. The issues are the the scripts do not run consistently, and breaks when a certain user action happens . please read below for detail.
Objectives
To have a list of sales orders in Excel workbook for user to print Picking Sheet of one or many orders. The workbook is expected to be open throughout the day.
Conditions and Considerations
- Sage 100c Advanced 2017 (5.40.3.0) & VBA.
- The form code used in this script "PICKSHEET-KY" exists in SY_ReportSetting. This is a heavily used form code. Using a newly created form code in the script did not change the behavior.
- This script successfully prints after manually launching a Picking Sheet Printing task from the launcher, physically type in the form code in the GUI, and process an order.
- The script will continue to print UNTIL a picking sheet is printed with the same form code from the PickingSheet Quick Print UI launched from the Sales Order Entry UI through clicking the button. Doing #3 will make it work again always. Doing #4 will break it always.
- Whether the script prints or not, it always resets the form code of both Picking Sheet Printing and Quick Picking Sheet Printing to "STANDARD" for the user.
- SetKeyValue and SetValue for PrintPickshee() seems unnecessary since commenting them out does not change the behavior, and it still prints in the condition #3 above.
- All other posts with sample scripts included this, as well as some BOI training documents so I'm a bit perplexed on this too.
'Project-Level Variable Declaration
'public objects below are used so that the login prompt at open can maintain the objects in scope until the workbook is closed Public oScript As Object Public oSS As Object 'Below collection is updated when the user's order selection changes. Public colSelectedOrders as New Collection
Module code:
Note: Login() is called at Workbook open. Error checking and cleanup lines have been omitted.
Public Sub Login()
****Script (oScript), Session (oSS) object intantiation ommitted****
retval = nSetUser("UserID","Password")
retval = nSetCompany("Company")
sDate = oSS.sModuleDate
retval = nSetDate("S/O", sDate)
retval = oSS.SetModule("S/O")
oSS.nSetProgram (oSS.nLookupTask("SO_PickingSheetPrinting_UI"))
End Sub
All errors mentioned below are from oPickPrint object (SO_PickingSheetPrinting_rpt)
Public Sub PrintPicksheet()
Dim oPickPrint as Object, oSoEntry as object
Dim sSelectedOrders as string : sSelectedOrders = "0123457" 'This variable is updates when a Collection Obj colSelectedOrders changes
Dim sLastFormUsed as string : sLastFormUsed = "PICKSHEET-KY"
'Initialize object
Call Login 'In case the user has been kicked out or error foces oSS to go out of scope
Set oPickPrint = oScript.NewObject("SO_PickingSheetPrinting_rpt", oSS)
'Set UI termination
Select Case sPrintMode 'sPrintMode = value of combobox on form
Case "PRINT", "DEFFERED"
retavl = oSS.nTerminateUI()
Case Else
retavl = oSS.nInitiateUI()
End Select
'Set Template
Dim sLastFormUsed As String
sLastFormUsed = oPickPrint.sGetLastForm() 'The value is PICKSHEET-KY
If sLastFormUsed = "" Then 'Something happened to clear the form code from the last run. Assign from the stored value in worksheet
sLastFormUsed = ThisWorkbook.Worksheets("ProgramProperties").Range("B4").value
Else
ThisWorkbook.Worksheets("ProgramProperties").Range("B4").value = sLastFormUsed
End If
retavl = oPickPrint.nSelectReportSetting(sLastFormUsed)
'^ retval=1
'Below 3 lines return retval = 0 with the error message
'"The column is not part of the key."
retavl = oPickPrint.nSetKeyValue("ReportID", "SO_PickingSheetPrinting_UI")
retavl = oPickPrint.nSetKeyValue("ReportSetting$", sLastFormUsed)
retavl = oPickPrint.nSetKeyValue("RowKey$", "1")
retavl = oPickPrint.nSetKey()
'^ retval = 2
'Below 6 lines return retval = 1 with the warning messsage
"The S/O0000000001SO_PICKINGSHEETPRINTING_UI PICKSHEET-KY 00001 is invalid
retavl = oPickPrint.nSetValue("SelectField$", "Order Number")
retavl = oPickPrint.nSetValue("SelectFieldValue$", "Order Number")
retavl = oPickPrint.nSetValue("Tag$", "TABLE=SO_SALESORDERHEADER; COLUMN=SALESORDERNO$")
retavl = oPickPrint.nSetValue("Operand$", "=")
retavl = oPickPrint.nSetValue("Value1$", sSelectedOrders)
retavl = oPickPrint.nWrite()
'Updated Selected Sales Order's PrintPicksheet setting
Set oSoEntry = oScript.NewObject("SO_SalesOrder_Bus", oSS)
For i = 1 To colSelectedOrders.Count 'Loop through collection object that stores user selected order numbers
retVal = oSoEntry.nSetKey(colSelectedOrders.Item(i))
retVal = oSoEntry.nSetValue("PrintPickingSheets$", "Y")
retVal = oSoEntry.nWrite()
Next i
Set oSoEntry = nothing
'Process
retVal = oPickPrint.nProcessReport(sPrintMode)
^retval = 0, error message "Data is not selected for report printing"
'Cleanup
Set oPickPrint = Nothing
End sub
What am I missing here to be able to....
- Print successfully every time (given the form code exists)?
- Make the form code not reset to Standard?
Ken
