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:
-
Add a Command Button to your Control Screen.
-
Set its Hyperlink SubAddress property to the name of the form you want to open (e.g.,
frmCustomer
). -
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:
-
Open the Control Screen form in Design View.
-
Select the List Box tool from the toolbox (or Controls group in the Ribbon).
-
Click on the form to place the List Box.
-
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
andDesc
fields. -
Sort by
ID
orDesc
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.
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.
-
Switch to Design View of the form containing your List Box.
-
Select the List Box control, and open the Property Sheet (if not already open).
-
Locate the property called On Dbl-Click.
-
From the dropdown, select .
-
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 theDataFiles
table match exactly with your actual form names. -
Ensure that the
Type
field is filled0
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:
-
The ID (sequence number) of the selected item
-
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
[...] other databases also once you know few basics of it. Take a look at the Article on this topic: Opening Access Forms __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn [...]
ReplyDelete