ACCESS FEED: Re: [MS_AccessPros] Re: Working with PDFS in Access 2013

Sorry all , I finally decided to take the time to chime in. I just picked up a contract that had an Access database that not only stored the physical documents in the database but also tried to display them in a form control.

The first thing I did was to get the files out of Access and place them in a folder on a server so that they'd be backed up, wouldn't be stored in multiple locations, and all users would have access to them. I also needed to drop the Access database size down from 1.4 GB before the users actually started using it a lot.

I then looked at the form and the control designed to display the contents of a file. Since I got old, I've noticed that my eyes have done so along with the balance of my body. The information displayed in the control was too small for me to easily read. I am supporting users with different size monitors and different resolutions. Therefore I decided to retain the information needed to open the attachment in the database but to use the attachment's native application to open and display the file's contents.

This direction allowed me to let users store any type of file and open it as long as the file extension was tied to a registered application on their PC.

I set up the attachment location in its own table and made sure that the full path was dynamic rather than disk drive specific. I used a simple form to maintain the path so that when the server changed, it's easy to update the attachment path. Here's an example of the path.

\\sbedfs11\$NET_SBOE$\SBOENEW\ADMIN\Important_COMPLIANCE\LEGAL_Stuff\LEGAL Stuff CHECKS\EVENT Attachments\SAVE Responses

I didn't need to have different locations but could do so easily enough with the addition of a user specific index or key.

I then, since I can have a many to many relationship between the attachments and the records they are attached to, I built a child table that is linked to the parent table that lists each attachment's name and not much else. The attachment names are displayed in a textbox in a subform to list them all.

I added the following double click event to the textbox open and display the attachment.

Private Sub DocumentName_DblClick(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strQuery As String
Dim strFolder As String

    Set db = CurrentDb
    strQuery = "SELECT tbl_Attachments_Location.AttachmentPath FROM tbl_Attachments_Location;"
    Set rs = db.OpenRecordset(strQuery)
    strFolder = rs![AttachmentPath]

    rs.close
    Set rs = Nothing
    Set db = Nothing
    strFolder = strFolder & "\"
    ShellExecute 0&, "OPEN", strFolder & DocumentName.Value, "", "", 1

End Sub

~~~ end of code snippet ~~~

I found that the user community much preferred to see their attachments in the appropriate application. They could do what they wanted with the opened attachment. Another benefit is that the attachment can be a *pdf, or Excel, or Word, or a graphic file or any other type of file they wish and as long as the file has an application associated with it, it can be opened with the double click.

Thus endth my 02 cents.

Jeff

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, May 30, 2014 10:08:18 AM
Subject: Re: [MS_AccessPros] Re: Working with PDFS in Access 2013

My bad - I didn't notice it's a PDF.

Art- You need a Text field to store the link outside of the OLEBound control.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On May 30, 2014, at 3:55 PM, wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John & Art

Unless I'm mistaken, you have to use an OLEObject control, not an image control to show a PDF. And I was not saying to store a link. The path is still stored as a text. I simply took your sample for showing images and made a couple small changes: the control and a couple lines of code.

When changing the SourceDoc on an OLEBound control you also have to set the action. That's where Me.OLEBound4.Action = acOLECreateLink comes into play. That link is not stored. It is done to create a link to the file for the control's use only.

Art - I'm not sure why the code jumps to the Else line. Comment out the "On Error Resume Next" so you can see the error line.

Bill

---In MS_Access_Professionals@yahoogroups.com, <JohnV@msn.com> wrote :

Art-

Not sure why Bill recommended you continue to store a link to the image in the database in an OLEObject. As of Access 2007, all you need is a Text field to store the full path. Use an Image control on forms and reports and set the Control Source of the Image control to the text field. Access will load the image for you for each record. You still need a bit of code to invoke the file dialog when the user wants to locate and save a new image.

Note that if this is a shared network app, the path must use an UNC path, not a drive letter. You can help ensure that happens by setting the starting .Directory to the network path and folder, sort of like:

.Directory = "\\OurShare\Photos\"

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On May 30, 2014, at 12:32 AM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

This is the add code

' User asked to add a new photo
Dim strPath As String

    ' If you want to use the Office FileDialog object,
    ' comment out the following code and remove the
    ' comments from the block below
' ***** Begin ComDlg code
    ' Establish a new ComDlg object
    With New ComDlg
        ' Don't allow multiple files
        .AllowMultiSelect = False
        ' Set the title of the dialog
        .DialogTitle = "Locate note PDF File"
        ' Set the default directory
        .Directory = CurrentProject.Path & "\Pictures\"
        ' .. and file extension
        .Extension = "pdf"
        ' .. but show all graphics files just in case
        '.Filter = "Image Files (.bmp, .jpg, .gif, .pdf)|*.bmp;*.jpg;*.gif;*.pdf"
        .Filter = "PDF Files (.pdf)|*.pdf"
        ' Tell the common dialog that the file and path must exist
        .ExistFlags = FileMustExist + PathMustExist
        If .ShowOpen Then
            strPath = .FileName
        Else
            Exit Sub
        End If
    End With
' ***** End ComDlg code

        ' Set an error trap
        On Error Resume Next
        ' Set the image
        Me.imgPDF.SourceDoc = strPath
        Me.imgPDF.Action = acOLECreateLink
        ' Make sure that "took" OK
        If Err = 0 Then
            ' Got a good file selection ...
            ' See if the photo is in a subpath of this project
           ' If Left(strPath, Len(CurrentProject.Path)) = CurrentProject.Path Then
                ' Strip it off and store a relative path
            '    strPath = Mid(strPath, Len(CurrentProject.Path) + 2)
            'End If
            ' Set the path in the record
            Me.txtPhoto = strPath
            ' Hide the message
            Me.lblMsg.Visible = False
            ' and reveal the new photo
            Me.imgPDF.Visible = True
        Else
            ' OOOps.
            ' Clear photo
            Me.txtPhoto = Null
            ' Hide the frame
            Me.imgPDF.Visible = False
            ' Clear the image
            Me.imgPDF.Picture = ""
            ' Set the message
            Me.lblMsg.Caption = "Failed to load the picture you selected. Click Add to try again."
            ' Make it visible
            Me.lblMsg.Visible = True
        End If
    ' Put focus in a safe place
    Me.txtNoteDate.SetFocus

End Sub

I stepped through it and drops right to the Else statement. I Changed the name of the control from imgPhoto to imgPDF.

With Warm Regards,

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265 Ext. 130

Fax (605)964-1070

alorenzini@crhanetwork.org

"Only those who will risk going too far can possibly find out how far one can go."

On , Art Lorenzini <dbalorenzini@yahoo.com> wrote:

I have made the changes but for some reason it is not saving the path so I have to locate the PDF file each time I open the record.

With Warm Regards,

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265 Ext. 130

Fax (605)964-1070

alorenzini@crhanetwork.org

"Only those who will risk going too far can possibly find out how far one can go."

On Thursday, May 29, 2014 3:48 PM, "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Art - replace the image control with an OLEBound control. Keep the same name so you have less work to do.

Change this line where ever it exists:

Me.imgMember.Picture = strPath

to this:

Me.OLEBound1.SourceDoc = strPath

Me.OLEBound1.Action = acOLECreateLink

Also, if you are using a common dialog to get the path, change the filter/extension to PDF

With New ComDlg

' Don't allow multiple files

.AllowMultiSelect = False

' Set the title of the dialog

.DialogTitle = "Locate Member picture File"

' Set the default directory

.Directory = CurrentProject.Path & "\Pictures\"

' .. and file extension

.Extension = "pdf"

' .. but show all PDF files just in case

.Filter = "PDF Files (.pdf)|*.pdf"

' Tell the common dialog that the file and path must exist

.ExistFlags = FileMustExist + PathMustExist

If .ShowOpen Then

strPath = .FileName

Else

Exit Sub

End If

End With

Regards,
Bill Mosca

From: "Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, May 29, 2014 11:06:31 AM
Subject: Re: [MS_AccessPros] Re: Working with PDFS in Access 2013

Bill,
I am sorry but I don't even know where to begin with that. I have attached a screen shot of what it looks like right now. Like this is based off images and not PDF files.

With Warm Regards,

Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
alorenzini@crhanetwork.org
"Only those who will risk going too far can possibly find out how far one can go."

On Thursday, May 29, 2014 12:40 PM, "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Art
Use a bound OLE control.

Private Sub Command1_Click()
Me.OLEBound4.SourceDoc = "C:\MyFolder\MyPDFfile.pdf"
Me.OLEBound4.Action = acOLECreateLink

End Sub

-Bill

[Non-text portions of this message have been removed]

---

Jeffrey Park Jones
Excel, Access, Word, Office Expert
Excel and Access, LLC®
http://ExcelAndAccess.Com

919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net

__._,_.___

Posted by: jpjones23@centurylink.net

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (16)

Visit Your Group