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.
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.
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:
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.