Start by modifying the SOP Inquiry form
In the toolbox, drop a button onto the form
When we drop the button, it looks too plain, make it look nicer by copying its properties from another button.
Appearance and BackColor will do most of it.
In the properties window, click on the second line (DataType), then click on the ellipsis in the header. That will open up the Local Field Definition form.
Name the field
Open the ellipsis next to Static Values and add an image. You only need one.
Once you're happy with how it looks, go back to Dynamics.
Set security to your modified form. There is a guide here
Note that our button is visible, and the window title has a dot(.) before the name. That indicates that the form has been modified. Later we'll see a dot after the name as well, indicating that there is VBA behind the form.
Add the form to VB
Add our button to VBA, and the SOP Number and Sop Type.
There is a trick here. Look at this tutorial to show the hidden SOPTYPE field. Remember to remove it when finished.
This is the 'add field to VB' icon.
When ready, move to the VBA editor
In the left pane, move to the SalesTransactionIquiryZoom form and click on it, to open it in the editor window.
Add a reference to ADO in the TOOLS > REFERENCES form.
Add the code below
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strItemClass As String
Private Declare Function ShellExecute _
Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As Long, _
ByVal Operation As String, _
ByVal Filename As String, _
Optional ByVal Parameters As String, _
Optional ByVal Directory As String, _
Optional ByVal WindowStyle As Long = vbMinimizedFocus _
) As Long
Sub openConnection()
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub
Private Sub btnTLELookup_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
Dim lSuccess As Long
Dim strUrl As String
Dim strSopnumber As String
Dim intSoptype As Integer
Dim strUserDef5 As String
'data access to get the userdef5 field
If cn.State = 0 Then
openConnection
End If
strSopnumber = Me.DocumentNo
intSoptype = Me.SOPType
If strSopnumber = "" Then
Exit Sub
End If
If intSoptype < 2 Or intSoptype > 4 Then
Exit Sub
End If
'get an item
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = "select USRDEF05 from SOP10106 where sopnumbe = ? and soptype = ?"
cmd.Parameters.Append cmd.CreateParameter("@sopnumbe", adVarChar, adParamInput, 21, strSopnumber)
cmd.Parameters.Append cmd.CreateParameter("@soptype", adInteger, adParamInput, 0, intSoptype)
Set rst = cmd.Execute
If rst.EOF Then
MsgBox ("Unable to retrieve the user defined fields")
Exit Sub
Else
strUserDef5 = rst("USRDEF05")
If Trim(strUserDef5) = "" Then
MsgBox ("Unable to retrieve the user defined 5 field")
Exit Sub
End If
End If
strUrl = "http://localhost:5003/tle/workflow/otc/detail?companyid=1&dtid=1&dkid=" + strUserDef5 lSuccess = ShellExecute(0, "Open", strUrl)
End Sub
Private Sub Window_AfterOpen()
openConnection
End Sub
All set, it should work now.