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

Read More

Opening Access Forms

Opening Forms from a Control Screen Using Hyperlink SubAddress

Using the same technique discussed in the Closing Forms section, you can create Command Buttons on a Control Screen and link each one to a specific form by setting its Hyperlink SubAddress property to the Form Name.

This is a quick and straightforward method for opening forms from a central location, especially useful when your application contains multiple forms.

How to do it:

1. Add a Command Button to your Control Screen.

2. Set its Hyperlink SubAddress property to the name of the form you want to open (e.g., frmCustomer).

3. When the user clicks the button, the corresponding form opens.WWWW

💡 Tip: Use descriptive captions on buttons like "Open Customer Form" for clarity.

---

Limitation.

One drawback of this method is screen clutter—as the number of forms increases, the Control Screen may become crowded with too many buttons.

To address this, consider using:

• Option groups

• List boxes or combo boxes to select a form name dynamically

• A navigation form or tab control for better organization

Organizing and Opening Forms from a Control Screen

There are several ways to organize and open forms from a central Control Screen. In this section, we’ll explore an effective method that uses a table to manage the list of forms and their captions.

This approach offers better scalability and keeps your Control Screen clean and organized, especially when your application contains many forms.

To implement this method, you’ll need to create a supporting table first.

Creating a Table.

First of all, create a Table with the following Fields:

SRL	Field Name	Field Type	Field Size
1.	ID	Number	Integer
2.	DESC	Text	35 Characters
3.	FORM	Text	20 Characters
4.	MACRO	Text	20 Characters
5.	TYPE	Number	Integer

And save it with the name: DataFiles

An Image of a Table with sample data filled in is given below:

Step 1: Populate the Forms List Table

Begin by filling the table with a list of your data files (tables) for which you’ve already created forms.

The table should have the following fields:

• ID: Serial Number (e.g., 1, 2, 3…)

• Desc: A short description of the form or related table

• Form: The name of the form linked to the data file

• Type: For now, enter 0 in this field for all records. We will explore its purpose in a later step.

This setup enables us to create a dynamic navigation system on the Control Screen, which retrieves data from this table and opens the corresponding form based on the user's selection.

Step 2: Create a List Box on the Control Screen.

To build a dynamic form navigation system, follow these steps to create a List Box that shows your data file list:

1. Open the Control Screen form in Design View.

2. Select the List Box tool from the toolbox (or Controls group in the Ribbon).

3. Click on the form to place the List Box.

4. When the List Box Wizard starts:

   • Choose: “I want the list box to look up the values in a table or query.”

   • Select your DataFiles table.

   • From the available fields, choose only the ID and Desc fields.

   • Sort by ID or Desc as needed.

   • Set the column widths (e.g., hide ID by setting its width to 0 if you only want to show descriptions).

   • Save the list box with a meaningful name like lstForms.

Now, your List Box will display a list of form descriptions from the DataFiles table. When you open the Control Screen, you will see a list of serial numbers and corresponding descriptions of your data files populated in the List Box.

This setup provides a clear and organized view, making it easy for users to select the form they wish to open.

Sample List Box View.

Click to Enlarge

Now, go back to the design view of the Control Screen (if you have turned on the Form View earlier) and click on the ListBox and display the Property Sheet (View - ->Properties). Change the Name Property to Dat1.

Creating the FormOpen() Function

Display the VB Module (click on the Module Tab of your Database and click on the New option on the database window, which will open a new Global Module. Copy and paste the following Code in that Module and save it.

Public Function FormOpen(ByVal FileName As String, ByVal ID As Integer) As Boolean 
Dim vartyp As Variant, varFormName As Variant 
Dim varMacName As Variant 
Dim Criteria As String  
On Error GoTo FormOpen_Err  
FormOpen = False  
Criteria = "ID = " & Format(ID)  
vartyp = DLookup("Type", FileName, Criteria)  
If vartyp = 0 Then
 varFormName = DLookup("Form", FileName, Criteria) 
   If IsNull(varFormName) = False Then
      DoCmd.OpenForm varFormName, acNormal
   End If
 ElseIf vartyp = 1 Then 
   varMacName = DLookup("Macro", FileName, Criteria) 
      If IsNull(varMacName) = False Then
         DoCmd.RunMacro varMacName
       End If 
End If
    FormOpen = True

FormOpen_Exit: 
Exit Function  

FormOpen_Err: 
MsgBox Err.Description, , "FormOpen" 
FormOpen = False 
Resume FormOpen_Exit 
End Function 

Now, let’s write a line of code to respond to the On Double-Click event of the List Box.

1. Switch to Design View of the form containing your List Box.

2. Select the List Box control, and open the Property Sheet (if not already open).

3. Locate the property called On Dbl-Click.

4. From the dropdown, select $Event Procedure$.

5. Then, click on the build (...) button (just to the right of the dropdown) to open the form's Code Module in the VBA editor.

You're now ready to write the code that opens the selected form when a user double-clicks a description in the List Box.

You will find an empty On Dbl Click Event Procedure with its starting line Private Sub Dat1_DblClick(Cancel As Integer) and End Sub lines. Complete it by inserting a line of code between these lines as shown below, without making typing mistakes:

Private Sub Dat1_DblClick(Cancel As Integer)
   FormOpen "DataFiles", Me![Dat1]
End Sub

Tip: If you’re unsure about the exact code, highlight the middle line shown above, copy it, and paste it directly into the form’s code module — between the automatically generated lines for the On Dbl_Click event.

Make sure the List Box is named exactly as Dat1, as this name is referenced in the code. The source table name used for the List Box must be placed inside double quotes, and the two parameters passed to the FormOpen function should be separated by a comma.

⚠️ Important: If you have used different names for the List Box or the source table in your database, you must update them accordingly in the code.

Now, close the Form's Code Module and save the Form. It is time to test our project. Before testing, check the accuracy of the Form Names that you have filled in the Form field of the DataFiles Table.

Step: Open the Control Form, then double-click on any item in the List Box.
You should immediately see the corresponding Form open, based on the item you selected.

Troubleshooting Tips:
If you encounter any errors:

• Verify the Name of the Listbox (it should be Dat1 if you're following the example).

• Confirm the source data table name is correct.

• Check that the Form names listed in the Form field of the DataFiles table match exactly with your actual form names.

• Ensure that the Type field is filled  0 for all entries.

• Make sure the ID field contains consecutive numbers without gaps.

✅ Tip: Use short, consistent names for your forms to minimize errors when referencing them.

💡 What Happens When You Double-Click an Item in the Listbox?

When a user double-clicks an item in the Listbox:

1. The ID (sequence number) of the selected item

2. And the name of the source data table

...are both passed as arguments to the FormOpen() function.

The FormOpen() function then looks at the Type value of the selected record and performs an action based on it:

---

✅ Action Based on Type Field:

Type Value	Action Performed
(Empty)	Double-click is ignored (no action is taken).
0	Opens the form specified in the Form field of the table.
1	Runs the macro specified in the Macro field of the table.

---

This setup gives you flexibility to launch either a Form or a Macro from the same Listbox interface, based on how you configure the Type field in your DataFiles table.

This is useful if you want to open a table in read-only mode, or to open a form after running a series of Queries to create an output table/Query linked to a Form that expects different results every time, etc., by writing a macro and putting its name in the Macro Field.

🧩 Extend the Same Method to Reports

This method isn't limited to Forms—you can use it for Reports.
Once you’ve designed additional Forms or Reports, simply add a new entry to the Listbox’s source table (DataFiles table).

There’s no need to write extra code—the existing double-click action will automatically handle opening the correct Form or Report based on the Type field and associated settings.

Next, we will see how to use the Hyperlink method in a Listbox design.

Click Next to see the use of Hyperlinks in the Listbox

Read More