The difficult I do immediately, the impossible takes a little bit longer.
Tips (Main)
Naming Conventions
Where are the Options?
Options for Current Database
Object Designers
Trusted Locations
Repair (or Remove) MISSING or Broken References
Export Specifications
Import Specifications
Navigation Pane Options
Setting up a Model Database
Default Buttons and Legend
Set Subdatasheet to [NONE]
Creating a Settings Table
Not_In_List Event
Database Design Tips
Handling Attachments...

Sooner or later you are going to want (or need) to add *attachments* to your database.  While the Attachment Data Type may seem tempting it's not a good idea if you plan to have a lot of files (documents) and images.  Access has a 2 gig limit and you will find your file quickly grow should you start adding images and files.  And let's not forget the fact that you cannot upsize the Attachment Data Type to SQL Server.  Instead, best practice is to *link* (file path of document or image) in the database to the file stored in a location available to all Users.

I set up a separate to handle links (tblLinks), as it's easier to update should something change...
And then your Form (or Forms) depending on how many menus you want your Users to be able to link files from, i.e.
Single Form
Continuous Form
Now we can start to add the code behind those buttons.  (Note, for the Navigation Buttons click here.
Private Sub cmdOpenFile_Click()
    Dim strFile As String
    strFile = Nz(Me.txtPath, "")
    If IsNull(Me.txtPath) Or Me.txtPath = "" Then
        'If the File Path is empty
        MsgBox "No Link.", vbInformation + vbOKOnly, "Link"
    Exit Sub
    End If
    If Dir(strFile) = "" And Not IsNull(Me.txtPath) Then
        MsgBox "No Link.", vbInformation + vbOKOnly, "Link"
        Call GoHyperlink(Me.txtPath)
    End If
End Sub
When creating the Form(s) don't forget to add the hidden field(s) indicated in the Figure to the right. Note, you can leave off the optional fields.
This button does double duty, it can either replace an existing file or you can go to new and select a file.  You might be thinking why not just limit it to selecting a new file?  Well, sometimes the file changes and you really don't want to add a new one you just want to update the existing link.  For example, the file was .DOC but it's been upgraded and now it a .DOCX.  You don't want to leave the .DOC so you just Browse to the updated document and select it.

Notice the CopyFile line where the code tags are?  This line copies the file to the indicated folder.  While this line is optional it really is a good practice to move the files to a central location where they won't accidently get deleted.  If you do decide to do this then directly after the CopyFile line insert...

Me.txtPath = "C:\adBEs\links\" & GetFilenameFromPath(Me.txtPath)
Private Sub cmdAddNew_Click()
On Error GoTo SmartFormError
    Dim pubProjectID As Long
    pubProjectID = Me.txtProjectID
        DoCmd.RunCommand acCmdRecordsGoToNew
        Me.txtProjectID = pubProjectID
    Exit Sub
    If Err = 2046 Or Err = 2501 Then
        Resume Next
        MsgBox Err.Description
        Resume Exit_SmartFormError
    End If
End Sub
Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function
You will notice I use GoHyperlink() by Allen Browne (former Microsoft® Access MVP) instead of Application.FollowHyperlink.  Click the code tags above and read the Why a replacement? section to see why.  However, you CAN use the built-in Application.FollowHyperlink if you prefer.
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.