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:
-
Label size and location
-
Label fore color and underline
-
Label OnClick event
4. In
the OnMouseMove event for each label, run a routine that updates the display:
-
Label font color
-
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.
IE 7
Firefox 2.0
