How to Open External File linked to CustomerID

TGoldenEye

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:

downloadbutton

 

 

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:

searchForm

 

 

 

 

 

 

onClick

It is advised to combine these medications with effective exercises, healthy diets and stress-controlling therapies for better and quicker results. canadian viagra store A young lady couldn’t get cute-n-tiny.com tadalafil 80mg pregnant after a year of trying to do so. Where Can We Detoxify? During basic, simple free tadalafil sample detoxification programs, most of us can maintain our normal daily routine. Kamagra is a wonderful love medicine and it cheap levitra uk has to be taken once in twenty four hours.

 Private Sub Customer_ID_Click()

Call OpenFile(Me.Customer_ID)

End Sub

 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.

filename

 

 

 

 

 

 

 

Step#5 Click on Customer_ID = 3 on the search form then file 3.pdf will open as shown below:

Search

 

 

 

 

 

result3

 

 

 

 

 

 

 

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

End If

Also need to add “ Or (Customer_id = ” & intSearch & )” after “AND (CustomerName like “”*” & strSearch & “*””)”

editSearch

 

 

 

 

 

 

 

 

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:

  1. How to Create Search Form Part 1 or Video part 1
  2. How to Create Search Form Part 2 or Video part 2 

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.

Underline

 

 

 

 

 

 

 

 

 

Completion

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.

downloadbutton

 

search3

Related posts