External Files List in Hyperlinks Form.
The 'Office.FileDialog' Control
The FileDialog Control displays files from the selected folder as hyperlinks within the form. Clicking on a hyperlink opens the file in its native application, if installed.
The control supports user-defined filters, enabling users to narrow down the file list by category—for example, Word documents, Excel worksheets, Access databases, or all files in the folder. Once the required files are selected, clicking the Create File Link command button adds them to a table and displays them in the form’s datasheet view as hyperlinks. For reference, the full file path is also shown in a separate column.
Files' List Display Image.
After entering the file filter in the Pathname text box, click the Create File Links command button. This action opens the File Browser control, which displays the available files and folders based on the filter settings.
At this stage, you may navigate to and select any folder to search for files. To choose multiple adjoining files, click on the first file, hold down the Shift key, and then click on the last file. Finally, click the Open command button. The selected files will then appear in the list, as shown in the first image.
The Form Module VBA Code.
Option Compare Database
Option Explicit
Private FD As New FLst_Object_Init
Private Sub Form_Load()
Set FD.fl_Frm = Me
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set FD = Nothing
End Sub
In the global declaration area, an instance of the FLst_Object_Init class module is declared with the name FD. The keyword New is used to create the object instance in memory.
In the Form_Load() event procedure, the current form object is passed to the FD.fl_Frm property of the FD instance.
The FLst_Object_Init Class Module Code.
The FLst_Object_Init with the Class_Init() Subroutine VBA Code is given below:
Option Compare Database
Option Explicit
Private cmd As FLst_CmdButton
Private frm As Access.Form
Private Coll As New Collection
'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Disk Directory Listing in Hyperlinks
'Author: a.p.r. pillai
'Date : 25/10/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Property Get fl_Frm() As Access.Form
Set fl_Frm = frm.m_cFrm
End Property
Public Property Set fl_Frm(ByRef pNewValue As Access.Form)
Set frm = pNewValue
Call Class_Init
End Property
Private Sub Class_Init()
Dim ctl As Control
Dim listcount As Long
Const EP = "[Event Procedure]"
'=============================
'Calling the Public Function ButtonStatus() From FLst_CmdButton Class
'from the Flst_CmdButton Class directly,
Set cmd = New FLst_CmdButton 'Create a separate instance
Set cmd.cmd_Frm = frm 'Pass the Form Object to the Property
Call cmd.ButtonStatus 'Call the Public Function, with Param, if any
Set cmd = Nothing 'Remove the instance
'=============================
For Each ctl In frm.Controls
Select Case TypeName(ctl)
Case "CommandButton"
Select Case ctl.Name
Case "cmdHelp", "cmdFileDialog", _
"cmdDelLink", "cmdDelFile", _
"cmdClose", "cmdDelAll"
Set cmd = New FLst_CmdButton
Set cmd.cmd_Frm = frm
Set cmd.c_cmd = ctl
cmd.c_cmd.OnClick = EP
Coll.Add cmd
Set cmd = Nothing
End Select
End Select
Next
End Sub
Private Sub Class_Terminate()
Do While Coll.Count > 0
Coll.Remove 1
Loop
End Sub
The following two Subroutines, if present in the Class Module, run automatically.
Class_Initialize()
Class_Terminate()
Assume that we have both the above Subroutines in ClassA.
When you create an instance of ClassA inside ClassB, for example:
The Class_Initialize() subroutine of ClassA runs automatically (if it exists). You can place any initialization code here to prepare the class for use.
When the statement Set A = Nothing is executed, or when the ClassB object itself is unloaded, the Class_Terminate() subroutine in ClassA executes. This is where you can perform clean-up tasks, such asSet Obj = Nothing releasing memory and resources.
This mechanism is especially useful when your class contains other objects—like a Collection, Dictionary, or additional class instances—that must be explicitly cleared.
The following Class1 module is instantiated within the form module.
'Class1
Dim DT As ClsDateTime
Private Sub Class_Initialize()
Set DT = New ClsDateTime
Forms("Form2").Text2 = DT.DateTime
End Sub
Private Sub Class_Terminate()
Set DT = Nothing
End Sub
The Class_Initialize() subroutine, if defined in a class module, executes automatically when the class object is instantiated. However, in our streamlined VBA coding approach, we are unable to leverage this feature. The reason is that the class requires the Form object reference to be available before the initialization routine can run. Since the Form object is not yet available at the moment of instantiation, we cannot rely on Class_Initialize().
Instead, we explicitly call the Class_Init() subroutine immediately after acquiring the Form object reference within the class module’s Property Set procedure. This ensures that initialization takes place only after the required Form reference is available.
That doesn’t mean the Class_Initialize() subroutine is unusable in this context. We can still take advantage of it to instantiate supporting objects, such as a Collection or Dictionary, or any other objects that do not depend on the Form reference. For instance, you might use it to create and prepare a Collection object as shown below:
Private Sub Class_Initialize() Set Coll = New Collection End Sub
The Collection object is declared in the global declaration area of the Class Module. Since we used the New keyword in the declaration statement, explicit initialization code inside the Class_Initialize() subroutine is not required—the object is automatically created when the class instance is instantiated.
The Class_Terminate() subroutine, on the other hand, is very useful for memory management. It acts much like the Form_Unload() event procedure, providing a place to release object references and perform any necessary cleanup before the class instance is destroyed.
Private Sub Class_Terminate()
Do While Coll.Count > 0
Coll.Remove 1
Loop
End Sub
The above code ensures that the Collection object is cleared when the FLst_Object_Init Class Module unloads from memory.
For this project, only two Wrapper Class Modules are required:
-
FLst_Object_Init — which contains the Class_Init() subroutine.
-
FLst_CmdButton — which handles all Command Button operations on the form.
The FLst_CmdButton class contains several subroutines. For clarity and better organization, each Command Button’s Click Event procedure calls its corresponding subroutine from this class, instead of placing the entire block of code directly under the Command Button event. This approach makes the code more modular, easier to read, and simpler to maintain.
The FLst_CmdButton Class Module Code.
'The Click Event Subroutines
Private Sub cmd_Click()
Select Case cmd.Name
Case "cmdClose"
If MsgBox("Close this Form?", vbOKCancel + vbQuestion, "cmd_Click") = vbOK Then
DoCmd.Close acForm, cmdfrm.Name
Exit Sub
End If
Case "cmdFileDialog"
Call cmdFileDialog 'Display selected Path & files
Case "cmdDelLink"
Call cmdDelLink 'Delete Selected Link from list
Case "cmdDelAll"
Call cmdDelAll 'Delete All Links from list
Case "cmdDelFile"
Call cmdDelFile 'Delete Link and File from Disk
Case "cmdHelp"
DoCmd.OpenForm "Help", acNormal 'Show help Form
End Select
End Sub
The cmdFileDialog() Subroutine.
This Subroutine is run by clicking on the Command Button with the Caption Create File Links.
Private Sub cmdFileDialog()
On Error GoTo cmdFileDialog_Click_Err
'Requires reference to Microsoft Office 12.0 Object Library.
Dim fDialog As office.FileDialog
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim defPath As String
Dim varFile As Variant
Dim strfiles As String
'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Allow user to make multiple selections of disk files.
.AllowMultiSelect = True
.InitialFileName = Dir(strPath)
.InitialView = msoFileDialogViewDetails
'Set the title of the dialog box.
.Title = "Please select one or more files"
'Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "Access Databases", "*.mdb; *.accdb"
.Filters.Add "Excel WorkBooks", "*.xlsx; *.xlsm; *.xls; *.csv"
.Filters.Add "Word Documents", "*.docx; *.doc"
.Filters.Add "Access Projects", "*.adp"
.Filters.Add "All Files", "*.*"
.FilterIndex = 1
'.Execute
'Show the dialog box. If the .Show method returns True, the
'user picked at least one file. If the .Show method returns
'False, the user clicked Cancel.
If .Show = True Then
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
'Add all selected files to the DirectoryList Table
defPath = ""
For Each varFile In .SelectedItems
If defPath = "" Then
defPath = Left(varFile, InStrRev(varFile, "\"))
defPath = defPath & "*.*"
cmdfrm.PathName = defPath
cmdfrm.PathName.Requery
strPath = defPath
End If
rst.AddNew
'Create Hyperlink in 4 segments
'1st segment: only the File Name
strfiles = Mid(varFile, InStrRev(varFile, "\") + 1)
'2nd segment:Full File PathName,3rd Empty,4th TipText
strfiles = strfiles & "#" & varFile & "##Click"
rst![FileLinks] = strfiles
rst![Path] = varFile
rst.Update
Next
Call ButtonStatus
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
cmdFileDialog_Click_Exit:
Exit Sub
cmdFileDialog_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdFileDialog_Click()"
Resume cmdFileDialog_Click_Exit
End Sub
The statement
opens the File Browser Dialog Control and initializes its various properties. Within this control, file type filters can be defined, allowing users to select specific categories of files when browsing from the default path setting.
If users are unsure about the file selection process, they can click on the Help Command Button located at the top right of the form. This button provides detailed guidance on the purpose of each command button and explains the different ways files can be selected.
A table named DirectoryList is designed to store the selected files. The first column saves the files in hyperlink format, while the second column records the full file path for reference. Clicking on a hyperlink will open the file in its native application (e.g., MS Word, Excel, etc.).
The statement
invokes the ButtonStatus() subroutine, which checks whether the DirectoryList table contains any records. If the table is empty, all command buttons (except Create File Links and Help) are disabled. This subroutine is also called from other procedures as well as from the FLst_Object_Init class module (refer to the red-highlighted code inside the Class_Init() subroutine above).
Another important point: if you create a Public function inside a stand-alone class module, it becomes accessible across other class modules or standard modules within the application. This means such a function can be called and reused from outside its defining class.
In the next step, we will conduct some trial runs to explore how to call a function from:
-
another Class Module,
-
a Standard Module, and
-
a Form Module.
The cmdDelLink Subroutine.
To delete a record from the hyperlink list, first click on the Record Selector button to highlight the desired record. Then click the Delete Link command button. Before the record is permanently removed, a confirmation message will appear, giving you the option to proceed with the deletion or cancel the action.
'Delete the Link From the List
Private Sub cmdDelLink()
On Error GoTo cmdDelLink_Click_Err
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFile As String
Dim msg As String
'Read the current record Pathname
strFile = cmdfrm.DirectoryList.Form!Path
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
rst.FindFirst "Path = '" & strFile & "'"
If Not rst.NoMatch Then
msg = UCase("Link: " & strFile & vbCr & "DELETE from above List?")
If MsgBox(msg, vbQuestion + vbYesNo, "cmddelLink_Click()") = vbYes Then
rst.Delete
rst.Requery
cmdfrm.DirectoryList.Form.Requery
MsgBox UCase("File Link: " & strFile & " Deleted.")
End If
Else
MsgBox UCase("Link: " & strFile & " Not Found!!")
End If
Call ButtonStatus
rst.Close
Set rst = Nothing
Set db = Nothing
cmdDelLink_Click_Exit:
Exit Sub
cmdDelLink_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelLink_Click()"
Resume cmdDelLink_Click_Exit
End Sub
The cmdDelAll() Subroutine.
This subroutine deletes all records from the DirectoryList table. Once the deletion is complete, all three command buttons associated with delete actions are disabled. They remain disabled until at least one file is added back to the hyperlink list.
Private Sub cmdDelAll()
Dim msg As String
Dim yn As Integer
Dim listcount As Long
On Error GoTo cmdDelAll_Click_Err
listcount = DCount("*", "DirectoryList")
If listcount = 0 Then
cmdfrm.cmdDelAll.Enabled = False
Exit Sub
Else
cmdfrm.cmdDelAll.Enabled = True
End If
msg = "All File Links in the List will be Deleted!"
msg = msg & vbCr & "Are You sure?"
If MsgBox(msg, vbYesNo + vbCritical, "cmdDelAll()") = vbYes Then
If MsgBox("Deleting All File Links?", vbOKCancel + vbInformation, "cmdDelAll()") = vbOK Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "DeleteAll_LinksQ", acViewNormal
DoCmd.SetWarnings True
cmdfrm.DirectoryList.Form.Requery
cmdfrm.cmdDelAll.Enabled = False
End If
End If
Call ButtonStatus
cmdDelAll_Click_Exit:
Exit Sub
cmdDelAll_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelAll_Click()"
Resume cmdDelAll_Click_Exit
End Sub
The cmdDelFile() Subroutine.
Caution:
Be cautious when using this command button. Clicking it will permanently delete the file from the disk as well as remove its hyperlink from the list. Use this option only when you intend to delete the actual file from your system, not just the link.
'Caution: Deletes the File from Disk
'1. Delete the File from Disk
'2. Remove selected link from List
Private Sub cmdDelFile()
On Error GoTo cmdDelFile_Click_Err
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFile As String
Dim msg As String
'Read selected Record Pathinfo
strFile = cmdfrm.DirectoryList.Form!Path
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
rst.FindFirst "Path = '" & strFile & "'"
If Not rst.NoMatch Then
msg = UCase("File: " & strFile & vbCr & "DELETE from Disk?")
If MsgBox(msg, vbQuestion + vbYesNo, "cmdDelFile_Click") = vbYes Then
If MsgBox(UCase("Are you sure you want to Delete") & vbCr _
& UCase(rst!Path & " File from DISK?"), vbCritical + vbYesNo, "cmdDelFile_Click()") = vbNo Then
GoTo cmdDelFile_Click_Exit
End If
'Delete record entry from Table DirectoryList
rst.Delete
rst.Requery
Call ButtonStatus
'Delete file from Disk
If Len(Dir(strFile)) > 0 Then
Kill strFile
MsgBox "File: " & strFile & " Deleted."
Else
MsgBox "File: " & strFile & vbCr & "Not Found on Disk!"
End If
End If
Else
MsgBox "File: " & strFile & " Not Found!!"
End If
cmdDelFile_Click_Exit:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
cmdDelFile_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelFile_Click()"
Resume cmdDelFile_Click_Exit
End Sub
The ButtonStatus()
All three delete subroutines in the FLst_CmdButton class, as well as those in the FLst_Object_Init class, invoke the public subroutine ButtonStatus(). This subroutine ensures that the command buttons remain disabled whenever the DirectoryList table is empty.
Public Sub ButtonStatus()
Dim listcount As Long
On Error GoTo ButtonsStatus_Err:
listcount = DCount("*", "DirectoryList")
cmdfrm.DirectoryList.Form.Requery
If listcount = 0 Then
cmdfrm.cmdDelLink.Enabled = False
cmdfrm.cmdDelAll.Enabled = False
cmdfrm.cmdDelFile.Enabled = False
Else
cmdfrm.cmdDelLink.Enabled = True
cmdfrm.cmdDelAll.Enabled = True
cmdfrm.cmdDelFile.Enabled = True
End If
ButtonsStatus_Exit:
Exit Sub
ButtonsStatus_Err:
MsgBox Err & " : " & Err.Description, , "ButtonsStatus()"
Resume ButtonsStatus_Exit
End Sub
Calling a Public Function from a Class Module.
Create a Class Module with the name ClsDateTime.
Copy and Paste the following Function Code into the Class Module:
Option Compare Database Option Explicit Public Function DateTime() As String Dim fmt As String fmt = "dd/mm/yyyy hh:nn:ss" DateTime = "DateTime: " & Format(Now(), fmt) End Function
Save the Class Module.
Create a New Form with the name Form1, or any other name you prefer, and open it in Design View.
Add a TextBox Control on the Form and make sure the TextBox Name is Text0.
Display the Form Property Sheet and select the Other Tab in the Property Sheet.
- Set the Has Module Property value to Yes to add a Class Module to the Form.
Display the Form1 Code Module, Copy and Paste the following Code in the Form Module, Save and Close the Form:
Private Sub Form_Load() Dim DT As New ClsDateTime Me.Text0 = DT.DateTime End Sub
Open Form1 in Normal View. The current Date and Time will appear in the TextBox.
In the Form_Load() event procedure, create an instance of the ClsDateTime class module with the object name DT. When you type
DT.the DateTime() function will automatically appear in the IntelliSense list. Simply select and call the function, and when the form opens, the current date and time will be displayed in the designated TextBox.This same procedure can also be applied between two class modules—allowing you to call a function in one class module from another, instead of from the form module.
In our streamlined, structured VBA coding approach, we typically work with three levels of class modules:
-
The Form Module
-
An Intermediary Class Module
-
The Class Module containing the required function (in this case, DateTime())
Let us now test this function in such a three-tier setup, where the form module communicates with the intermediary class, which in turn calls the function in the dedicated class module.
-
Make a Copy of Form1 and name it Form2.
Rename the TextBox Name to Text2.
Display its Class Module, then copy and paste the following two Lines of Code, overwriting the existing lines.
Option Compare Database Private test As New Class1
Create a Class Module with the Name Class1.
Copy and paste the Following Code into the Class1 Module:
Option Compare Database Private D As New ClsDateTime Private Sub Class_initialize() Forms("Form2").Text2 = D.DateTime End SubSelect Save from the File Menu to save all the Files.
Open Form2 in Normal View. The DateTime value should appear in the Text2 TextBox on the Form.
Since class modules cannot load themselves into memory, we used the Form2 module to create an instance of the Class1 module. Once the Class1 class module is instantiated, it in turn creates an instance of the ClsDateTime class module.
At this point, the Class_Initialize() subroutine in ClsDateTime runs automatically. From within this subroutine, the DateTime() public function is called. The result returned by the function is then passed back and displayed in the TextBox on Form2.
✅ This keeps the workflow very clear:
Form2 → Class1 → ClsDateTime (Initialize → DateTime() → Return value → Form2.TextBox)Hope you understand now how it works.
Try Calling the DateTime() Function from the Standard Module from a Test() Function.
Demo Database Download Link.
Streamlining Form Module Code in Standalone Class Module.
- Reusing Form Module VBA Code for New Projects.
- Streamlining Form Module Code - Part Two.
- Streamlining Form Module Code - Part Three
- Streamlining Form Module Code - Part Four
- Streamlining Form Module Code - Part Five
- Streamlining Form Module Code - Part Six
- Streamlining Form Module Code - Part Seven
- Streamlining Form Module Code - Part Eight
- Streamlining Form Module Code - Part Nine
- Streamlining Form Module Code - Part Ten
- Streamlining Form Module Code - Part Eleven
- Streamlining Report Module Code in Class Module
- Streamlining Module Code Report Line Hiding-13.
- Streamlining Form Module Code Part-14.
- Streamlining Custom Made Form Wizard-15.
- Streamlining VBA Custom Made Report Wizard-16.
- Streamlining VBA External Files List in Hyperlinks-17
- Streamlining Events VBA 3D Text Wizard-18
- Streamlining Events VBA RGB Color Wizard-19
- Streamlining Events Numbers to Words-20
- Access Users Group(Europe) Presentation-21
- The Event Firing Mechanism of MS Access-22
- One TextBox and Three Wrapper Class Instances-23
- Streamlining Code Synchronized Floating Popup Form-24
- Streamlining Code Compacting/Repair Database-25
- Streamlining Code Remainder Popup Form-26
- Streamlining Code Editing Data in Zoom-in Control-27
- Streamlining Code Filter By Character and Sort-28
- Table Query Records in Collection Object-29
- Class for All Data Entry Editing Forms-30
- Wrapper Class Module Creation Wizard-31
- wrapper-class-template-wizard-v2
DirListing2K1.zip
