With an Access database, there are many ways to perform similar functions like creating a clickable button using the standard CommandButton or using hyperlinks in Access Database instead – that’s a URL link and having some quick automated way to fire up an email for example.
This blog expands on this feature and in particular how you can use a hyperlink to act as an alternative button for your datasheet or form objects in your database without even using a single line of programming code albeit a macro or some Access VBA code.
Working With Hyperlinks In Access – Create A Button Link
Rather than a conventional CommandButton (a grey dull looking control) to click and run a procedure, we can adopt the hyperlink action instead and break with tradition here so that when a user clicks this link it can open a form, run a report or with some coded procedure (macro or VBA code), run a more complex routine.
Here’s a simple example to run a report (in preview) for a selected order displayed in the datasheet view (via a form object).
1. Have your data prepared whether via a query or table and displayed in the datasheet view via the form which in my example uses a query to list ‘UK’ orders raised during the first quarter of 2009.
Even though your form can be based on a table, we really need to use a query because the trick here will be to create a new field which will be clickable hyperlinks in Access Database and this application does not allow calculations normally via the table (though version 2010 now does!)
2. With the saved query, create a list or tabular form view but display it as the datasheet view as shown:
Temporarily save your form and we will return to this later on.
3. Next, let’s create or choose the report that will be opened when we eventually click the associated hyperlink.
In this example, I want to open the associated report for the selected order therefore even though I said no programming code is required to open any object in Access, this example will however require some macro or VBA code (I’m going to provide this for you – don’t worry!) but the principle is the same without any programming code.
Here’s the design of the order report:
(How I designed this report is not explained here).
4. Now to display a hyperlink via the datasheet is not so obvious for this type of output as this view is limited when using the rich formatting options available and placing a normal hyperlink as a clickable Label or CommandButton will not appear unless it’s actually part of the query (as a calculated field).
We need to revisit the design view of the actual query and add a new calculated field called ‘View Order‘ which will have the value ‘Open…‘ that will open and run a report.
In design of the query, I’m going to add the following expression into a new field:
View Order: "Open..."
There’s no other action needed here just the text for the hyperlink being displayed alongside each record.
Close and save the query.
5. Back in the design of your datasheet view form, add the new field which is based on the newly saved query and re-arrange this new field so it’s at the beginning (first field) as shown:
6. In the design view, let’s convert this to a hyperlink and I’ll then provide the code too. Modify the new added field’s property so that the Hyperlink is set to ‘Yes‘ which displays the look and feel of a hyperlink (defaulting to a blue underlined format).
Now in the ‘Event‘ tab for the same field, click and create an [Event Procedure] and add the following VBA code:
Here I open a report called ‘rpt_OrderH’ and pass the matching ID (Order ID) between the unique ID from the form and the loading report which it filters by.
Of course, you need to adjust the names illustrated above with your own version but whether you want to add a standard hyperlink or an added coded procedure, the process is the same.
Now my hyperlinks in Access works well as finally shown below:
Want to learn more? My eBooks may be a good place to start!