How to get the value of posted order no in Order View screen

[mlbechard]

I added a new push button in the Order View screen (OEINQQRY). I would like to get the value in the Order No under Posted Orders. I thought the field name was OrderNo1 but when using this in my code, I get the error message each time I open this screen:

Function SetUniqueCtlName in macform
Error no: 80004005
Description: Unspecified error
Error message: Unspecified error

The OrderNo1 is available when coding in the VB screen but not available at the runtime.

Can anyone help?

Thanks

 

[dgillz]

Progression or ES? Precisely what version would also be nice to know.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[dgillz]

Are you talking about the OEINQORD screen? The screen you have referenced is unfamiliar to me.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[mlbechard]

Sorry for the delay. I took a few days off.

It is Macola ES release 360 (E4SHELL version 4.0.0.323).

You can have access to the OEINQQRY screen by clicking on OE & RMA tab, under View click on Orders/Invoices.

Thanks

 

[dgillz]

The following code works and returns a message box with the order number. I named my push button "Ord Info".

Private Sub OrdInfo_Click()
MsgBox OrderNo1.Text
End Sub

I am using Macola ES 9.5.200. Can you upgrade? You are using a very old version.


Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[mlbechard]

It is planned to upgrade to Macola ES 9.5 but only after the project I am working on right now.

I guess I have 2 choices. Ask the client to upgrade to Macola ES 9.5 and continue on this project later or find a work around to have this value.

Thank you very much.

 

[dgillz]

Does my code not work for you? We need to make sure this is not some other issue unrelated to an upgrade.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[mlbechard]

Your code is not working. I tried it already and it gives me error 'variable not defined' at run time. It is odd because when coding VB gives me all the available properties for the OrderNo1 object but do not recognize it at run time.

It might be an error in this version of Macola and have been fixed in recent release.

Thanks

 

[dgillz]

Please list all the references in your VBA project.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[mlbechard]

The references are:

Visual Basic For Applications
apcConn 1.0 Type Library
OLE automation
apcCon 1.0 Type Library
Microsoft ActiveX Data Objects 2.6 Library
Microsoft Forms 2.0 Object Library

Do I miss a reference?

 

[dgillz]

No, your references are fine. Please post the entire code of your project if that is doable.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[NEmacGuy]

Error in on appconn-macform. Read the posted tried it in my system on ES9.5 and got the same error. Turned out I did not name the push button, and copied the code above. Figured that out and it worked! In between I did delete the modified form from designer.

My guess, pick your pushbutton field by typing macform. and then select from the list.

 

[mlbechard]

Here is the entire code of the push button called ColorSelectionReport:

*******
Private Sub ColorSelectionReport_Click()
Dim lsOrdType As String

If Len(Trim(OrderNo.Text)) > 0 Then
If isOrderExistInCC(False, lsOrdType) Then
strOrderReportName = strAppRptPath & "\" & _
"InstallationToolKit\v11\VOR_ColorSelectionReport.rpt"
Call ShellAndWait(strAppVortexPath & "\CRViewer_RA.exe " & _
strOrderReportName & "|" & Trim(ConnInfo.Server) & "|" & _
ConnInfo.Database & "|" & ConnInfo.User & "|" & _
"order_no|" & Trim(OrderNo.Text) & "|" & _
"order_type|" & lsOrdType, vbNormalFocus)
Shell (strAppVortexPath & "\AltTab.bat")
Else
MsgBox "No Color Choices found for this Order No!" & vbCrLf & _
"Please enter another Order Number", vbExclamation, "Cannot open the report"
End If
Else
If Len(Trim(OrderNo1.Text)) > 0 Then
If isOrderExistInCC(True, lsOrdType) Then
strOrderReportName = strAppRptPath & "\" & _
"InstallationToolKit\v11\VOR_ColorSelectionReport.rpt"
Call ShellAndWait(strAppVortexPath & "\CRViewer_RA.exe " & _
strOrderReportName & "|" & Trim(ConnInfo.Server) & "|" & _
ConnInfo.Database & "|" & ConnInfo.User & "|" & _
"order_no|" & Trim(OrderNo1.Text) & "|" & _
"order_type|" & lsOrdType, vbNormalFocus)
Shell (strAppVortexPath & "\AltTab.bat")
Else
MsgBox "No Color Choices found for this Order No!" & vbCrLf & _
"Please enter another Order Number", vbExclamation, "Cannot open the report"
End If
Else
MsgBox "Invalid Order Number!" & vbCrLf & _
"Please enter a valid Order Number", vbExclamation, "INVALID ORDER NUMBER"
End If
End If
End Sub
**********

Thanks

 

[dgillz]

What is this function?

isOrderExistInCC

There must be code that defines this function. Also, where in your code does it indicate the error is?

I am pretty sure that upgrading is not going to fix this, you have something else wrong.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[mlbechard]

The function isOrderExistInCC is very simple. It verify if the order number exist in a table and return a value from a field.

It is not very important because the error appears before calling this function.

The error 'Function SetUniqueCtlName in macform' appears when opening this screen. If I keep using the screen and click on the Color Selection Report button, another error saying that 'variable not defined' appears at the header of the clicked event of this button and highlight the OrderNo1.

Is there a way that you can try your code using my release?

Thanks

 

[dgillz]

Yes, but you'll have to get me the entire set of code. Every module, the macform and any user forms.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[mlbechard]

Here is all the set of code. There is one module and the macform.

Module1 source code
=====================
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, ByVal dwProcessID As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Public Const MACOLA_CONN_STRING1 = "Provider=SQLOLEDB;Persistent Security Info=False;User ID="
Public Const MACOLA_CONN_STRING2 = ";Pwd=;Data Source="
Public Const MACOLA_CONN_STRING3 = ";Initial Catalog="
Public Const MACOLA_CONN_STRING4 = ";Integrated Security=SSPI;"

Public strOrderReportName As String
Public Const strAppVortexPath = "\\vor2k3srv08\Macola_Image_360\rpt\Vortex"
Public Const strAppRptPath = strAppVortexPath & "\DeployRpts"

Public Function ReadTextFile(FullPathName As String) As String
Dim intFile As Integer
Dim lngFileLen As Long
Dim DataArray As String

intFile = FreeFile()
lngFileLen = FileLen(FullPathName)
Open FullPathName For Binary As intFile
ReadTextFile = Input(lngFileLen, intFile)
Close #intFile
End Function

Public Function IfIsNull(vValue As Variant, vValueIfNull) As Variant
IfIsNull = IIf(IsNull(vValue), vValueIfNull, vValue)
End Function

' Start the indicated program and wait for it to finish, hiding while we wait.
Public Sub ShellAndWait(ByVal program_name As String, ByVal window_style As VbAppWinStyle)
Dim process_id As Long
Dim process_handle As Long

' Start the program.
On Error GoTo ShellError
process_id = Shell(program_name, window_style)
On Error GoTo 0

DoEvents

' Wait for the program to finish.
' Get the process handle.
process_handle = OpenProcess(SYNCHRONIZE, 0, process_id)
If process_handle <> 0 Then
WaitForSingleObject process_handle, INFINITE
CloseHandle process_handle
End If
Exit Sub

ShellError:
MsgBox "Error starting task " & txtProgram.Text & vbCrLf & _
Err.Description, vbOKOnly Or vbExclamation, "Error"
End Sub


Macform source code
===================
Option Explicit

Private MODULE_NAME As String

Private Sub ColorSelectionReport_Click()
Dim lsOrdType As String

If Len(Trim(OrderNo.Text)) > 0 Then
If isOrderExistInCC(False, lsOrdType) Then
strOrderReportName = strAppRptPath & "\" & _
"InstallationToolKit\v11\VOR_ColorSelectionReport.rpt"
Call ShellAndWait(strAppVortexPath & "\CRViewer_RA.exe " & _
strOrderReportName & "|" & Trim(ConnInfo.Server) & "|" & _
ConnInfo.Database & "|" & ConnInfo.User & "|" & _
"order_no|" & Trim(OrderNo.Text) & "|" & _
"order_type|" & lsOrdType, vbNormalFocus)
Shell (strAppVortexPath & "\AltTab.bat")
Else
MsgBox "No Color Choices found for this Order No!" & vbCrLf & _
"Please enter another Order Number", vbExclamation, "Cannot open the report"
End If
Else
If Len(Trim(OrderNo1.Text)) > 0 Then
If isOrderExistInCC(True, lsOrdType) Then
strOrderReportName = strAppRptPath & "\" & _
"InstallationToolKit\v11\VOR_ColorSelectionReport.rpt"
Call ShellAndWait(strAppVortexPath & "\CRViewer_RA.exe " & _
strOrderReportName & "|" & Trim(ConnInfo.Server) & "|" & _
ConnInfo.Database & "|" & ConnInfo.User & "|" & _
"order_no|" & Trim(OrderNo1.Text) & "|" & _
"order_type|" & lsOrdType, vbNormalFocus)
Shell (strAppVortexPath & "\AltTab.bat")
Else
MsgBox "No Color Choices found for this Order No!" & vbCrLf & _
"Please enter another Order Number", vbExclamation, "Cannot open the report"
End If
Else
MsgBox "Invalid Order Number!" & vbCrLf & _
"Please enter a valid Order Number", vbExclamation, "INVALID ORDER NUMBER"
End If
End If
End Sub

Private Function isOrderExistInCC(ByVal aHistoric As Boolean, ByRef aType As String) As Boolean
Dim Macola As New ADODB.Connection
Dim macrs As Recordset
Dim strSQL As String
Dim lbReturn As Boolean

On Error GoTo ErrorHandler
MODULE_NAME = "isOrderExistInCC()"

lbReturn = False
aType = ""

Set Macola = Nothing
Macola.ConnectionTimeout = 120
Macola.Open MACOLA_CONN_STRING1 & ConnInfo.User & MACOLA_CONN_STRING2 & Trim(ConnInfo.Server) & MACOLA_CONN_STRING3 & ConnInfo.Database & MACOLA_CONN_STRING4

If aHistoric Then
strSQL = "SELECT ord_type FROM VOR_ColorChoicesHstOrd " & _
"WHERE ord_no = '" & OrderNo.Text & "' " & _
"ORDER BY ord_type"
Else
strSQL = "SELECT ord_type FROM VOR_ColorChoicesOrdLin " & _
"WHERE ord_no = '" & OrderNo.Text & "' " & _
"ORDER BY ord_type"
End If

Set macrs = New ADODB.Recordset
macrs.Open strSQL, Macola, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not macrs.EOF Then
aType = macrs.Fields("ord_type")
lbReturn = True
End If

macrs.Close
Macola.Close
Set macrs = Nothing
Set Macola = Nothing

isOrderExistInCC = lbReturn
Exit Function

ErrorHandler:
MsgBox "Module Name: " & MODULE_NAME & vbCrLf & _
"Error No: " & Err.Number & vbCrLf & _
"Error Desc: " & Err.Description, vbCritical, _
"Vortex [Report Error to System Admin]"
isOrderExistInCC = False
End Function


Thank you!