How to Open Filename linked to CustomerID
You can open the external file like MS Word or PDF format from MS Access. In this How To, I will demonstrate how to open the external PDF file that has a filename matches with the customer ID in MS Access. For instance, I have the PDF file name “3.PDF” located in some folder on my computer for match with the CustomerID = 3 on Customer table. After I click the Customer_ID =3 on the Customer_ID field on form, the Adobe Reader will open file 3.PDF.
Before I start I think about how to handle function if there is no such filename in the folder. You can google it to find any function to check if file exists or have file in the folder. I found one function names function FileExists to test if file exists or not. This function will return True if there is a filename in the specified folder, if not return False.
Step#1 Place the FileExists() function on the Module of your Access file. Full code can be download here:
Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean ........................ ........................ End Function
Step#2 Create a function to open file in the Module as well. Place a function OpenFile() below in the Module.
Function OpenFile(ByVal CustomerId As String) As String Dim FileName As String Dim strFilePath As Variant FileName = CustomerId ‘Open PDF file from the Downloads folder on my computer strFilePath = "C:\Users\tewan\Downloads\" & FileName & ".PDF" ....................... ....................... End Function
Step#3 I will use my Search Customer Form to open the external file linked to customer ID. If your form is locked or the Customer_ID field is locked or disabled, please change it to unlocked or enabled in order to click on this field. Call OpenFile() function on the On Click Event of Customer_ID field as shown in the picture below:
Private Sub Customer_ID_Click()
Step#4 Prepare your PDF file format with name matching the Customer_ID on Customer Table. In this example, I name file as “3.pdf” for Customer_ID = 3.
Step#5 Click on Customer_ID = 3 on the search form then file 3.pdf will open as shown below:
Step#6 Implement the Search button to include a field Customer_ID in the keyword search. By doing this, the user can search for Customer_ID and CustomerName on one search. Then the user can click on the Customer_ID field to open a PDF file matching with Customer_ID from searching.
We need to update some codes on the Search Button by adding code to test if the input is a numeric or not. If it is a number then used it to Search for Customer_ID.
Dim intSearch As Long
If IsNumeric(Me.txtSearch) Then
intSearch = Me.txtSearch.Value
Also need to add “ Or (Customer_id = ” & intSearch & )” after “AND (CustomerName like “”*” & strSearch & “*””)”
A completed coding for the VBA of SQL for Task string will be:
Private Sub Command163_Click() On Error GoTo ErrHandler: Dim strsearch As String Dim Task As String Dim strProgram As String Dim Individual As Integer Dim Government As Integer Dim Business As Integer Dim NonProfit As Integer If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed" Me.txtSearch.BackColor = vbYellow Me.txtSearch.SetFocus Else ................ 'FULL CODE ON DOWNLOAD ACCESS FILE ................ Exit_ErrHandler: Resume Next End If ErrHandler: Exit Sub End Sub
Note: If you don’t know how to create a search form, I encourage you to watch my How TO and video from link below:
Step#7 Format the Customer_ID field by changing the font color to Blue and Underline to make it link hyperlink with underline on text.
Under the form view, enter 3 in a search box then click Search. There is only one customer record with a Customer_ID = 3 as shown below. After you click on 3 the 3.pdf file will open.