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.

Right-click to open a Large Image in New Tab/Window

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
    • BackColor: 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:

Right-click to open a Large Image in New Tab/Window

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

Right-click to open a Large Image in New Tab/Window

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, 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 set its Special Effect Property to Etched. Save the Form. The completed design on the Control Screen will look like the sample given below:

Right-click to open a Large Image in New Tab/Window

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:

Right-click to open a Large Image in New Tab/Window

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.

Right-click to open a Large Image in New Tab/Window

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 $EventProcedure$.

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

Closing MS Access Forms

Access Form Closing Options.

There are several ways to close an active Access form, and with a few simple techniques, you can enhance the appearance and behavior of this action for a better user experience.

The quickest method to close an active Access form is by clicking the standard Close (X) button on the window, which most users are familiar with. This action also triggers the Form's 'On Close' event, allowing the programmer to handle any necessary final tasks, such as closing related forms or saving changes.

However, when multiple forms are open simultaneously, there's a risk that the user might accidentally close the wrong form by clicking the incorrect X button. This can disrupt workflow or cause confusion, especially in multi-form applications.

Command Button with Macro Hyperlink.

Instead of relying on the standard Close (X) button, I prefer to create a dedicated Command Button placed conveniently on the form, usually in the Header or Footer section. This provides a clearer and more user-friendly method to close the form.

To implement this, I create a macro (I usually name it Hlink_Close) that contains only the Close action. I leave the Object Type and Object Name parameters blank, so it simply closes the active form. Then, I set the Command Button’s Hyperlink SubAddress property to point to this macro.

This approach not only avoids accidental form closure but also provides a more controlled and visually consistent user experience.

To link your Command Button to the Hlink_Close macro using the Hyperlink SubAddress property, follow these steps:

1. Select the Command Button you’ve added to your form.

2. In the Property Sheet, go to the Other tab and locate the Hyperlink SubAddress property.

3. Click the build (…) button next to this property to open the Edit Hyperlink dialog box.

4. In the dialog box, under Link to, select Object in this Database.

5. A list of database objects (Tables, Queries, Forms, Macros, etc.) will appear.

6. Expand the Macros group and locate the Hlink_Close macro.

7. Select it, and click OK to apply the link.

Now, when the user clicks the Command Button, the macro will run and close the form.

An additional advantage of using the Hyperlink SubAddress method is its visual feedback.
When the user moves the mouse pointer over the Command Button linked to a macro (like Hlink_Close) the cursor changes to a hand with the index finger pointing, just like it does when hovering over a hyperlink on a webpage.

This subtle cue improves usability by clearly indicating that the control is clickable, making your form more intuitive and interactive.

Reusing the Hlink_Close Macro Across Multiple Forms.

The same Hlink_Close Macro can be reused across any number of forms. You can assign it to Command Buttons, Labels, Images, or any control that supports the Hyperlink SubAddress property.

By linking these controls to the Hlink_Close Macro users can simply click the object to close the form, without writing additional code for each instance. This method provides a consistent and user-friendly way to close forms across your application.

Important Note on Popup Forms. 

'Hlink_Close' The macro method does not work on forms where the Popup property is set to Yes. In such cases, clicking a control with a 'Hlink_Close' Hyperlink may instead close another open form with Popup = No, leaving the intended form open.

If the pop-up form is the only form open, Access will throw an error:

“Can’t follow the Hyperlink Macro Hlink_Close.”

Recommended Alternative.

For pop-up forms, use a Command Button with an On Click event procedure in VBA:

vba
Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

This reliably closes the active pop-up form without relying on the Hlink_Close macro.

If you need to change only the style of the Mouse Pointer to a hand while pointing the cursor on the control, then insert a # symbol in the Hyperlink SubAddress Property of the Command Button.

Tip: You can Open External Files Like Word, Excel, etc. by setting the Hyperlink Address Property (not Hyperlink SubAddress) of the Command Button with an external File's location address. Select the Link to Option: Existing File or Webpage rather than Object in this Database from the Edit Hyperlink Dialog Control.

Click Next to see Opening Access Forms

Read More