📋 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:
-
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 namedfrmCustomers
:
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 namedHlink_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:
-
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"
-
"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"
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:
-
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
-
Move your mouse pointer toward the left edge of the hyperlink field.
-
When the pointer changes to a 3D "+" cross, click once.
-
This will select the field instead of activating the hyperlink.
🖱️ Method 2: Use Right-Click + Cancel
-
Right-click on the hyperlink field.
-
This will highlight the field and open a shortcut menu.
-
Then, click outside the menu (anywhere on the screen) to dismiss it.
-
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.
- 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.
- 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.
- Press the Alt+Enter keys, or click Properties from the View menu to display the Form's property sheet.
- 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
- Click on the Detail Section of the form and display the Property Sheet.
- Change the Back Color Property Value to 128 (will change to dark red color)
- Select the Seq Field control and change its width property to 0.2'
- Select the Seq & Desc text boxes together, and select Align -> Left from the Format menu, which will bring the controls close together.
- Select Shortest from the Format -> Size Option to change both controls' height to the same value.
- 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
- 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
- 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 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...

many thanks to you
ReplyDeletebut can you up to us an examole to see that
or send it to me at : ahmedtharwat19@yahoo.com
Yes, I will forward a sample database to you shortly.
ReplyDeleteregards,
a.p.r. pillai
Do you know how to connect a hyperlink to a command button that sends you to the hyperlink based on selections picked from combo boxes? Example to clarify:
ReplyDeleteYou want to set up a car database so you make tables with certain information regarding the car, but for the purpose of this, lets just look at the model table. The model table (tblModel) consists of the following fields: ModelID, ModelName, MakeID, Hyperlink. The MakeID would be a link if you had cascading combo boxes in the form. So from a form the user would select a Make (cboMake), which would then break down the Models (cboModel). Based on the selection made by the user for the model the code in the command button would compare the model selected in cboModel to the model in tblModel and return the hyperlink (field 4) so that when the user clicked the command button he would see a pdf for that model.
Thanks in advance
Cory Hall - cory_r_hall@yahoo.com
Yes, this is possible. Sample Code is given below:
ReplyDeletePrivate Sub cboModel_Click()
Dim myhype, xy As String, xcboModel
Dim db As Database, rst As Recordset
xcboModel = Me![cboModel]
Set db = CurrentDb
Set rst = db.OpenRecordset("tblModel", dbOpenDynaset)
rst.FindFirst "ModelID = " & Chr$(34) & xcboModel & Chr$(34)
If Not rst.NoMatch Then
Set myhype = rst![Hlink]
xy = HyperlinkPart(myhype, acAddress)
Me.cmdPDF.HyperlinkAddress = xy
End If
rst.Close
End Sub
The above Code Runs when you Click on the cboModel ComboBox. Searches the Table for the Model Value selected in the Combo Box and when found retrieves the HyperLink Value into myhype Variable. The HyperLinkPart() Function extracts the .pdf file pathname from the Hyperlink Field. The extracted Path is inserted into the HyperLink Address Property of the cmdPDF Command Button. When you Click the Command Button the PDF File will be opened.
The Hyperlink Field Value in the tblModel must be set in the following format:
Display Text#pdf File PathName##Tooltip Text
Example:
Model Document#C:\My Documents\Lexus.pdf##Click
You may visit the following link to learn how to refresh a ComboBox contents that depends on another ComboBox value selection:
http://www.msaccesstips.com/2008/03/refresh-dependant-combo-box-contents/
Regards,
a.p.r. pillai
The code works great, thanks a lot. Was just going to ask about two lines:
ReplyDeleteSet rst = db.OpenRecordset("tblModel", dbOpenDynaset)
rst.FindFirst "ModelID = " & Chr$(34) & xcboModel & Chr$(34)
I know if you have spaces in your titles such as Model ID instead of ModelID, you usually put the name in brackets such as [Model ID], but it seems for both tblModel and ModelID above it gives debug errors when you bracket them.
You don't need to put [] in the first statement, even if there is space in the Tahle Name.
ReplyDeleteBut, if there is space in the ModelID Field Name then you must insert the square brackets. When you do see that you are not adding extra spaces immediately after or before the brackets.
Regards,
With the following code, I'm trying to get a bit fancier and embed the pdf within the form. I'm using a microsoft web browser object to do this, but when i click the cmdPDF button it just shows a page that can't find the hyperlink. acxWebBrowser is the name of the web browser object.
ReplyDeletePrivate Sub cboModel_Click()
Dim myhype, xy As String, xcboModel
Dim db As Database, rst As Recordset
xcboModel = Me![cboModel]
Set db = CurrentDb
Set rst = db.OpenRecordset("tblModel", dbOpenDynaset)
rst.FindFirst "ModelID = " & Chr$(34) & xcboModel & Chr$(34)
If Not rst.NoMatch Then
Set myhype = rst![Hlink]
xy = HyperlinkPart(myhype, acAddress)
Me.acxWebBrowser.Navigate "xy"
End If
rst.Close
End Sub
Remove the Quotes from Me.acxWebBrowser.Navigate "xy"
ReplyDeleteMe.acxWebBrowser.Navigate xy
and try again.
Thanks for the idea, can you send me to nurulmarea@gmail.com the example of the database so that I can get the clear idea. Thansk and regards
ReplyDeleteI will forward a Demo Database to you shortly.
ReplyDeleteRegards
Hello,
ReplyDeleteCan I pass argument inside hyperlink?
For Example:
When I open hyperlink in edit mode looks like
"Address: #Form Employees"
Can I open form Employees to display specific Employee?
Something like:
Docmd.OpenForm Employees,WhereCondition:="[ID]="&51
Parameter Values cannot be passed through HyperlinkSubAddress. You have already shown the example through the Docmd.OpenForm method.
ReplyDeleteHello again,
ReplyDeleteIs there way to call Public Function or Public Sub with Hyperlink?
[…] […]
ReplyDelete