Create Sales Order Lines Object from MS Access Module

SUGGESTED

Good Day,

I hope this message finds you well. I am currently facing an issue with a control I've created in MS Access that interacts with Sage100 (Standard 2020 v6.20.4). I'm reaching out to seek your assistance in resolving this matter.

The control performs the following actions:

  1. Logs into Sage100.
  2. Locates and sets the Sales Order, updating the UDF_SMI_Release value on the sales order header.
  3. Iterates through the lines of the Sales Order and updates the UDF_PMQTY value.
  4. write the line record.
  5. write the header record

However, I've encountered a problem. Despite receiving successful return values for edtLne (2), getVal (1), and setVal (1), the write line returns an error stating, "File does not exist: File: RA_Options." When I suppress the write line the header write preforms as expected but the values in the lines section of the Sales Order do not update.

I want to highlight that the Sage version I'm currently testing on has Scanco's PM and Multi-bin installed. Although these enhancements have caused interference with the User Interface (UI) and Business Object Interface (BOI) in the past, I am unsure if they are the cause of this particular issue.

I've included snippets of the associated code and a screenshot of the relevant access screen below:

I would greatly appreciate your expertise and guidance in resolving this issue. If there is any additional information or details you require, please let me know.

Best Regards,

-SKG


Pasted code snippets: Access screenshot below:

setVal = oSOHdr.nSetKeyValue( "SalesOrderNo$", sSONo )
setKey = oSOHdr.nSetKey()
setVal = oSOHdr.nSetValue( "UDF_SMI_RELEASE$", bSMI )
MsgBox( IsObject( oSOHdr.oLines ) )
'
'movVal = oSOHdr.oLines.MoveFirst() '< Error line is commented
'Do While Not cBool( oSOHdr.oLines ) '< Error line is commented
'
edtLne = oSOHdr.oLines.nEditline( sLineKey ) return  value = 2
If edtLne = 0 Then : MsgBox("ERROR edit line method " & oSS.sLastErrorMsg) : Else MsgBox("Edit Line " & edtLne & ":"& sLineKey & " method Good") '< debug only
'
getVal = oSOHdr.oLines.nGetValue( "ItemCode$", sItemCode ) return value = 1
If getVal = 0 Then : MsgBox("ERROR Get value " & oSS.sLastErrorMsg) : Else MsgBox("Get Item " & getVal & ":" & sItemCode & " value Good") '< debug only
'
setVal = oSOHdr.oLines.nSetValue( "UDF_PMQTY", nPMQty ) return value = 1
If setVal = 0 Then : MsgBox("ERROR Set value " & oSS.sLastErrorMsg) : Else MsgBox("Set " & setVal & ":" & nPMQty & " Value method Good") '<debug only
'
wrtVal = oSOHdr.oLines.nWrite() '<Error #12
If wrtVal = 0 Then : MsgBox("Line Write ERROR > " & oSS.sLastErrorMsg) : Else MsgBox("Line wrtVal SO " & wrtVal & ":" & sSONo & sLineKey & " Good") '< debug only
'
'movVal = oSOHdr.oLines.MoveNext() '< Error
'
'Loop '<Error
'
wrtVal = oSOHdr.nWrite()
If wrtVal = 0 Then : MsgBox("ERROR wrtVal " & oSS.sLastErrorMsg) : Else MsgBox("wrtVal SONo " & sSONo & " Good") '< debug only
'
End If
rst.MoveNext

Parents
  • 0

    Steve,

    Thank you for your efforts, but I regret to inform you that the information provided is incorrect, and my question remains unanswered.

    I'm not browsing the record using move methods. Those particular lines are commented out see above code

    With the current code edit line method doesn't add anything.

    This line > wrtVal = oSOHdr.oLines.nWrite() '<Error #12

     

  • 0 in reply to SKG HIBARS

    EditLine() is doing a SetKey().  If the LineKey$ doesn't exist it WILL create a new line and return the 2 which indicates a new record.

    If the value for LineKey$ is blank it will create a new line, returning the 2 which indicates a new record.

  • 0 in reply to Steve Passmore

    Yes, I am indeed aware of the details you mentioned. As previously stated in the script snippet and shown in the provided screenshot, the LineKey$ is passed to the SetValue method using the variable sLineKey. I have thoroughly checked and confirmed the spelling and data types. The return value of edtLne indicates whether it pertains to a new line or an existing one. In this particular case, the expected return value should be 1, correct?

    I have executed the script in the Purchase Order (PO) module, and I have observed the same behavior persisting.

    It is important to note that the client is currently operating Sage2022 with ScanCo PM and MultiBin functionalities, and no JC (Job Costing) directories or modules have been installed.

    Considering this new J/C error (RA error above fixed), I have taken the initiative to request the reseller to open a case with Sage. It is possible that this error is the cause of the incorrect return values we have been discussing.

    Based on these observations, I strongly believe that this issue is an oversight among the programming team during the unit testing phase.

    Regards,

    SKG

    	'
    	'*	###  S E T   S O   P R O G R A M   ###
    	'
    	Dim setProgram As Variant
    	'*
    	setProgram = 0
    	setProgram = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_UI"))
    	If setProgram = 0 Then : MsgBox("ERROR: " & oSS.sLastErrorMsg) : Else  MsgBox("Set Program Good") '< Debug Msg
    	If setProgram <> 0 Then
    		Set oSOHdr = oScript.NewObject("SO_SalesOrder_BUS", oSS )
    		Set oSODtl = oScript.NewObject("SO_SalesOrderDetail_BUS", oSS )
    		'
    		'*	### I T E R A T E   MS  t b l S A L E S D t l
    		'
    		'*	Select sage values from sales detail table
    		sSQL = ( "SELECT SalesOrderNo, LineKey, UDF_PMQTY, Approve FROM tblSalesDtl WHERE Approve = -1 Order By SalesOrderNo ASC" )
    		Set rst = CurrentDb.OpenRecordSet( sSQL )
    		If rst.RecordCount <> 0 Then
    			rst.MoveFirst
    			Do While Not cBool( rst.EOF )
    				'
    				sSONo = Nz( rst.Fields( "SalesOrderNo" ).Value ,"" )
    				sLineKey = Nz( rst.Fields( "LineKey").Value, "" )
    				bApprove = Nz( rst.Fields( "Approve" ).Value, 0 )
    				If bApprove = -1 Then :	bSMI = "Y" : Else bSMI = "N"
    				'
    				'*	### F I N D   S A L E S O R D E R   ###
    				'*	
    				setVal = oSOHdr.nSetKeyValue( "SalesOrderNo$", sSONo )
    				fndVal = oSOHdr.nFind()
    				'
    				'*	### S E T   K E Y ###
    				'
    				If fndVal <> 0 Then
    					setVal = oSOHdr.nSetKeyValue( "SalesOrderNo$", sSONo )
    					setKey = oSOHdr.nSetKey()
    					If setKey <> 0 Then 
    						setVal = oSOHdr.nSetValue( "UDF_SMI_RELEASE$", bSMI ) '< set scan date
    						'
    						'movVal = oSOHdr.oLines.MoveFirst() '< RunTime error# 438 Object doesn't support this property or method
    						'Do While Not cBool( oSOHdr.oLines ) '< Error see above
    							'
    							edtLne = oSOHdr.oLines.nEditline( sLineKey ) 
    							setVal = oSOHdr.oLines.nSetValue( "UDF_PMQTY", nPMQty )
    							wrtVal = oSOHdr.oLines.nWrite() '<Error Module J/C is not on file
    							'
    							'movVal = oSOHdr.oLines.MoveNext() '< Error see above
    							'
    						'Loop '<Error
    						'
    						wrtVal = oSOHdr.nWrite()
    					End If
    					MsgBox("SO Completed") '< Debug Msg
    				End If
    				rst.MoveNext
    			Loop
    		End If
    	End If
    	retVal = oSS.nCleanup()
    	retVal = oSS.nLogoffUser()
    	'
    	Debug.Print "END btnSageDtl_Click"

  • 0 in reply to SKG HIBARS

    After your SetKey for oSOHdr, you need to create your lines object from the already-open header... Ex. oSOLines.

    Set oSOLines = oSession.AsObject(oSOHdr.Lines)

    ...then you should be able to navigate the orders lines.

    movVal = oSOLines.MoveFirst()

    For a lines object, the edit key is not just the LineKey value.

    sSurchargeEditKey = oLines.GetKey()

    . . .

    retVal = oLines.EditLine(sSurchargeEditKey)

  • 0 in reply to Kevin M

    Dear Kevin M,

    I sincerely appreciate the time you've taken to assist me. However, despite your efforts, I'm still encountering the RT error# 438, which states, "Object doesn't support this property or method."

    Please note that the script is being executed from a Microsoft Access module. Modified script below.

    Thank you once again for your support.

    Best regards,

    SKG

    	'*	###  S E T   S O   P R O G R A M   ###
    	'
    	Dim setProgram As Variant
    	'*
    	setProgram = 0
    	setProgram = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_UI"))
    	If setProgram = 0 Then : MsgBox("ERROR: " & oSS.sLastErrorMsg) : Else  MsgBox("Set Program Good") '< Debug Msg
    	If setProgram <> 0 Then
    		Set oSOHdr = oScript.NewObject("SO_SalesOrder_BUS", oSS )
    		'Set oSODtl = oScript.NewObject("SO_SalesOrderDetail_BUS", oSS )
    		'
    		'*	### I T E R A T E   MS  t b l S A L E S D t l
    		'
    		'*	Select sage values from sales detail table
    		sSQL = ( "SELECT SalesOrderNo, LineKey, UDF_PMQTY, Approve FROM tblSalesDtl WHERE Approve = -1 Order By SalesOrderNo ASC" )
    		Set rst = CurrentDb.OpenRecordSet( sSQL )
    		If rst.RecordCount <> 0 Then
    			rst.MoveFirst
    			Do While Not cBool( rst.EOF )
    				'
    				sSONo = Nz( rst.Fields( "SalesOrderNo" ).Value ,"" )
    				sLineKey = Nz( rst.Fields( "LineKey").Value, "" )
    				bApprove = Nz( rst.Fields( "Approve" ).Value, 0 )
    				If bApprove = -1 Then :	bSMI = "Y" : Else bSMI = "N"
    				'
    				'*	### F I N D   S A L E S O R D E R   ###
    				'*	
    				setVal = oSOHdr.nSetKeyValue( "SalesOrderNo$", sSONo )
    				fndVal = oSOHdr.nFind()
    				'
    				'*	### S E T   K E Y ###
    				'
    				If fndVal <> 0 Then
    					setVal = oSOHdr.nSetKeyValue( "SalesOrderNo$", sSONo )
    					setKey = oSOHdr.nSetKey()
    					If setKey <> 0 Then 
    						'
    						'Set oSOLines = oSS.AsObject( oSOHdr.Lines ) '< ERROR RunTime error# 438 Object doesn't support this property or method 
    						'
    						setVal = oSOHdr.nSetValue( "UDF_SMI_RELEASE$", bSMI ) '< set scan date
    						'
    						'movVal = oSOLines.MoveFirst() '< RunTime error# 438 Object doesn't support this property or method
    						'Do While Not cBool( oSOLines ) '< Error see above
    							'
    							getLineKey = oSOLines.GetKey()
    							MsgBox("getLineKey: " & getLineKey ) '< Debug Msg
    							'
    							edtLne = oSOLines.nEditline( sSONo & sLineKey ) 
    							setVal = oSOLines.nSetValue( "UDF_PMQTY", nPMQty )
    							wrtVal = oSOLines.nWrite() '<Error Module J/C is not on file
    							'
    							'movVal = oSOLines.MoveNext() '< Error see above
    							'
    						'Loop '<Error
    						'
    						wrtVal = oSOHdr.nWrite()
    					End If
    					MsgBox("SO Completed") '< Debug Msg
    				End If
    				rst.MoveNext
    			Loop
    		End If
    	End If
    	retVal = oSS.nCleanup()
    	retVal = oSS.nLogoffUser()
    

  • 0 in reply to Steve Passmore

    Steve,

    I hope this message finds you well. Since 1993, I have witnessed the remarkable growth of this product, and even now, I am driven to enhance its usefulness and contribute towards improving the overall user experience.

    I want to express my sincere appreciation for your decision to contribute your time and expertise in the likeness.

    Your commitment is truly valued and makes a significant impact.

    I am committed to continue this research journey to discover a solution that aligns with the customer's desired functionality for the UI.

    I will persevere in my efforts to find the appropriate resolution.

    Best Regards,

    SKG

  • 0 in reply to SKG HIBARS
    SUGGESTED

    In VBA BOI scripting, and BOI in general object properties begin with a letter that identifies type. "o" is for object and while ".lines"  works in event scripting or UDS's, ".oLines" is used in BOI. I also don't use the method AsObject to get a lines object I use:

    Set oSOLines = oSOHdr.oLines

    This is how I was taught in BOI class 25 years ago: Set oGLJournalLines = oGLJournalEntry.oLines

  • 0 in reply to connex

    I don't do external scripting, just UDS run from within Sage business objects.  That is the syntax I use... but of course, some syntax details for external scripts are different, as you know.

Reply Children
No Data