Preview Documents in Microsoft Access Form

Project Scope

We recently added a document center to a project which allows users to attach documents such as pdf, word, txt and images to a project.  In addition they will have an automatic process set up to read incoming emails and save the email attachments to a network drive.  The goal is to allow the user to view new documents that have been received and save them to the correct location.  Of course we will manage the process to extract information such as subject and who the document came from to allow the user to understand the general information of the document and this will be saved to a table.  In addition the user needs to be able to view the document to read its contents.

Solution

We can of course launch other applications from access to view word docs in Word and pdf in Adobe Acrobat etc.  However when a user only has one screen to work with this means switching from one application to another and if there are multiple document types then of course this means opening multiple applications.  We therefore provided a simple solution that would launch the document in a new access form with tab layout allowing the user to easily manage the system.  In the example below I am using code that will read the file from a given path, which the user will need access to on their computer.  Alternatively, if the document is saved to a SQL table, we can modify the code to temporarily save the document to a local drive and launch the file from the temp location.  If you use this method you would also want to include additional code to delete the temp file after viewing/closing the form.

Sample Form

The sample form provided should be launched passing the document path in the OpenArgs

DoCmd.OpenForm "frmDocumentReview", OpenArgs:= Me.DocumentPath

The on load code verifies that the path was passed and also if the file exists.  If all is good the process continues.

Private Sub Form_Load()

    If IsNull(Me.OpenArgs) Then
        MsgBox "No document to show", , "Path not specified"
        Exit Sub
    Else
        If Not FSO.FileExists(Me.OpenArgs) Then
            MsgBox "Your computer does not have access to the named file. You need to download the file to view it."
            Exit Sub
        Else
            Showdocument OpenArgs
        End If
    End If
    
End Sub

Finally the code loads the document.  The Showdocument sub takes care of the different document types. Most document types use the else case. The form itself has a hidden text box called txtPath which stores the value of the OpenArgs (File name and path) this can be either a mapped drive or UNC. The web browser object stores =[txtPath] as the control source. However word documents do not work the same, and you first need to save the document as html. I used a method originally written by Crystal Long and made some minor changes.

Private Sub Showdocument(strPath As String)
    Dim strExt As String
    Dim sPathFileHTML As String
    Dim oWordApp As Object
    Dim oDoc As Object
    
    strExt = GetFileExt(strPath)
    Select Case strExt
        Case "docx"
            'save word doc as HTML
            sPathFileHTML = CurrentProject.Path & "\temp.html"
            Set oWordApp = CreateObject("Word.Application")
            Set oDoc = oWordApp.Documents.Open(strPath)
            oDoc.SaveAs2 FileName:=sPathFileHTML, FileFormat:=8 'wdFormatHTML
            oDoc.Close False
            oWordApp.Quit
            Set oDoc = Nothing
            Set oWordApp = Nothing
        Case Else
            sPathFileHTML = strPath
    End Select
    
    'open in browser control
    Me.txtPath.Value = sPathFileHTML

End Sub