Open Forms with Hyperlinks in Listbox

📋 Organizing and Opening Forms from a ListBox

In the previous example, we saw how all your Forms can be centrally organized using a ListBox on a Control Screen. By simply double-clicking an item in the list, the corresponding Form is opened.

The behavior is controlled by the Type field in the control table:

• If the Type value is 0, then the specified Form or Report opens directly.

• If the Type value is 1, a macro is executed first to perform a sequence of operations (such as data preparation), and then the related Form opens with the processed data.

🎯 Objective

The goal of the method we’re about to explore is the same as before—opening forms from a centralized control screen—but this time we’ll do it more simply and differently.

It’s always beneficial to learn multiple ways to accomplish a task. This gives you the flexibility to choose the most efficient or appropriate method for each project, rather than depending on a single approach every time.

Continuous Form to look like a List.

For this example, we'll design a simple form that looks and functions like a ListBox, even though it's not actually a ListBox control.

First, create a table with two fields with the following field Types and sizes:

Field Name	Type	Size
Seq	Number	Integer
Desc	Hyperlink

Step 1: Save the Table
Save your new table with the name DataFiles2.

Step 2: Add Sample Records
Open the DataFiles2 table and manually enter a few sample records.

Important Note for Access 2003 Users:
In Microsoft Access 2003, there's a known limitation when trying to enter Hyperlink SubAddress values for database objects (like forms, reports, or macros) directly within a table.

But don't worry — we can work around this issue using a few clever tricks.

---

Understanding How Hyperlink Data Is Stored

In Access, a hyperlink field actually stores data in a four-part format, separated by the # symbol:

less
DisplayText#Address#SubAddress#ScreenTip

• DisplayText – What you see in the table.

• Address – Typically a URL or file path (can be left blank for internal objects).

• SubAddress – The name of the object within the database (form, report, macro, etc.).

• ScreenTip – Text that appears when you hover over the hyperlink.

Example:
To link to a form named frmCustomers:

less
Open Customers Form##frmCustomers#

Here:

• Open Customers Form becomes the display text.

• The Address is blank.

• frmCustomers is the internal SubAddress.

• The ScreenTip is also blank.

Editing a Hyperlink Field in Access 2003

When you right-click on a Hyperlink field in your table, a shortcut menu appears.
Hover over the Hyperlink option, then select Edit Hyperlink.

💡 This is the same dialog box you used earlier via the Hyperlink SubAddress property of a Command Button (as we saw in the Closing Forms example).

However, there’s one key difference when using this dialog from a table field:

🚫 Limitation:

The “Object in this Database” option — which lets you browse and select forms, reports, or macros — is missing in this version of the dialog.
This means you can't browse and select a database object directly to fill the SubAddress.

---

✅ Workaround:

Even though browsing isn’t available, you can still manually enter the hyperlink components using the Edit Hyperlink dialog:

• Leave the Address field blank.

• Enter the name of your form/report/macro in the SubAddress field.

• Optionally, fill in Text to Display and ScreenTip.

Example 1:
To link to a macro named Hlink_Close, enter:

• Text to Display: Close Form

• Address: (leave blank)

• SubAddress: Hlink_Close

• ScreenTip: (optional)

Click OK, and your Hyperlink field will now point to the object even though you couldn’t browse for it.

🔗 Access Hyperlink Field Format (for Internal Objects)

When storing hyperlinks in a Hyperlink field in Microsoft Access (especially in versions like Access 2003), you manually construct the hyperlink string using a specific format:

🧩 Hyperlink Format Structure:

shell
TextToDisplay##ObjectType ObjectName#

• TextToDisplay — The label or caption is shown in the table.

• ## — Two hash symbols separate the display text from the actual hyperlink destination.

• ObjectType ObjectName — Specify the type (Form, Report, Macro, etc.) followed by the exact name of the object, with a space between them.

• # — The final hash symbol ends the hyperlink structure.

---

✅ Example: Linking to a Form Named "Employees"

less
Open Employees Form##Form Employees#

• "Open Employees Form" — Text shown in the table

• ## — Separates display from destination

• "Form Employees" — Opens the form named Employees

✅ Example: Linking to a Macro Named "Hlink_Close"

sql
Close Window##Macro Hlink_Close#

This will run the macro Hlink_Close  When clicked.

---

⚠️ Notes:

• You must manually enter this structure if you're using the Edit Hyperlink dialog, because Access 2003 doesn't let you browse to internal objects from table fields.

• This trick allows direct linking to objects within the same database, even when the dialog limits you.

🔗 Opening External Files with Hyperlink Fields in Access

You can also use Hyperlink fields in Access to open external files, such as Word documents or Excel files, by manually entering their full file path.

✅ Example:

nginx
My Word Document#C:\My Documents\mywordfile.doc#

• My Word Document — Text displayed in the table

• # — Separates display text from the actual hyperlink

• C:\My Documents\mywordfile.doc — Full path to the external file

• The third segment (SubAddress) is not needed for external files. If it’s included, Access will ignore it.

---

✏️ Editing a Hyperlink Field: Tips & Tricks

Editing hyperlink fields in Access can be tricky because clicking the field usually tries to open the link, not edit it. Here are two techniques to safely select the field for editing:

---

🧭 Method 1: Click Carefully Near the Left Edge

1. Move your mouse pointer toward the left edge of the hyperlink field.

2. When the pointer changes to a 3D "+" cross, click once.

3. This will select the field instead of activating the hyperlink.

---

🖱️ Method 2: Use Right-Click + Cancel

1. Right-click on the hyperlink field.

2. This will highlight the field and open a shortcut menu.

3. Then, click outside the menu (anywhere on the screen) to dismiss it.

4. Now you can safely edit the text.

---

These tips allow you to work around Access's default behavior and make precise edits to hyperlink fields without accidentally opening the link.

Enter a few more records in the DataFiles2 Table to open the Objects of your choice by entering their Object Type and Name correctly in the Hyperlink SubAddress segment, and close the Table.

Now we need to design a small form. Click the Table and select Form from the Insert Menu, and select AutoForm: Tabular from the New Form Wizard, and click Ok. A tabular form is created and opened in the data view. Close the form and save it with the Name: DataFiles2. Open the form in Design view to change its properties to look like a Listbox.

1. Click the View menu and remove the checkmark from the Form Header/Footer item. This action will display a warning message. Click Yes to remove the Header and Footer Sections of the Form.
2. Click on the left top corner of the form where the left-side and top scales meet, where a black square shows at the intersection, to select the Form rather than its controls.
3. Press the Alt+Enter keys, or click Properties from the View menu to display the Form's property sheet.
4. Change the following Form Properties:
   • Allow Datasheet View: No
   • Allow Additions: No
   • Allow Deletions: No
   • Data Entry: No
   • Scroll Bars: Neither
   • Record Selectors: No
   • Navigation Buttons: No
   • Dividing Lines: No
   • Border Style: None
   • ControlBox: No
   • Min Max Buttons: None
   • Close Button: No
5. Click on the Detail Section of the form and display the Property Sheet.
6. Change the Back Color Property Value to 128 (will change to dark red color)
7. Select the Seq Field control and change its width property to 0.2'
8. Select the Seq & Desc text boxes together, and select Align -> Left from the Format menu, which will bring the controls close together.
9. Select Shortest from the Format -> Size Option to change both controls' height to the same value.
10. Now change the Properties (still both controls selected) of the controls to the following values:
    • Special Effect: Flat
    • Top: 0
    • Back Color: 128
    • ForeColor: 16777215
11. Resize the form's detail section close to the field sizes using the sizing handles at the right and bottom edges of the form. Change both controls' border Color & Font weight:
    • Border Color: 128
    • Font Weight: Bold
12. Select the Seq field alone and change the following properties too:
    • a. Enabled: No
    • b. Locked: Yes

The completed form in the design view will look like the sample given below:

When opened in Form View, it will look like the sample below:

If you opened it in Form View, select Close from the File menu to close the Form. Now the remaining task is to insert this form into our Main Control Form, and change a few properties of the control to complete the final design, and you are ready to go.

Close all other objects and ensure that the Database window is not minimized; if so, restore it. Now, open the Control Form in Design View. Select Tile Vertically from the Windows Menu. The Database Window and the Form will be arranged side by side. Click on the Forms Tab (if it is not visible), click, drag, and drop the DataFiles2 form on the Control Screen where you want the Menu Options to appear. Resize the control to the desired size. Change its child label Caption to Data Files. Size the child label to the same size as the control, and its Special Effect Property to Etched. Save the Form. The completed design on the Control Screen will look like the sample given below:

Click to Enlarge

Click an Item in the Listbox, and the Hyperlinks open the Object (Form, Table, Macro, etc.) attached to it.

When more links are added to the DataFiles2 Table, they will appear in the list as well.

Click Next for Control Screen Design...

Download Demo Hyperlinks2k.zip