Navigate Access Forms with Faux Hyperlinks

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.

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.

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles