asked on

How do we embed the path needed for a PDF form to find files to be displayed?

We have an Access program that in addition to providing important information in various fields, it allows us to click a button and view a PDF file on the screen within a special form for each record. The way that it is now set up, we have a text box where we type the entire path and pdf file name. ex: g:\voke2011\sectionA\colordata\vokescans\abc050211.pdf.
This worked ok for us when we had only a few files. Now the number of files is increasing so much that the users want to just enter the file name (Ex: abc050211.pdf) and let the program automatically know where the file is located in the proper directory. We would like to leave off the ",pdf", if possible.

The button has the following code in the "ON CLICK" area of the main form:
------------------------------------------------------------------------------------------
Private Sub Form_Current()
strpubBasicPDFPath = IIf(IsNull(Me.BasicPDFPath), "", Me.BasicPDFPath)
End Sub
------------------------------------------------------------------------------------------
Private Sub cmdBasicPDF_Click()
strpubBasicOrPmtPDF = "Basic"
If strpubBasicPDFPath = "" Then
MsgBox "These is no PDF associated with this record.", vbInformation
Exit Sub
End If
DoCmd.OpenForm "frmPDF"
End Sub
------------------------------------------------------------------------------------------
Private Sub cmdPRPDF_Click()
strpubBasicOrPmtPDF = "Pmt"
If strpubPmtPDFPath = "" Then
MsgBox "These is no PDF associated with this record.", vbInformation
Exit Sub
End If

DoCmd.OpenForm "frmPDF"
End Sub
------------------------------------------------------------------------------------------

I should mention that there are two buttons on the main form. One button is for displaying a PDF with basic information "Basic" shown.
The other button is for displaying payment information "Pmt".
The PDF form was created by an Experts-Exchange expert and we have been usiing it, wthout problems, for a long time.

The frmPDF form has this code in the "ON OPEN" section:
------------------------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
If strpubBasicOrPmtPDF = "Pmt" Then
If strpubPmtPDFPath = "" Then
Me.WebBrowser0.Navigate ("about:blank")
Else
Me.WebBrowser0.Navigate strpubPmtPDFPath
End If
ElseIf strpubBasicOrPmtPDF = "Basic" Then
If strpubBasicPDFPath = "" Then
Me.WebBrowser0.Navigate ("about:blank")
Else
Me.WebBrowser0.Navigate strpubBasicPDFPath
End If
End If
End Sub
------------------------------------------------------------------------------------------

I don't know if this is important for the operation of the search and PDF display but the following is stored in the "Modules" section of the main form.
------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

'----Public Variables----
'PDF Path for the Payment PDF
Public strpubPmtPDFPath As String
'PDF Path for the main PDF
Public strpubBasicPDFPath As String
'Determines what button was clicked
'Generates "Pmt" or "Basic"
Public strpubBasicOrPmtPDF As String
------------------------------------------------------------------------------------------

I hope that this is enough information to help you. The main thing that I need to know is how can we embed the location into the program so that the user will only need to enter the file name in the text box. The setup for the payment box is slightly different and I figure that if you can show me how to get the directory path into the program for the Basic side, I can do the Payment side the same way. This is the current path: g:\voke2011\sectionA\colordata\vokescans. All of the PDFs for this program are in this same directory path. Soon, I will move all of these files into a shorter named directory with less subdirectories.

Any assistance that you can give will be appreciated. Thank you.

WRS

als315

I think you can add path to file name (if Me.BasicPDFPath is file name):
strpubBasicPDFPath = IIf(IsNull(Me.BasicPDFPath), "", "g:\voke2011\sectionA\colordata\vokescans\" & Me.BasicPDFPath & ".pdf")
You can also find here many examples with file browsing:
https://www.experts-exchange.com/questions/26648356/API-file-selection.html

Helen Feddema

You can browse for the file using the FileDialog object. Here is some code:

Public Function SelectFile() As String
'Requires Office XP (2002) or higher
'Requires a reference to the Microsoft Office Object Library
'Created by Helen Feddema 3-Aug-2009
'Last modified 3-Aug-2009

On Error GoTo ErrorHandler

   Dim fd As Office.FileDialog
   Dim varSelectedItem As Variant
   Dim strFileNameAndPath As String
   
   'Create a FileDialog object as a File Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
   
   With fd
      'Set AllowMultiSelect to True to allow selection of multiple files
      .AllowMultiSelect = False
      .Title = "Browse for File"
      .ButtonName = "Select"
      .Filters.Clear
      'Modify filters as needed
      .Filters.Add "Documents", "*.doc; *.txt", 1
      .InitialView = msoFileDialogViewDetails
      If .Show = -1 Then
         'Get selected item in the FileDialogSelectedItems collection
         For Each varSelectedItem In .SelectedItems
            strFileNameAndPath = CStr(varSelectedItem)
         Next varSelectedItem
      Else
         Debug.Print "User pressed Cancel"
         strFileNameAndPath = ""
      End If
   End With
   
   SelectFile = strFileNameAndPath
   
ErrorHandlerExit:
   Set fd = Nothing
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in SelectFile procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

Helen Feddema

You can add a set of controls (a command button and a textbox) to the main menu for selecting the PDF folder, and then use that to set the initial folder in the FileDialog. Here is some code for that:

Private Sub cmdPDFDocsPath_Click()
'Created by Helen Feddema 21-Apr-2011
'Last modified by Helen Feddema 21-Apr-2011

On Error GoTo ErrorHandler

   'Create a FileDialog object as a Folder Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFolderPicker)
   Set txt = Me![txtPDFDocsPath]
   strPropertyName = "PDFDocsPath"
   strPath = GetProperty(strPropertyName, "")
   
   With fd
      .Title = "Browse for folder where PDF reports are stored"
      .ButtonName = "Select"
      .InitialView = msoFileDialogViewDetails
      .InitialFileName = strPath
      If .Show = -1 Then
         strPropertyValue = CStr(fd.SelectedItems.Item(1))
         lngDataType = dbText
         Call SetProperty(strPropertyName, lngDataType, _
            strPropertyValue)
         txt.Value = strPropertyValue
      Else
         Debug.Print "User pressed Cancel"
      End If
   End With
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Helen Feddema

This allows you to change the PDF folder without editing a hard-coded path in the code.

sherman6789

ASKER

Thanks als315,
Your solution is definately in the right direction. I learned that some of the items have "PDF" on the end and some donot. Therefore; I believe that we will have to use whatever is at the end of the original file. If I put PDF on a pdf file that does not have PDF typed on the end, it will not work and vice versa.

The change works on some items and not on others. should I change it like the following:
strpubBasicPDFPath = IIf(IsNull(Me.BasicPDFPath), "", "g:\voke2011\sectionA\colordata\vokescans\" & Me.BasicPDFPath") ?

sherman6789

ASKER

Hello,
I forgot to mention that we are now using MS Access Version 7. I have been using version 3 until this year. Does that make a difference? The users have just begun to use this file again since June of last year. I was not sure if the change in version had anythng to do with the problem in addition to the fact that we now have more items to input into the system.

Thanks to als315:and Helen_Feddema for your responses.

Helen, what is the main difference in your version vs. the suggestion from als315?

I have other databases that allow us to view PDF files and I need to understand what and why I am doing things.

Thanks to both of you.

WS

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sherman6789

ASKER

Thanks als315

Your change worked perfectly for the "Basic" Button. When I tried to use the "Payment" button, the results indicated that the program thought that I was trying to access an incorrect "web address".

I think that the change does not correct the "payment button" problem. The payment button works if I place the directory and file name together but not when I just enter the file name.

Below is the code for the Basic Data (Basic) and the Payment Record (Pmt) buttons from the main form..

NOTE: The actual path has been changed and is different from the one shown on my original question.
"g:\voke2011\sectionA\colordata\vokescans\" is actually
"g:\OVC-Databases\cvf-log\pdf-scans\"

-------------------------------------------------------------------------------
Option Compare Database

Private Sub Form_Current()
strpubBasicPDFPath = IIf(IsNull(Me.BasicPDFPath), "", "g:\OVC-Databases\cvf-log\pdf-scans\" & Me.BasicPDFPath)
End Sub
-------------------------------------------------------------------------------
Private Sub cmdBasicPDF_Click()

strpubBasicOrPmtPDF = "Basic"

If strpubBasicPDFPath = "" Then
MsgBox "These is no PDF associated with this record.", vbInformation
Exit Sub
End If

DoCmd.OpenForm "frmPDF"

End Sub
-------------------------------------------------------------------------------
Private Sub cmdPRPDF_Click()
strpubBasicOrPmtPDF = "Pmt"

If strpubPmtPDFPath = "" Then
MsgBox "These is no PDF associated with this record.", vbInformation
Exit Sub
End If

DoCmd.OpenForm "frmPDF"

End Sub
-------------------------------------------------------------------------------

Maybe the following two lines need to be handled above differently.
strpubBasicPDFPath
strpubPmtPDFPath

If this is not enough information, please let me know. Thank you.

WRS

sherman6789

ASKER

To: Helen_Feddema

Since all of our PDF files for the program are in the same folder, it seems that it will not be necessary for the program to search for a particular file. If it does, will this take significantly longer? What will happen if there are to directories that happen to have a file with the same name?

I do see where your search procedures might come in handy on various projects but do you think that there is an advantage in this particular project?

Thanks for your assistance and consideration.

WRS

als315

You have assigned value only for strpubBasicPDFPath
Private Sub Form_Current()
strpubBasicPDFPath = IIf(IsNull(Me.BasicPDFPath), "", "g:\OVC-Databases\cvf-log\pdf-scans\" & Me.BasicPDFPath)
End Sub
I don't see assignment for strpubPmtPDFPath anywhere.
May be you can upload a part from your DB with involved forms and code?

sherman6789

ASKER

Hello als315,

Thank you for the time you have put in this project. Even though I am still learning Access code, I could tell that there seemed to be no reference to the Payment button or taxt box. The payment button and subform will work if the entire path and files name is entered into the PR-PDF field. We want it to work like you have the Basic text box and button, if possible. I hope that the attached sample file will help. I have also attached a MS Word file shich shows the layout of the main form. The arrows and text box have information to help you find what you need. The file name for the payments are placed in the Access subform field marked PR-PDF. The action button is labeld Paymen & Check PDF.The other button and text box work well after I input your suggestions. I have also attached a few PDF files for your test.
Thank you for your assistance.

WRS
EE---TEST-Log-Layout.doc
PDF-circle.pdf
PDF-ABC.pdf
Court-XYZ.pdf
EE---TEST-Log-Layout.doc

sherman6789

ASKER

als315,

I forgot to mention that you may have to hold the shift key down while you load the file to get access to the navigation bar and/or ribbon. I realize that you probably already know that but I wanted to save you some time if it seemed to not work at first.

Thanks again.

WRS

als315

It seems to me you have not uploaded DB

sherman6789

ASKER

Sorry als315,

You are correct. I was so excited to send the information to you that I missed sending you the most important file. It is now attached.

Thanks.

WRS
EE-Test.mdb

SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sherman6789

ASKER

Hello,
I was about to enter the nine lines above that you sent this morning and noticed that these lines are already there. Could it be that the lines that need to be modified or added deal with "Private Sub Form_Current()" ?
Maybe the code that needs to be added is similar to the following:
---------------------------------------------------------------------------------------------------------
Option Compare Database

Private Sub Form_Current()
strpubBasicPDFPath = IIf(IsNull(Me.BasicPDFPath), "", "g:\OVC-Databases\cvf-log\pdf-scans\" & Me.BasicPDFPath)
End Sub
---------------------------------------------------------------------------------------------------------

Thanks
WRS

als315

Lines 3 and 7 are different.
You can of course add this line:
strpubPmtPDFPath = "g:\OVC-Databases\cvf-log\pdf-scans\" & Me.[subfrmPaymentRecords].Form![PR-PDFPath]
to Form_Current event, but result will be same

als315

Line 3 in code could be better:
If Me.[subfrmPaymentRecords].Form![PR-PDFPath] & "" = "" Then

sherman6789

ASKER

Thank you als315. all parts of the PDF problem have been corrected and are working perfectly. This will be much easier for all of the users. It has already been tested by four users in the office with not problems. After you let me know that there were changes in the code that you sent, I place it in the system and it began working immediately.

Again, thank you and I will close this thread.

sherman6789

ASKER

The solution that was given by als315 is great. It works well and will save a lot of time and energy by up to 14 staff members.

I will also be able to incorprate this solution on four other projects that I have. This solution will be added to my database of important solutions to problems.

Thanks again als315. I wish that I were allow to give you more points.

WRS