Open file link or web link from Access
(Download Sample Access File)
All your documents or websites can be opened from MS Access. We can collect all addresses of those files or web address into Access database. Then we can search for the name or description of file or website and click to open those files or website from Access. The steps below will demonstrate how to create such Access database program.
Step #1 Create 2 tables names it as LinkList and LinkType. The LinkType table contains LTypeID field and LinkType field. The LinkList table contains fields: LinkID, LinkName, LinkDescription, LinkAddress, LActive and LinkType (look up from LinkType table shown below in the Relationships diagram).
Stop #2 Create Main form to show as a first page when Access opened. Insert 2 comboboxes for selecting My Doc Links and Other Links on the form. Also insert 3 buttons to open three different forms to add/edit LinkType, LinkName and search for file and website. Insert a label “Welcome to.. Link Database” on the top of form.
Step #3 under the AfterUpdate() Event for combobox of both My Doc Links and Other Links, insert a function “fHandleFile()” to open file or website after a name is selected from the dropdown list of combo box.
Private Sub Combo29_AfterUpdate()
Dim FilePath2 As String
FilePath2 = DLookup(“LinkAddress”, “LinkList”, “LinkID = ” & Me.Combo29)
Call fHandleFile(FilePath2, 3)
End Sub
Step #4 under the Search button on the Search form, update a VBA code to search a keyword in the LinkName and LinkDescription fields from LinkList table.
Note: see another How To for Create a Search Form here.
Private Sub Command163_Click()
‘Check if search parameters exist
Dim strSearch As String
Dim strLoad As String
Dim strSpaceFix As String
Dim Task As String
Me.txtTemp = txtSearch
‘Clear recordsource before seach
myTask = “SELECT * FROM linklist WHERE [linkID] =”””
Me.RecordSource = Task
If IsNull(Me.txtSearch) Or Me.txtSearch = “” Then
MsgBox “Please enter keyword for linkName or Description.”, vbOKOnly, “Keyword Needed”
Me.txtSearch.BackColor = vbYellow
Me.txtSearch.SetFocus
Else
‘Load Text Box contents
strLoad = Me.txtSearch.Value
‘strSearchValue = Me.txtSearch
‘Replace spaces with addition search code
strSpaceFix = Replace(strLoad, ” “, “” & “*”” AND [linkname] Like “”*” & “”, 1, -1)
Task = “SELECT * FROM linklist WHERE ([linkname] Like “”*” & strSpaceFix & “*”” Or [LinkDescription] like “”*” & strSpaceFix & “*””)” & _
“order by linkname, linktype”
Me.RecordSource = Task
Me.total = FindRecordCount(Task)
If Me.total = 0 Then
MsgBox “No record found”, vbInformation, “Not found”
End If
Me.txtSearch.BackColor = vbWhite
Me.Text89.Requery
End If
End Sub
Step #5 Call a function fHandleFile() VBA code on the On Click Event under the LinkName field. By doing this, you can click on a LinkName to open file or website from a result of searching keyword.
Private Sub LinkName_Click()
Dim FilePath As String
On Error GoTo err_Handler
If IsNull([LinkAddress]) Then
MsgBox "No Link Address entered for this Document or web page"
ElseIf IsNull([LinkID]) Then
Else
FilePath = DLookup("linkAddress", "linklist", "LinkID = " & Me.LinkID)
'MsgBox (FilePath)
Call fHandleFile(FilePath, 3)
End If
Exit Sub
'**************************************
'* Error Handler
'**************************************
err_Handler:
MsgBox "Error " & Err.number & " occured." & vbNewLine & "Please search reference before clicking this field."
End Sub
Step #6 Insert VBA code under the On Double Click Event for LinkDescription field to open the Add/Edit LinkPage. In this How To, I use an Embedded Macro method to open form as shown below:
This just indicates it comprises of large portions of the general population in this world are experiencing the same issue and in the greater part of situations coming from all this clinical viagra without prescription uk data and consumer feedback offered. It seems like more and more people are suffering from type 2 diabetes, which generic sale viagra is a medical condition or emotional issue or might be both. Another of the top 5 watermelon juice benefits is the protection which it provides against several types of cancer, including prostate cancer, breast cancer, endometrial cancer, and lung cancer, due to the Lycopene viagra cheap generic which it contains. We also developed medicine systems through our knowledge of indigenous herbs and djpaulkom.tv buy viagra generic products of relevance to our health and wellness.
Step #7 Call a function fHandleFile() VBA code on the On Double Click Event under the LinkAddress field. By doing this, you can double click on a LinkAddress field to open file or website from to test if it works or not.
Step #8 Insert the VBA code for function fHandleFile() under the Module of your Access file.
You can find the VBA code from Google or you can download a sample Access file here with a little of donation. You will save a lot of time with a download file.
Function fHandleFile(stFile As String, lShowHow As Long)
‘…………………………..
‘………………………….
‘You can search for this function from Google
‘Or you can download the sample file from this website here
‘…………………………
‘…………………………
End Function
Step #9 Insert the VBA code for function FindRecordCount() under the Module of your Access file. You can find the VBA code from Google or you can download a sample Access file here with a little of donation. You will save a lot of time with a download file. Donation is much appreciated.
Function FindRecordCount(strSQL As String) As Long
Dim db As Database
Dim rstRecords As Recordset
‘On Error GoTo ErrorHandler
Set db = CurrentDb
Set rstRecords = db.OpenRecordset(strSQL)
If rstRecords.EOF Then
FindRecordCount = 0
Else
rstRecords.MoveLast
FindRecordCount = rstRecords.RecordCount
End If
rstRecords.Close
db.Close
Set rstRecords = Nothing
Set db = Nothing
End Function
We will call this function on the Search button. It will give us a message “No record found” if no result after search by keyword. We will add the code below on the Search button after Me.RecordSource = Task.
Me.total = FindRecordCount(Task)
If Me.total = 0 Then
MsgBox “No record found”, vbInformation, “Not found”
End If
==============================
Example of selecting website from the dropdown list on combobox to open the website.
Bestbuy.com website will open in the internet browser after the name of Bestbuy.com was released from the dropdown list in Access.
You can download sample Access file here:
