Modify the form, add a button to the form
Add the following VBA Code
Private Sub ViewPDF_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strURL As String
Dim lSuccess As Long
Dim strVoucher As String
Dim intDocType As Integer
Dim strNote As String
'open an ADODB connection
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn
'validate the form voucher number and doctype
strVoucher = Me.VoucherNo
If strVoucher = "" Then
Exit Sub
End If
intDocType = Me.DocumentType
'call the stored procedure
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dd_PMNotes_SEL_byVoucher"
cmd.Parameters.Append cmd.CreateParameter("@vchrnmbr", adVarChar, adParamInput, 21, strVoucher)
cmd.Parameters.Append cmd.CreateParameter("@doctype", adInteger, adParamInput, 0, intDocType)
Set rst = cmd.Execute
If Not rst.EOF Then
strURL = rst("pdf")
If strURL > "" Then
'this command will call the application associated with the file. If there is no associated
'application, this will generate an error
Set WshShell = CreateObject("WScript.Shell")
WshShell.Run (strURL)
End If
End If
'Close the connection.
If cn.State = 1 Then
cn.Close
End If
End Sub
Add this stored procedure
IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_PMNotes_SEL_byVoucher') begin
DROP proc dd_PMNotes_SEL_byVoucher
end
GO
CREATE proc dd_PMNotes_SEL_byVoucher
-- dd_PMNotes_SEL_byVoucher '00000000000000462', 1
@vchrnmbr VARCHAR(21),
@doctype INT
AS
set transaction isolation level read uncommitted
--we decided to store the file path here. The client does not have modifier, this was easiest.
DECLARE @PDFDirectory VARCHAR(100) = 'C:\Temp'
DECLARE @txtfield VARCHAR(MAX)
DECLARE @out VARCHAR(100)
DECLARE @index AS INT
DECLARE @index2 AS INT
DECLARE @reverse VARCHAR(MAX)
SET @out = ''
SELECT
@txtfield = n.txtfield
FROM sy03900 n
JOIN (
SELECT vchrnmbr, doctype, NOTEINDX, 'work' AS Source FROM pm10000
UNION ALL
SELECT vchrnmbr, doctype, NOTEINDX, 'Open' AS Source FROM pm20000
UNION ALL
SELECT vchrnmbr, doctype, NOTEINDX, 'Hist' AS Source FROM pm30200
) pt ON pt.NOTEINDX = n.NOTEINDX
WHERE pt.VCHRNMBR = @vchrnmbr AND pt.DOCTYPE = @doctype
--the file name might be mixed in with a bunch of text, like this:
--'some random text mydoc.pdf some more text
--this code will retrieve it
--reverse the text in the text field
SET @reverse = REVERSE(@txtfield)
--find the 'signature' of a pdf document
SET @index = CHARINDEX('fdp.',@reverse)
--if we found it...
IF @index > 0 BEGIN
--find the next space
SET @index2 = CHARINDEX(' ',@reverse,@index)
--if there is no space, then it's at the end of the string
--now we know where it is. parse it out
IF @index2 = 0 BEGIN
SET @out = LEFT(@txtfield,LEN(@txtfield) - @index + 1 )
END ELSE BEGIN
SET @out = SUBSTRING(@txtfield,LEN(@txtfield) - @index2 + 2, @index2 - @index)
end
end
--don't assume the user added the '\'
IF LEFT(REVERSE(@PDFDirectory),1) <> '\' BEGIN
SET @PDFDirectory = @PDFDirectory + '\'
END
--return the file name
IF @out > '' BEGIN
SELECT QUOTENAME( @PDFDirectory + @out ,CHAR(34)) AS PDF
END ELSE BEGIN
SELECT '' AS PDF
end
GO
GRANT EXEC ON dd_PMNotes_SEL_byVoucher TO PUBLIC