Navigate Access Forms with Faux Hyperlinks

Friday Jan 16th 2004 by Danny Lesandrini

Danny Lesandrini examines a way to simulate web browser type hyperlinks on Access Windows forms.

I started this article because I felt like I'd stumbled on a clever way to simulate web browser type hyperlinks on my Access Windows forms and I wanted to share that with readers of Database Journal. However, as is always the case, I began to question my own practice, looking for ways to make it more efficient and extensible. I found one, but in the end, I decided that I liked my original solution better. I'll be curious to see what you think, especially if you have an alternate solution.

Below is an image of the main form in the download for this article. It demonstrates two different methods for exposing links that approximate hyperlinks in an Access form, methods that I refer to as Simple and Complex.

  • Simple Solution: Bind single control to table field, setting its IsHyperlink property to True.
  • Complex Solution: Create an array of labels, dynamically modifying their properties so they behave like hyperlinks.
Click for larger image
Faux Hyperlink Example

The action behind the Click event can be anything you want.  In one of my applications, the links point to a table record that stores filter criteria for an imbedded datasheet.  Clicking the link loads the criteria and requeries the datasheet, displaying, for example, the daily report for a given sales rep.  The example above, that comes with the sample code, opens a form that contains the full detail for the given record.  Below, there is an example of how to use this method to allow users to delete a record.  The possibilities are endless.

Simple Solution

I included this solution because I was certain that some reader would write me, saying that Access exposes an IsHyperlink property for controls, making my entire solution irrelevant.  I played with it and here is what I came up with.

Private Sub Form_Load()
On Error Resume Next

   Dim iTotal As Integer
   Dim sMsg As String

   iTotal = DCount("*", "tblItems")
   sMsg = "Total of " & iTotal & "item detail records found."

   Me.lnkItemDetail0.Caption = sMsg

End Sub

Private Sub txtItemDescription_Click()
On Error Resume Next

   Dim strItem As String
   Dim lngItemID As Long

   strItem = Me.txtItemDescription
   lngItemID = Me.ItemID

   DoCmd.OpenForm "frmItem", , , "[ItemID]=" & lngItemID
   DoCmd.MoveSize 100, 6800

End Sub

Now, that was easy, wasn't it.  The advantage of this method is that you can have unlimited links, since the single control is repeated once for each detail record, no matter how many records there are.  You will notice in the screen shot above that the Simple Navigation example indicates 236 item detail records, while the Complex Navigation example reveals only 130 records.  This is due to a limitation of the size of the form, as we will see when we examine the process for the Complex Navigation example.

So, the IsHyperlink trick has two advantages:  it is easy to code and allows for the display of unlimited records.  Why, then, do I still prefer the other method?  Because it allows the developer greater control of the visual appearance of the links and because, in general, hyperlinks are the sort of UI feature that lend themselves to fewer items per page, usually less than 25 or so.  When is the last time you saw a web page with 236 links?

One additional trick was required to make this solution mimic the complex one.  Getting line numbers to display is not a trivial task.  I created a global, public function named GetNextNumber() which returns the ordinal position of the given record.  This is calculated in the query behind the subform and bound to an additional control.

Complex Solution

The code for the complex solution is too verbose to reproduce here, but you can browse it by downloading the code for this article. Basically, the idea is this:

1.      Create an array of labels, naming them lnkItem1, lnkItem2, etc
(maximum of 130 per form, per column created)

2.      Set the OnMouseMove event to =ActivateLink(1), = ActivateLink(2), etc.

3.      In the OnCurrent event for each new parent record, run a routine that resets the following properties:

  1. Label size and location
  2. Label fore color and underline
  3. Label OnClick event

4.      In the OnMouseMove event for each label, run a routine that updates the display:

  1. Label font color
  2. Label underline

This solution is limited to 130 controls because the max height for the detail section of a form is 22 inches.  If you use an 8 point font for the labels and set their heights to 0.1667 inches, you can fit 130 link labels and one header label.  If you use a smaller font or simply scrunch the height, you can fit more, but regardless, there is a limit.  If your UI lends itself to the practice, you could load them in two or more columns, which would double or triple this number, but at this point we are defeating the purpose:  a simple UI.

The code for both events uses the process of looping through all the form controls and manipulating their properties.  I used similar code to assign names, which is included in the download in a module named bas_Utility.

When to use this solution

Well, that is how you do it, but you may still be wondering where to use it.  Below are a couple of screen shots of applications I have created recently where we implemented this idea.  The first implementation was intended to simplify a very complex form.  The Project Info form already has too many subforms on too many tabs.  (Please excuse me for this  egregious violation of good UI design.)  The hyperlinks in the lower right side of the Project Investigators tab replaced a subform with its own nested subform to the Site Notes table. 

The real estate allotted for the hyperlinks allows for a dozen Site Note links and a thirteenth link that opens All Site Notes for this project.  Users like this method, because they can more quickly see the history of their notes, even without opening up the notes form.  If, however, they want to see the detail, edit the note or add a new one, they can do so with a single click.  This was the most widely accepted new UI feature ever deployed for this application.  The users love it.

Click for larger image
Pharmatech Launch Note Example

The next implementation has not yet been rolled out to users because of the record count limitation.  This order entry system can, and does on occasion, have hundreds of order detail items for each order, which tends to make this subform grow uncontrollably.  In addition, users are accustomed to deleting line items from this subform, so I had to add an array of buttons, which virtually doubles the processing for each new order record loaded. 

Nevertheless, again, you can see the advantage of this method.  Where there are around 20 detail items (which is the most common situation) the interface allows the users to quickly see the entire list at a glance.  As a subform of datasheet records, much more screen real estate is required and scrolling was required left and right as well, adding an addition scrollbar.  The code for this solution is not included in the download, but it can be easily inferred from what is there.

Click for larger image
TruStile Open Order Detail Example


What's The Verdict?

I have to admit that this article is totally selfish for me. I really got a kick out of this idea and just wanted to share it with someone.  The few clients who are using the idea all seem to like it, and with things going toward the Internet more and more these days, people are not only comfortable with UI features like hyperlinks, they practically demand them.

As usual, my email is published above.  Drop me a line to let me know what you think, and educate me if you think I missed something with regard to a simpler implementation.  I am also curious to know if you think it is something you would ever put in a form of yours and how you think your clients would react to faux hyperlinks in an Access form.

» See All Articles by Columnist Danny J. Lesandrini

Mobile Site | Full Site