Navigate Access Forms with Faux Hyperlinks

January 16, 2004

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

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:

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.