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