Hyperlink with Access 97

Mike Gilbert

Internet features abound in the latest release of Microsoft’s flagship consumer product, Microsoft Office. Contributing Editor Mike Gilbert looks at one piece of the Access Internet puzzle: hyperlinks.

Unless you’ve been living under a rock lately, you’ve probably noticed how heavily Microsoft is pushing Internet features in its products. Specifically, the folks in Redmond have added functionality to a number of their products-including SQL Server, Internet Information Server (IIS), and, of course, Access-to tie databases to the Web. On top of database publishing (which Michael Kaplan and Tamra Myers covered last month in quite some detail), Microsoft has beefed up its end-user products with browser support. In addition to Internet Explorer, Microsoft’s stand-alone browser product, the company has added limited hyperlinking and browsing support to the Office 97 programs, including Access, Excel, and Word. In this article, I’ll explain how you can use the hyperlink features in your Access applications.

What are hyperlinks?

This might seem like a silly question, but in order to understand how Access stores and uses hyperlinks you need to know a little about what they are and how they’re formed. Hyperlinks allow you to navigate to other documents both on and off the Web using Universal Resource Locators (URLs). URLs support Web addresses (like the ever popular http://www.microsoft.com) as well as file locations on your hard disk. Normally you activate, or follow, a hyperlink by clicking on its text. When a browser application follows a link, it locates the document at the given URL and processes it. Most standalone browsers like Internet Explorer use HyperText Markup Language (HTML) to display formatted text and graphics. Office 97 applications have special provisions for navigating to documents created by other Office apps, launching the appropriate program, and displaying the file.

A hyperlink consists of a number of components. In addition to the URL (sometimes called the address), you can specify display text that Access displays to the user. When you supply display text, Access hides the URL and only uses it to follow the hyperlink when a user clicks on it. A hyperlink can also include what’s known as a subaddress, a particular location in a document specified by the URL. Table 1 lists the different types of hyperlink addresses and subaddresses supported by Access.

Table 1. Types of hyperlink addresses and subaddresses.

Access stores hyperlinks as plain text with each component separated from the others by pound symbols (#). The general format is DisplayText#Address#Subaddress. The only required component is the address (and this isn’t always required-see the section titled “Using Hyperlinks for Intra-database Navigation” later in this article). If you include nothing else, Access displays just the address. If you include display text, Access shows that instead. Table 2 lists some hyperlink examples, including the text displayed to the user.

Table 2. Examples of hyperlinks.

Note that a subaddress isn’t the same as a document nested in a subdirectory on a Web site. In the third example in Table 2, the address http://www.microsoft.com/accdev points to a document in the accdev directory at www.microsoft.com. Including accdev as the subaddress would force the browser to search for a named area called “accdev” in the document at www.microsoft.com.

Where to find the links

You’ll find hyperlinks in Access in two places. First, Microsoft has added a special Hyperlink data type to Jet tables so you can store hyperlinks in a table just like normal text. The difference, of course, is that when Access displays the hyperlink you can click on it with your mouse to follow the link. You can add a hyperlink to the table by typing it directly (using the syntax I just described) or by using the Insert Hyperlink dialog box shown in Figure 1.

Figure 1. The Insert Hyperlink dialog box lets you build hyperlinks interactively.

In the first field you can enter a URL directly, select one from your browser’s history list in the drop-down list, or use the Browse button to choose an Office document on your hard drive, a network share, or an Internet File Transfer Protocol (FTP) site. In the second field you can enter a subaddress or, if you’re linking to an Access database, use the Browse button to select an object from the database. (For the true hackers among you, Jet stores hyperlink data internally as a Memo field with a special bit mask [dbHyperlinkField, value 32768] on the field’s Attributes property.)

You can insert a hyperlink (as a label) on a form or report just like you can insert one into a table. You use the same Insert Hyperlink dialog box. Figure 2 shows a form after inserting a hyperlink to Microsoft’s home page.

Figure 2. A form hyperlink to Microsoft’s home page.

Unlike hyperlinks stored in tables, however, hyperlinks on forms and reports don’t embed all the information in one string. You can see from the property sheet in Figure 2 that the hyperlink’s display text is given by the Caption property and that the label has a separate Hyperlink SubAddress property for that component. Because you can’t set a hyperlink’s display text directly using the Insert Hyperlink dialog, you’ll need to edit the Caption property after inserting it. Figure 3 shows the hyperlink after changing the caption of the label.

Figure 3. Changing the Caption property changes the hyperlink display text.

Two other controls-command buttons and images-can contain hyperlinks in addition to labels. You can’t insert them directly, however, the way you can with a label. You must first create a command button or image control and then set the hyperlink properties using the property sheet. Both properties have builders, though, that use the Insert Hyperlink dialog box, so help constructing the property values is just a mouse click away.

Using hyperlinks for intra-database navigation

While you might not need to link your workgroup’s reporting system to the World Wide Web, one of the coolest uses for hyperlinks is navigating within a database. Using just the subaddress portion of a hyperlink, you can jump from one database form to another. Follow these steps:

Create two forms and save them as Form1 and Form2.

Open Form1 in design view and insert a command button on the form.

Set the command button’s Hyperlink SubAddress property to “Form2”. (Access will update this to “Form Form2”, the correct syntax for a form subaddress.)

Open Form1 in form view and click the button.

When you click the button (notice that your cursor changes to the hyperlink hand), Access opens Form2! This is a great technique for creating menu forms or any form that needs to provide access to another. You can also navigate between databases by including the database path in the Hyperlink Address property. Access will open another instance of itself, load the database, and open the specified form. Combining this technique with another new feature of Access 97, lightweight forms, you can create user interfaces that perform extremely well. (Lightweight forms are forms with no VBA class module associated with them.) Because Access doesn’t need to involve VBA when opening the form, it opens comparatively faster than a form with VBA code behind it. Hyperlinks don’t need VBA and thus are great for use with lightweight forms. The good news is that all Access forms start out as lightweight. Access adds a class module only if you try to insert VBA code. (You can make a “heavyweight” form lightweight by setting its HasModule property to No in design view. However, this destroys any existing VBA code in the form’s module.)

While using hyperlinks for database navigation is cool, it does have its drawbacks. First, Access displays the text of the command button like other hyperlinks, using colored, underlined text. After following a hyperlink the text changes color. You can control the colors Access uses by setting properties on the General tab of the Options dialog box. You can also override the formatting by changing the default format (removing the underline and setting the text color to black, for example). Another noticeable difference is that Access changes the mouse cursor to the hyperlink hand when you move it over the control. You can’t change this easily but it’s a small price to pay for simple, no-code navigation.

Controlling hyperlinks with VBA

Because this newsletter is devoted to Access developers, I should include at least some VBA code! Microsoft added a number of objects, properties, and methods to help you work with hyperlinks programmatically. You’ve already seen the HyperlinkAddress and HyperlinkSubAddress properties of labels, text boxes, and image controls. You can manipulate these in code just like any other property. For example, the following line of code sets a command button’s HyperlinkAddress property to point to the Access developer’s Web site at Microsoft:

Me!cmdFindMS.HyperlinkAddress = _
 "http://www.microsoft.com/accdev"

You can also deal with hyperlinks as objects themselves. Controls have a Hyperlink property which returns a reference to a Hyperlink object. The Hyperlink object, in turn, has Address and SubAddress properties. The following code prints the hyperlink information for a command button to the debug window:

With Me!cmdFindMS.Hyperlink
    Debug.Print .Address
    Debug.Print .SubAddress
End With

Furthermore, Hyperlink objects have two methods-AddToFavorites and Follow. As you can probably guess, the AddToFavorites method adds the hyperlink to your browser’s list of favorite sites. The Follow method links directly to the specified site.

There are two other functions you can use to manipulate hyperlinks stored in Access tables. HyperlinkPart accepts a complete hyperlink and returns a specified part of it, such as the display text or subaddress. FollowHyperlink accepts hyperlink components and tries to connect to the Web site or document.

As an example, in the following procedure, the code searches for a person in a table of contacts and then links to his or her site on the World Wide Web:

Sub LinkToContact(strName As String)
    Dim db As Database
    Dim rst As Recordset
    Dim strAddress As String
    Dim strstubAddress As String
    
    ' Find record in database
    Set db = CurrentDb()
    Set rst = db.OpenRecordset( _
     "SELECT * FROM tblContacts WHERE " & _
     "Name = '" & strName & "'", dbOpenSnapshot)
     
    ' If record was found, link to site
    If Not rst.EOF Then
        strAddress = HyperlinkPart(rst!Hyperlink, _
         acAddress)
        strstubAddress = HyperlinkPart(_
         rst!Hyperlink, acSubAddress)
        FollowHyperlink strAddress, strstubAddress
    End If
    
    rst.Close
End Sub

Summary

While it seems as if the Internet is everywhere these days, that doesn’t mean there aren’t good uses for the technology. Even if you haven’t caught “Internet fever,” you can use the hyperlink features of Access 97 to create fast and lightweight navigation tools for your users. When you finally are bitten by the bug, you’ll be ready to use what you’ve learned to hit the information superhighway at full speed. s

Mike Gilbert is a contributing editor to Smart Access and a senior consultant with MCW Technologies, a national firm specializing in application development using Microsoft technology. This article was adapted with permission from Access 97 Developer’s Handbook by Paul Litwin, Ken Getz, and Mike Gilbert (Sybex). mtgilbert@compuserve.com, http://www.mcwtech.com.

To find out more about Smart Access and Pinnacle Publishing, visit their website at:

http://www.pinpub.com/access/

Note: This is not a Microsoft Corporation website.
Microsoft is not responsible for its content.

This article is reproduced from the December 1996 issue of Smart Access. Copyright 1996, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call (800)788-1900 or (206)251-1900.