• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

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  
0
sherman6789
Asked:
sherman6789
  • 10
  • 7
  • 3
2 Solutions
 
als315Commented:
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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26648356.html
0
 
Helen FeddemaCommented:
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

0
 
Helen FeddemaCommented:
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

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Helen FeddemaCommented:
This allows you to change the PDF folder without editing a hard-coded path in the code.
0
 
sherman6789Author Commented:
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") ?
0
 
sherman6789Author Commented:
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
0
 
als315Commented:
Yes, this should work:
strpubBasicPDFPath = IIf(IsNull(Me.BasicPDFPath), "", "g:\voke2011\sectionA\colordata\vokescans\" & Me.BasicPDFPath)
File name should be with extension (.pdf or other).
0
 
sherman6789Author Commented:
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
0
 
sherman6789Author Commented:
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
0
 
als315Commented:
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?
0
 
sherman6789Author Commented:
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
0
 
sherman6789Author Commented:
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
0
 
als315Commented:
It seems to me you have not uploaded DB
0
 
sherman6789Author Commented:
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
0
 
als315Commented:
Try to change  one sub:
 
Private Sub cmdPRPDF_Click()
strpubBasicOrPmtPDF = "Pmt"
If Me.[subfrmPaymentRecords].Form![PR-PDFPath] = "" Then
        MsgBox "These is no PDF associated with this record.", vbInformation
        Exit Sub
End If
strpubPmtPDFPath = "g:\OVC-Databases\cvf-log\pdf-scans\" & Me.[subfrmPaymentRecords].Form![PR-PDFPath]
DoCmd.OpenForm "frmPDF"
End Sub

Open in new window

0
 
sherman6789Author Commented:
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
0
 
als315Commented:
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
0
 
als315Commented:
Line 3 in code could be better:
If Me.[subfrmPaymentRecords].Form![PR-PDFPath] & "" = "" Then
0
 
sherman6789Author Commented:
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.
0
 
sherman6789Author Commented:
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
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 10
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now