Assigning Module Level Error Trap Routines

Introduction.

Last week, I introduced a function that automatically inserts error-handling lines into a VBA function or subroutine. While readers appreciated its usefulness, some felt the process was a bit cumbersome.

Before running that function, the user had to identify some text to search for and then execute the function with that text as a parameter. The utility relied on the 'Text.Find()' method of the Module object to locate the specified text and select the corresponding line within the target function or subroutine. From that starting point, it could determine other details—such as the total number of lines in the procedure, the line number of the header, and the line number of the end statement. These values were necessary to insert the error-handling lines in the correct locations.

However, when working with multiple functions or subroutines, this method could become time-consuming, as each one has to be processed individually.

In this article, we’ll explore an improved version of the utility that scans an entire module and inserts error-handling lines into all functions and subroutines in a single pass.

Before we dive in, here are links to the earlier articles, in case you’d like to revisit the simpler methods we tried using the Module object:

• Write VBA Code with VBA
• VBA Module Object and Methods
• Prepare a list of Procedure Names from a Module
Utility for inserting VBA Error Handler Code

The ErrorTrap() Function.

The new function is much simpler to use.  Copy and paste the following code into a new Standard Module and save it:

Public Function ErrorTrap(ByVal str_ModuleName As String)
On Error GoTo ErrorTrap_Error
'--------------------------------------------------------------
'Program : Inserting Error Handler Lines automatically
'        : in a VBA Module 
'Author  : a.p.r. pillai
'Date    : December, 2011
'Remarks : All Rights Reserved by www.msaccesstips.com
'--------------------------------------------------------------
'Parameter List:
'1. strModuleName - Standard Module or Form/Report Module Name
'--------------------------------------------------------------

Dim objMdl As Module, x As Boolean, h As Long, i As Integer
Dim w As Boolean, lngR As Long, intJ As Integer, intK As Integer
Dim linesCount As Long, DeclLines As Long, lngK As Long
Dim str_ProcNames(), strProcName As String, strMsg As String
Dim start_line As Long, end_line As Long, strline As String
Dim lng_StartLine As Long, lng_StartCol As Long
Dim lng_EndLine As Long, lng_EndCol As Long, procEnd As String
Dim ErrHandler As String, lngProcLineCount As Long
Dim ErrTrapStartLine As String, lngProcBodyLine As Long

Set objMdl = Modules(str_ModuleName)

linesCount = objMdl.CountOfLines
DeclLines = objMdl.CountOfDeclarationLines
lngR = 1
strProcName = objMdl.ProcOfLine(DeclLines + 1, lngR)
If strProcName = "" Then
   strMsg = str_ModuleName & " Module is Empty." & vbCr & vbCr & "Program Aborted!"
   MsgBox strMsg, , "ErrorTrap()"
   Exit Function
End If
strMsg = strProcName
intJ = 0

'Determine procedure Name for each line after declaraction lines
For lngK = DeclLines + 1 To linesCount
  
  'compare procedure name with ProcOfLine property
  If strProcName <> objMdl.ProcOfLine(lngK, lngR) Then
     'increment by one
     intJ = intJ + 1
     'get the procedure name of the current program line
     strProcName = objMdl.ProcOfLine(lngK, lngR)
  End If
Next lngK

ReDim str_ProcNames(intJ)

strProcName = strMsg: intJ = 0
str_ProcNames(intJ) = strProcName
For lngK = DeclLines + 1 To linesCount
  'compare procedure name with ProcOfLine property
  
  If strProcName <> objMdl.ProcOfLine(lngK, lngR) Then
     'increment array index by one
     intJ = intJ + 1
     'get the procedure name of the current program line
     strProcName = objMdl.ProcOfLine(lngK, lngR)
     str_ProcNames(intJ) = strProcName
     
  End If
Next
   
For intK = 0 To intJ
    ErrHandler = ""
    ErrTrapStartLine = ""
    'Take the total count of lines in the module including blank lines
    linesCount = objMdl.CountOfLines

    strProcName = str_ProcNames(intK) 'copy procedure name
    'calculate the body line number of procedure
    lng_StartLine = objMdl.ProcBodyLine(strProcName, vbext_pk_Proc)
    'calculate procedure end line number including blank lines after End Sub
    lng_EndLine = lng_StartLine + objMdl.ProcCountLines(strProcName, vbext_pk_Proc) + 1
    
    lng_StartCol = 0: lng_EndCol = 150
    start_line = lng_StartLine: end_line = lng_EndLine
    
    'Check for existing Error Handling lines in the current procedure
    x = objMdl.Find("On Error", lng_StartLine, lng_StartCol, lng_EndLine, lng_EndCol)
    If x Then
         GoTo NxtProc
    Else
     'Create Error Trap start line
         ErrTrapStartLine = "On Error goto " & strProcName & "_Error" & vbCr
    End If

    ErrHandler = vbCr & strProcName & "_Exit:" & vbCr
    
    lngProcBodyLine = objMdl.ProcBodyLine(strProcName, vbext_pk_Proc)
    
    'Set procedure start line number to Procedure Body Line Number
    lng_StartLine = lngProcBodyLine
    'calculate procedure end line to startline + procedure line count + 1
    lng_EndLine = lng_StartLine + objMdl.ProcCountLines(strProcName, vbext_pk_Proc) + 1
    
    'Save end line number for later use
    'here lng_endline may include blank lines after End Sub line
    lngProcLineCount = lng_EndLine
    
    'Instead of For...Next loop we could have used the .Find() method
    'but some how it fails to detect End Sub/End Function text
    For h = lng_StartLine To lng_EndLine
      strline = objMdl.Lines(h, 1)
      i = InStr(1, strline, "End Sub")
      If i > 0 Then
          'Format Exit Sub line
          ErrHandler = ErrHandler & "Exit Sub" & vbCr & vbCr
          lngProcLineCount = h 'take the correct end line of End Sub
          h = lng_EndLine + 1
          GoTo xit
      Else
         i = InStr(1, strline, "End Function")
         If i > 0 Then
          'Format Exit Function line
          ErrHandler = ErrHandler & "Exit Function" & vbCr & vbCr
          lngProcLineCount = h 'or take the correct endline of End Function
          h = lng_EndLine + 1
          GoTo xit
        End If
      End If
xit:
    Next

   'create Error Handler lines
   ErrHandler = ErrHandler & strProcName & "_Error:" & vbCr
   ErrHandler = ErrHandler & "MsgBox Err & " & Chr$(34) & " : " & Chr$(34) & " & "
   ErrHandler = ErrHandler & "Err.Description,," & Chr$(34) & strProcName & "()" & Chr$(34) & vbCr
   ErrHandler = ErrHandler & "Resume " & strProcName & "_exit"
 
  'Insert the Error catch start line immediately below the procedure header line
   objMdl.InsertLines lngProcBodyLine + 1, ErrTrapStartLine
   
 'Insert the Error Handler lines at the bottom of the Procedure
 'immediately above the 'End Function' or 'End Sub' line
   objMdl.InsertLines lngProcLineCount + 2, ErrHandler
     
NxtProc:
Next

strMsg = "Process Complete." & vbCr & "List of Procedures:" & vbCr
For intK = 0 To intJ
  strMsg = strMsg & "  *  " & str_ProcNames(intK) & "()" & vbCr
Next
MsgBox strMsg, , "ErrorTrap()"

ErrorTrap_Exit:
Exit Function

ErrorTrap_Error:
MsgBox Err & " : " & Err.Description, , "ErrorTrap()"
Resume ErrorTrap_Exit
End Function

Running the Function.

You can run this function from the Debug Window or from a Command Button Click Event Procedure.  Sample run on Standard Module:

ErrorTrap “Module Name”

Example-1:

ErrorTrap "Module3"

Module 3 will be scanned for Procedure Names, and each procedure is checked for the presence of existing Error Handling lines.  If the ‘On Error Goto’ statement is encountered anywhere within a procedure, then that procedure is skipped and goes to the next one to check.

To run on the Form or Report Module, use the following Syntax:

ErrorTrap "Form_FormName"

Example-2:

ErrorTrap "Form_Employees"

Example-3

ErrorTrap "Report_Orders"

When the ErrorTrap() function completes working with a module, it displays the list of procedures found in that Module. Sample run image is given below:

If you run the ErrorTrap() Program on a Form/Report that doesn’t have a VBA Module (or its Has Module Property value is set to No), then a Subscript out of Range message is displayed, and the program will be aborted.

Saving the code in the Library Database

It is better if you save this Program in your Library Database and link the Library Database to your Project.  Visit the Link: Command Button Animation for details on how to use a database as a Library Database with your own Custom Functions.

I tried to take the ErrorTrap() Function one step further to scan through the entire database Modules and insert error trap routines in all of them, saving each module immediately after changes.  But Access 2007 keeps crashing every time, and finally, I discarded the idea.  Besides, the above function gives the user more control to review the module subjected to this function for any kind of side effects.

I did the test runs on this function several times and found it ok, but field testing may be required in different environments to detect logical errors.  If you find any such errors, please give me feedback through the comment section of this page.  Review each module immediately after running this function for accuracy and use it at your own risk. 

Technorati Tags: Utility, Microsoft

• iSeries DATE in exported Data
• Change the Query Top Values property with VBA
• Creating Animated Command Buttons
• Data Change Monitoring
• Continued on Page 2 of Report

Read More

Utility for inserting VBA Error Handler Code

Introduction

So far, we have explored several examples of working with the VBA Module object’s properties and methods. We learned how to insert a subroutine into a form module using the InsertLines() method, how to upload VBA code from a text file into a form module with the AddFromFile() method, and how to generate a list of functions and subroutines from a specific module programmatically.

Now, we’ll move on to creating a very practical utility program that can automatically insert standard error-handling code into VBA functions and subroutines. Before we begin, let’s first look at a typical error-trap routine that we usually add to a subroutine. Such a routine helps us manage unexpected errors and ensures the program exits gracefully—without abruptly halting execution or inconveniencing the user during normal operations.

Sample Error Handler.

Private Sub cmdRunReport_Click()
On Error Goto cmdRunReport_Click_Error
.
.
.
.
cmdRunReport_Click_Exit:
Exit Sub

cmdRunReport_Click_Error:
MsgBox Err & " : " & Err.Description,,"cmdRunReport_Click()"
Resume cmdRunReport_Click_Exit

End Sub

The blue-colored lines in the earlier example represent the error handler code, while the dotted section holds the actual logic of the procedure. Normally, as developers, we focus first on writing the core logic of the program (the dotted section), and only later—during the finishing stage—do we add the error-handling routines. In some cases, such as file-handling programs or procedures with heavy validation checks, setting up error-trap routines gets higher priority right from the start.

Our goal here is to automate the insertion of error-handling lines at both the beginning and the end of a procedure. Every serious program benefits from structured error handling, but writing these lines manually for each subroutine or function can be time-consuming. If some of your existing code lacks proper error-handling routines, you can easily fix that now using the utility program we are about to build.

Notice in the example that lines such as _Exit: and _Error: are suffixed with the program name, e.g., cmdRunReport_Click_Exit:. These labels are automatically derived from the subroutine or function name. The first error-handling line will be inserted immediately after the procedure declaration line (e.g., Sub … or Function …), while the rest of the error-handling block will be placed at the end of the procedure.

To achieve this, we first need to extract certain details about the procedure itself:

• The starting line number of the procedure.

• The ending line number of the procedure.

• The procedure name.

With these details, we can then insert the error-trap lines in the right places. Fortunately, VBA exposes all this information through the Module object, which provides access to the required property values of any line of code within a function or subroutine.

A plan to find the specific location in the Module.

To make the approach clearer, let’s outline a simple plan for our program:

1. Locate a unique text string inside the VBA module, somewhere within the target Function or Subroutine.

   • For this step, we will use the Find() method of the Module object.

2. Identify procedure details once the search lands on the desired line inside the Function or Subroutine.

   • The Find() method not only stops on the target line but also provides important information, such as:

     • The line number of the match (modules are internally numbered line by line, including blank lines).

     • The starting column number where the search text is found.

     • The ending column number where the search text ends.

With this information in hand, we can determine the exact position of the procedure within the module, which is essential for inserting our error-handling lines at the right places.

The syntax of the Find() Method is as follows:

1. Modules(ModuleName).Find strSearchText, lngStart_Line, lngStart_Column, lngEnd_line, lngEnd_Column, [[WholeWord], [MatchCase], [PatternSearch]]

   Sample Code:

   Set mdl = Modules("Form_Employees")
   
   With mdl
     .Find “myReport”, lngStart_Line, lngStart_Column, lngEnd_line, lngEnd_Column, False
   End With

• The first parameter of the Find() method is simply the search text you want to locate.

  The next four parameters define where to begin and end the search:

  • StartLine → the line number where the search begins.

  • StartColumn → the column position on the start line.

  • EndLine → the line number where the search ends.

  • EndColumn → the column position on the end line.

  For example, suppose you want to locate the second occurrence of the text "myReport" that appears somewhere after line 25 in the module. In that case, you might set:

  lngStart_Line = 25
  lngStart_Column = 0   ' start from the beginning of the line
  

  If "myReport" is part of a command like:

  DoCmd.OpenReport "myReport"
  

  then setting lngStart_Column = 10 would be sufficient, but leaving it at 0 is perfectly fine.

  Once the search text is located, the Find() method automatically updates all four parameters with the actual positions of the match. In other words, you’ll get:

  • lngStart_Line → the line number where the match begins.

  • lngStart_Column → the exact column where the match starts.

  • lngEnd_Line → the line number where the match ends.

  • lngEnd_Column → the exact column where the match ends.

  This way, the method not only confirms the presence of the search text but also tells you exactly where it sits in the module — which is critical for inserting error-handling code at the right spots.

• When the search operation is successful, we can extract information related to that program line to use for working within that particular Function or subroutine. We will read the following information about a particular program to insert the Error Handler lines of Code at appropriate locations in the Program:

  • Get the Program Name from the ProcOfLine Property (or in the expanded form Procedure name of the Line we found through search) of the program line.

  • Get the Procedure Body Line Number from the ProcBodyLine Property. The line number on which the program Private Sub cmdRunReport_Click() starts. This line number + 1 is the location where we can insert the first line (On Error Goto label statement) of the Error Handler.

  • Get the Number of Lines in this particular procedure, we are in, from the ProcCountLines Property.  Even though this is a piece of useful information, this has some drawbacks.  If there are blank lines above the procedure Name or below the End Sub or End Function line (if it is the last procedure in a Module, then it can have blank lines at the end), they are also included in the count.  So we must take corrective action or take alternative measures to correct the values.

• Once the above information is available, we can write the Error Handler lines into String Variables and use the InsertLines method of the Module Object to place them at the beginning and the end of the procedure.

The ErrorHandler() Function.

1. Open the VBA Editing Window (ALT+F11).

2. Insert a new Standard Module.

3. Copy and paste the following VBA Code into the Module and save it:

Public Function ErrorHandler(ByVal strModuleName As String, _
                                ByVal strSearchText As String, _
                                Optional ByVal lng_StartLine As Long = 1)
On Error GoTo ErrorHandler_Error
'--------------------------------------------------------------------------------
'Program : Inserting Error Handler Lines automatically
'        : in VBA Functions or Sub-Routines
'Author  : a.p.r. pillai
'Date    : December, 2011
'Remarks : All Rights Reserved by www.msaccesstips.com
'--------------------------------------------------------------------------------
'Parameter List:
'1. strModuleName - Standard Module or Form/Report Module Name
'2. strSearchText - Text to search for within a
'   Function or Sub-Routine
'3. lng_StartLine - Text Search Start line Number, default=1
‘Remarks: Standard/Form/Report Module must be kept open before running this Code
'--------------------------------------------------------------------------------
Dim mdl As Module, lng_startCol As Long
Dim lng_endLine As Long, lng_endCol As Long, x As Boolean, w As Boolean
Dim ProcName As String, lngProcLastLine As Long
Dim ErrTrapStartLine As String, ErrHandler As String
Dim sline As Long, scol As Long, eline As Long, ecol As Long
Dim lngProcBodyLine As Long, lngProcLineCount As Long
Dim lngProcStartLine As Long, start_line As Long, end_line As Long

Set mdl = Modules(strModuleName)
lng_startCol = 1
lng_endLine = mdl.CountOfLines
lng_endCol = 255

With mdl
    .Find strSearchText, lng_StartLine, lng_startCol, lng_endLine, lng_endCol, False
End With

'lng_StartLine - line number where the text is found
'lng_StartCol  -  starting column where the text starts
'lng_EndCol    - is where the search text ends
'lng_EndLine   - end line where the text search to stop
'if search-text is found then lng_StartLine and lng_EndLine will
'point to the same line where the search-text is found
'otherwise both will be zero

If lng_StartLine > 1 Then
  'Get Procedure Name.
  'The vbext_pk_Proc system constant
  'dictates to look within a Function or Sub Routine
  'Not to consider Property-Let/Get etc.
   ProcName = mdl.ProcOfLine(lng_endLine, vbext_pk_Proc)
   
   'Get Procedure Body Line Number
   lngProcBodyLine = mdl.ProcBodyLine(ProcName, vbext_pk_Proc)
   
   'Look for existing Error trap routine, if any
   'if found abort the program
   sline = lngProcBodyLine: scol = 1: ecol = 100: eline = lng_endLine
   x = mdl.Find("On Error", sline, scol, eline, ecol)
   If x Then
      MsgBox "Error Handler already assigned, program aborted"
      Exit Function
   End If
   
 'Get Line Count of the Procedure, including
 ' blank lines immediately above the procedure name
 'and below, if the procedure is the last one in the Module
   lngProcLineCount = mdl.ProcCountLines(ProcName, vbext_pk_Proc)
   
 'Create Error Trap start line
   ErrTrapStartLine = "On Error goto " & ProcName & "_Error" & vbCr
 'Compose Error Handler lines
   ErrHandler = vbCr & ProcName & "_Exit:" & vbCr

'determine whether it is a Function procedure or a Sub-Routine
'lng_StartLine = lng_endLine:
lng_startCol = 1: lng_endCol = 100: lng_endLine = lngProcBodyLine + lngProcLineCount
'save the startline and lng_EndLine values
start_line = lng_StartLine: end_line = lng_endLine

'Check whether it is a Function Procedure or a Sub-Routine
w = mdl.Find("End Function", lng_StartLine, lng_startCol, lng_endLine, lng_endCol, False)

If w Then 'Function Procedure
   'Take correct procedure line count excluding
   'blank lines below End Sub or End Function line
   lngProcLineCount = lng_StartLine
   ErrHandler = ErrHandler & "Exit Function" & vbCr & vbCr
Else
   lng_StartLine = start_line: lng_endLine = end_line: lng_startCol = 1: lng_endCol = 100
   w = mdl.Find("End Sub", lng_StartLine, lng_startCol, lng_endLine, lng_endCol, False)
   If w Then 'Sub-Routine
     lngProcLineCount = lng_StartLine
     ErrHandler = ErrHandler & "Exit Sub" & vbCr & vbCr
   End If
End If
   'create Error Handler lines
   ErrHandler = ErrHandler & ProcName & "_Error:" & vbCr
   ErrHandler = ErrHandler & "MsgBox Err & " & Chr$(34) & " : " & Chr$(34) & " & "
   ErrHandler = ErrHandler & "Err.Description,," & Chr$(34) & ProcName & "()" & Chr$(34) & vbCr
   ErrHandler = ErrHandler & "Resume " & ProcName & "_exit"
 
  'Insert the Error catch start line immediately below the header line
   mdl.InsertLines lngProcBodyLine + 1, ErrTrapStartLine
   
 'Insert the Error Handler lines at the bottom of the Procedure
 'immediately above the 'End Function' or 'End Sub' line
   mdl.InsertLines lngProcLineCount + 2, ErrHandler
   
End If

ErrorHandler_Exit:
Exit Function

ErrorHandler_Error:
MsgBox Err & " : " & Err.Description, , "ErrorHandler()"
Resume ErrorHandler_Exit

End Function

Running the Code.

Since this program itself is a Coding aide, you must keep the target Module (Standard/Form/Report) open before running this program to insert the error-handling code segment into the target Function/subroutine.

You may call the ErrorHandler() Function from the Debug Window or from a Command Button Click Event Procedure as shown below:

'The third parameter is optional, you may omit it
ErrorHandler "Form_Employees","myReport",1

This will start searching for the text myReport from the beginning of the Employees Form Module, stop within the program where the search finds a text match, and insert the Error Handling Code lines at the beginning and end of the program.

If the text 'myReport' appears in more than one Function/Sub-Routine in the Module, then you must give the third parameter (Search starts line number) to start searching for the text beyond the area where exclusion is required. Example:

'Look for text 'myReport' from line 20 onwards only 
ErrorHandler "Form_Employees","myReport",20

When the ErrorHandler() Function is run, first, it will look for the presence of existing error handling lines starting with 'On Error', and if found, assumes that the error handling lines are already present in the Function/Sub-Routine and stops the program after displaying the following message:

‘Error Handler already assigned, program aborted.'

Comment lines are added for clarity above the program lines, explaining what happens next. 

If you find any logical errors in the program, please give me feedback through the comment section of this page. To protect from spam, we insist on joining the site before you are able to post comments.

Technorati Tags: Utility, Microsoft
Key Words: Error, Function, Sub-Routine, Form, Module

Earlier Post Link References:


• Change the Query Top Values property with VBA
• Creating Animated Command Buttons
• Data Change Monitoring
• Continued on Page 2 of Report
• Product Group Sequence with Autonumbers

Read More

Prepare a List of Procedure Names from a Module

Introduction

How to prepare a list of Procedure Names (Function or Sub-Routine Names) from a Standard Module or Class Module?

Earlier Articles.

1. Writing VBA Code.
2. Uploading Code from an external Text File into a Module.

We have seen how to insert a cmdButton_Click() Event Procedure into a Form Module using the InsertLines() method of the Module Object in the first Article and how to upload the same program from a Text File with the AddFromFile() method of the Module Object in the second Article.

In this example, we will try to prepare a list of Procedures from a Standard Module and from a Class Module.  Here, we will learn the usage of the following Properties of the Module Object:

With Modules(strModuleName):
   lng_BodyLines = .CountOfLines ‘ Total Number of lines of Code in the Module
   lng_LinesAtGobalArea = .CountOfDeclarationLines ‘Takes a count of lines in the Global declaration area
   str_ProcedureName = .ProcOfLine(LineNumber, NumberOfLines) ‘indexed list of Code lines with their Procedure names 
 End with 

The ProcOfLine (stands for Procedure Name of the current line of VBA Code) is an indexed list, and we must provide the index number of the Code line under process to check which procedure the code line belongs to.  The second index value is normally one if you are inspecting on a line-by-line basis. If you prefer to check two or more lines together, then change this number accordingly.

The Procedure Steps.

The Pseudo Code of the VBA Program is given below.

1. Take Count of Code Lines of the Module, Standard Module or Class Module.

2. Take Global Declaration Lines Count.

3. First Procedure Name  =  Count of Global declaration Lines + 1

4. Array(0) = First Procedure Name. Saves the Function/Sub-Routine name into an Array.

5. Scan through the remaining lines of code:

     A)   Check the .ProcOfLine property value for the Procedure Name of the current line of Code.

     B)  If the Procedure Name of the current line is same as of the previous line, then go to C.

              Else save the current Code line’s Procedure Name in the next element of the Array().

     C)  Move to the next Code line and if End-of-lines reached, then go to D else repeat from A.

     D)  Create a string with the Procedure Names from the saved Array.

     E)  Display the list of Procedures in a Message Box.

     F)  End of Program.

The VBA Code.

Now let us write our VBA Code for the above program.

Public Function ListOfProcs(ByVal strModuleName As String)
'------------------------------------------------------
'Courtesy : Microsoft Access
'------------------------------------------------------
Dim mdl As Module
Dim linesCount As Long, DeclLines As Long
Dim strProcName As String, lngR As Long, intJ As Integer
Dim str_ProcNames() As String, lngK As Long
Dim strMsg As String

Set mdl = Modules(strModuleName)
'Total Count of lines in the Module
linesCount = mdl.CountOfLines

'Take the count of Global declaration lines
DeclLines = mdl.CountOfDeclarationLines
lngR = 1

'The first line below the declaration lines
'is the first procedure name in the Module
strProcName = mdl.ProcOfLine(DeclLines + 1, lngR)
'Re-dimension the str_ProcNames() Array for a single element
'and save the procedure name in the Array.
intJ = 0
ReDim Preserve str_ProcNames(intJ)
str_ProcNames(intJ) = strProcName
'Determine procedure Name for each line after declaraction lines
For lngK = DeclLines + 1 To linesCount
'compare current Code-line’s procedure name with earlier line’s name
 ‘if not matching then we have encountered a new procedure name
 If strProcName <> mdl.ProcOfLine(lngK, lngR) Then
 'increment array index by one
 intJ = intJ + 1
 'get the procedure name of the current program line
 strProcName = mdl.ProcOfLine(lngK, lngR)
 'Redimension the array for a new element by
 'preserving the data of earlier elements
 ReDim Preserve str_ProcNames(intJ)
 'Save the procedure name in the array 
str_ProcNames(intJ) = strProcName
 End If 
Next lngK
 'create the list of Procedure Names from Array to display 
strMsg = "Procedures in the Module: " & strModuleName & vbCr 
For intJ = 0 To UBound(str_ProcNames)
 strMsg = strMsg & str_ProcNames(intJ) & vbCr 
Next
 MsgBox strMsg 
End Function 

Copy and paste the above program into a Standard Module and save it.

Run Code Directly from Debug Window.

To display a list of procedures from a Standard Module, display the Debug Window (Ctrl+G) and type the following command with a standard Module name as a parameter, like a sample given below, and press the Enter Key:

ListOfProcs "Utility_Local"

A Sample run output of Procedure Names in a MsgBox is shown below:

Form/Report Module Procedure Listing.

To take the list of procedures from a Form or Report Module, use the following Syntax:

ListOfProcs "Form_myFormName"

or

ListOfProcs "Report_myReportName"

Sample run output from a Form Module.

Technorati Tags: Microsoft, VBA

• Creating Animated Command Buttons
• Data Change Monitoring
• Continued on Page 2 of Report
• Product Group Sequence with Autonumbers
• Invoke Word Mail-Merge from Access 2007

Read More

VBA Module Object and Methods

Introduction.

The VBA Module Object has several interesting methods and properties.  Last week, we saw how to insert a Click Event Procedure in a Form Module with a Function. You can find this blog post here.

I don’t say that frm.Module.CreateEventProc()  The method, which we have tried, was an easy approach for writing a one-line statement in a Form Module.  But, trying something different is always exciting; programming is like exploring the unknown.  After all, it is there as part of the Application to explore and learn. 

Today, we will try an alternative and simple method for the same example we tried last week.  That is to write all the program lines in a text file and load that program directly into the Form Module.

If you have tried last week’s example, we can use the same ‘Sample’ Form for today’s trial run,  or do the following to get prepared:

Loading VBA Code from Text File.

1. Open a new Form in Design View.

2. Create a Command Button on the Detail Section of the Form.

3. While the Command Button is in the selected state, display its Property Sheet (F4 or ALT+Enter).

4. Change the Name Property Value to cmdRun and the Caption Property Value to Run Report.

5. Save the Form with the name Sample.

6. If you have last week’s Sample form, then open it in Design View.

7. Display the Form Module, remove the existing program lines, and save the Form.

8. Open Notepad, copy and paste the following program lines into Notepad, and save it as c:\windows\temp\vbaprg.txt: 

   Private Sub cmdRun_Click()
   
       DoCmd.OpenReport "myReport", acViewPreview
   
   End Sub

9. Replace the report name "myReport" with one of your own Report Names from the database.

10. Open a Standard VBA Module, copy and paste the following main program into the Standard Module:

    The LoadFromTextFile() Function.

    Public Function LoadFromTextFile()
    Dim frm As Form, frmName As String, ctrlName As String
    
    frmName = "Sample"
    'ctrlName = "cmdRun"
    
    'Open the form in design view
    DoCmd.OpenForm frmName, acDesign
    
    'define the form object
    Set frm = Forms(frmName)
    
    'call the form's Module Object's AddFromFile() method
    'to read the program from the text file
    'and insert them into the Form Module
    frm.Module.AddFromFile "c:\windows\temp\vbaprg.txt"
    
    'Save and close the form with the code
    DoCmd.Close acForm, frmName, acSaveYes
    
    'Open the form in Normal view
    DoCmd.OpenForm frmName, acNormal
    
    End Function

11. Place the cursor in the middle of the Code and press F5 to run the Code.

12. Press ALT+F11 to display the Database window with the Sample Form open.

13. Click on the Command Button to open the Report in print preview.

14. Close the Report.

15. Change the Sample Form in Design View.

16. Open the form module and check for the program lines we have loaded from the vbaprg.txt file.

Technorati Tags: Form, Module, Text File

Earlier Post Link References:

• Data Change Monitoring
• Continued on Page 2 of Report
• Product Group Sequence with Autonumbers
• Invoke Word Mail-Merge from Access 2007
• Copy-Paste Data from Excel to Access 2007

Read More

Writing VBA-Code with VBA

Introduction.

To insert an Event Procedure in a Form or Report, we will open the VBA Module and write the code manually.  If we open the Class Module through the Event Property on the Property Sheet of a Control or Form (after setting the “[Event Procedure]” value in the Event property), then the procedure’s opening and closing statements (see the example given below) will be inserted by Microsoft Access automatically. After that, we insert the necessary body lines of the procedure manually between those opening and closing statements.

Sample empty Subroutine stub of Form_Current() Event Procedure is shown below:

Private Sub Form_Current()

End Sub

Let us do it differently this time by programming a Command Button Click event procedure automatically through VBA. We are going to insert a Command Button Click Event Procedure in a Form Module with the help of a Function Write_Code().  We learned something similar through an earlier article on the topic: Creating an Animated Command Button with VBA. 

A sample Trial Run.

In this trick, the Command Button is programmed automatically to open a Report in Print Preview.  The following are the lines of VBA Code we are going to insert into the Form Module automatically:

Private Sub cmdRun_Click()

    DoCmd.OpenReport "myReport", acViewPreview

End Sub

1. Open a new blank Form in Design View.

2. Add a Command Button control on the Form.

3. While the Command button is in the selected state, display its Property Sheet (F4 or ALT+Enter).

4. Change the Name Property Value to cmdRun.

5. Change the Caption Property Value to Run Report.

6. Save and close the Form with the name frmSample.

7. Open VBA Editing Window (ALT+F11) and insert a new Standard Module. You can toggle Database and Code Window with the ALT+F11 Keyboard shortcut.

8. Copy and paste the following Code into the Standard Module and save it:

   Public Function Write_Code(ByVal frmName As String, ByVal CtrlName As String)
   Dim frm As Form, x, txt As String, ctrl As Control
   
   DoCmd.OpenForm frmName, acDesign, , , , acHidden
   Set frm = Forms(frmName)
   Set ctrl = frm.Controls(CtrlName)
   With ctrl
       If .OnClick = "" Then
          .OnClick = "[Event Procedure]"
       End If
   End With
   
   x = frm.Module.CreateEventProc("Click", ctrl.Name)
   
   txt = "DoCmd.OpenReport " & Chr$(34) & "myReport" & Chr$(34) & ", acViewPreview"
   frm.Module.InsertLines x + 1, txt
   
   DoCmd.Close acForm, frmName, acSaveYes
   DoCmd.OpenForm frmName, acNormal
   
   End Function

9. Replace the Report name "myReport" with one of your own Report names in the program line: txt = "DoCmd.OpenReport " & Chr$(34) & "myReport" & Chr$(34) & ", acViewPreview".

10. Display the Debug Window (Ctrl+G).

11. Type the following line in the Debug Window and press Enter Key:

    Write_Code "frmSample","cmdRun"

    The Form’s name "frmSample"  is passed as the first parameter to the Write_Code() Function, and the Command Button’s name "cmdRun" is passed as the second parameter.

12. Press ALT+F11 to display the Database window.  You can see that frmSample is already open in normal view after inserting the program lines in its Code Module.

13. Click on the Command Button to open your Report in Print Preview with the cmdRun_Click() Event Procedure.  You may change the Form View into Design View, open the Form Module, and check the lines of Code we have inserted in there.

At the beginning of the above program, the OnClick Event Property is checked for the presence of any programmed action, like a Macro Name, and if found empty, then it inserts the text "[Event Procedure]" in the property in preparation for writing the program lines in the VBA Module.

In the next step, the Form Module’s CreateEventProc() method is called to create the Click Event Procedure of the Command Button: cmdRun.  If you want a Double-Click Event procedure, rather than a Click() event procedure, then change the word "Click" to "dblClick".

Replace the 'DoCmd.OpenReport' statement with appropriate Code for other actions like MouseMove.

You can call the Write_Code() function from a Command Button and click Event Procedure on a Form.  Create two TextBoxes on the Form, enter the Form Name and Control Names in them respectively, and use the text box names in both parameters of the Write_Code() function.

Earlier Post Link References:

• Continued on Page 2 of the Report
• Product Group Sequence with Autonumbers
• Invoke Word Mail-Merge from Access 2007
• Copy-Paste Data from Excel to Access 2007
• User-level Access Security and Access 2007

Read More

Control SetFocus on Tab Page Click

Introduction

A question was raised in an MS Access Discussion Forum:

"When I click on a Tab Control Page, I want to set focus on a particular Text Box on that page, not on the first Text Box by default. How can I do this?"

The user attempted a solution by writing code in the Page2_Click() event procedure of the Tab Control. Specifically, they tried variations of the following lines to set focus on the "Ship City" text box located on that page:

Private Sub Page2_Click()
     Forms!frm_Main![Ship City].SetFocus 
     frm_MainMenu![Ship City].SetFocus 
     me.[Ship City].SetFocus 
End Sub 

Tab Control-based Menus.

The Tab Control is an interesting and versatile object to use on a form. Personally, I have often used it to build form-based menus, placing list boxes on its pages to organize navigation options. For example, the image below shows a sample control screen with list box–based menus arranged neatly on the tab pages.

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

The middle of the Control Form shows a list as a menu of choices.  In fact, there are fifteen different sets of menus displayed there.  They are displayed one over the other by clicking on a set of Command Buttons, shown on either side of the list box.  You can learn this trick here.

Use Change Event for Click Event.

Coming back to the topic, the first thing that you should know is that when you click on the Tab-Page Button (see the sample image below) on a Tab-Control, the Click Event procedure will not be fired; instead, it fires the Change() Event. So use the Change() Event for Tab Page Clicks.

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

The Click event of a Tab Control fires only when you click on the top border area to the right of the tab pages. This means you need two clicks: one to select the tab-page button (to display its contents), and another on the Tab Control’s border to trigger the event procedure. Clearly, this is not a convenient approach. Instead, we will take an alternative route that allows the task to be completed with a single click, using a different method.

If you have already explored the text links provided earlier, you may have picked up a few ideas—and are probably one step ahead of what I am about to explain here.

Single Click Solution.

The simple method is using the Change Event Procedure on the TabPage Click.

We will implement the following ideas for a different approach:

1. Create a separate Command Button for each Tab-Page, with one line of VBA code to make it current or visible.

2. In the Command Button Click Event Procedure, we will add one more line of code to move the focus to a particular text box in the middle of the tab page.

3. Since we have Command Buttons to display Tab Pages, we will hide the Tab-Page Buttons of the Tab-control. Optionally, change the Tab-control’s back-style design to transparent to make the tab control’s border design invisible.

Skipping the Fancy Work.

Before diving into the detailed design of the steps mentioned earlier, let me share a very simple solution—if you’re not interested in all the extra work. Simply set the Tab Index property of the text box (for example, [Ship City]) to 0.

Be careful not to confuse the Tab Index with the Tab Control or Tab Page. The Tab Index property determines the order in which the cursor moves from one control (such as a text box, combo box, or check box) to the next when you press the Tab key on the keyboard.

These values are assigned sequentially, starting from 0 up to the number of controls that have a Tab Index property on the form. Access sets these values automatically, based on the order in which you add controls to the form—either manually or through the Form Wizard. When the form opens, the control with Tab Index = 0 receives the focus by default, regardless of where it is physically placed on the form.

So, if the [Ship City] Field is not the starting point on your form and you want to make it, then do the following:

1. Open the form in design view.

2. Click on the [Ship City] field to select it.

3. Display its Property Sheet (F4 or ALT+Enter).

4. Find the Tab Index Property and change the Value to 0.  Other controls’ Tab Index Property values will be automatically changed by Access.  You must review and change them, if needed, to bring them into the desired order.

NB:  Each Tab Page is like a separate sub-form and has a separate set of Tab Index sequence numbers starting with zero on it, even if you place a different group of fields from the current record.

Showing your Professionalism.

Now that you already know the quick and easy solution, let’s explore some advanced tricks for working with Tab Control programming.

Building a database to store and retrieve data is relatively simple—almost anyone can put one together using whatever method they find easiest. That might be fine for personal use, but when presenting a database to a client or end user, appearance and usability matter a great deal. A well-designed interface not only improves user experience but also reflects your professionalism and attention to detail.

Returning to our topic, let’s take a closer look at the first three steps of the alternative approach we outlined earlier. For this demonstration, we’ll design a sample form with a Tab Control containing three pages, each holding different groups of information from the Orders table in the Northwind sample database. You can use any table of your choice to create a similar form. On the left side of the Tab Control, add three command buttons to support our trial run.

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

The Design Task

1. Click on the first Command Button to select it.

   • Display its Property Sheet (F4 or ALT+Enter keys).

   • Change the Name Property Value to cmdOrder and the Caption Property Value to Order Details.

   • Click on the Event Tab of the Property  Sheet, select On Click Event property, and select [Event Procedure] from the drop-down control.

   • Click on the Build ( . . . ) Button to open the Form’s VBA Module with an empty sub-routine stub.

   • Copy and paste the following lines of Code, overwriting the existing lines, or simply copy the middle line alone and paste it between the sub-routine opening and closing lines, as shown below.

     Private Sub cmdOrder_Click()
       Me.TabCtl0.Pages(0).SetFocus
     End Sub

2. Similarly, change the middle Command Button’s Name Property Value to cmdShipper and Caption Property Value to Shipper Details.

   • Follow the last three steps mentioned above to copy-paste the following code for the middle Command Button Click Event Procedure:

     Private Sub cmdShipper_Click()
       Me.TabCtl0.Pages(1).SetFocus
       Me.Ship_City.SetFocus
     End Sub

     In the first line of code, we changed the Tab page reference from Page(0) to Page(1), which points to the second page of the Tab Control. We then added one more line:

     Me.Ship_City.SetFocus
     

     This moves the insertion point directly to the Ship City field, regardless of its physical placement on the form. As a result, a single click on the command button not only switches to the second tab page but also sets the focus on the Ship City field.

     Notice that we are addressing the control (Me.Ship_City.SetFocus) as though it were placed directly on the form surface, rather than treating it as a child control of the Tab Page. Keep in mind that each group of fields on a Tab Page has its own Tab Index sequence, starting from 0, which determines how the cursor moves among controls on that page.

     If you prefer, you can also reference the control explicitly as a child of the Tab Page, like this:

     Me.TabCtl0.Pages(1).Controls("Ship City").SetFocus
     

     This approach is equally valid.

3. Change the last Command Button’s Name Property Value to cmdPayment and Caption Property Value Payment Details.

   • Copy-paste the following lines of code for the last Command Button Click Event Procedure, as you did in the earlier two cases:

     Private Sub cmdPayment_Click()
        Me.TabCtl0.Pages(2).SetFocus
     End Sub

4. Save the Form and open it in the normal view. When you open the form, by default, Page1  of the tab control will be active.

5. Click on the middle Command Button. You can see the second page of the Tab Control become active, and the control "Ship City" field is in focus now.

6. Click on the Payment Details Command Button to select the third page. You may try all the command buttons repeatedly to get the feel of their usage.

   Since our command buttons took over the function of Tab-Pages of the Tab Control Object, we don't need the Tab Control Page buttons above, and we will remove them.

7. Change the Form Mode in Design View.

8. Click on the Tab Control by clicking on the right side of the Page3 button.

9. Display the Property Sheet (F4).

10. Click on the All tab of the property sheet and set the Style Property Value to None from the drop-down list.

The Demo Run.

If you open the form in Normal View, the Tab Control will appear as shown in the image below, without the tab page indicators. Clicking the command buttons will still switch the pages, just as before.

You can take this a step further by “hiding” the Tab Control’s identity marks entirely. To do this, set the

Back Style property of the Tab Control to Transparent. This creates a clean, seamless interface while retaining full tab functionality—a simple but impressive “magic trick.”

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

1. Change the form to design view (if the form is in normal view) and change the Back Style Property Value to Transparent.

2. Save the Form and open it in Normal View.

   No sign of the Tab Control now, except for displaying the controls on the first Tab Page with their values and labels. Click on the Command Buttons one after the other. You will find that the data fields and their labels appear from nowhere, occupying the same area every time, like magic.

• Product Group Sequence with Autonumbers
• Invoke Word Mail-Merge from Access 2007
• Copy-Paste Data from Excel to Access 2007
• User-level Access Security and Access 2007
• Macro and Temporary Variables

Read More

Adding Data directly into External Databases

Introduction.

The Back-End/Front-End database design is a common practice in Microsoft Access. The back end may consist of Access, dBase, SQL Server, Excel, or Paradox databases, with their tables linked to the front end (FE). Once the tables are linked, they behave just like native tables within Access—you can design queries, forms, and reports on them, and manage everything from the FE.

But what if we want to work without directly linking these tables to the FE? For example, can we create a query in the current database that uses an external table (not linked) from another Access database?

We have already explored this topic earlier and confirmed that it is indeed possible. You can check the following blog posts for practical demonstrations of this technique applied to different types of external data sources:

1. Opening External Data Sources
2. Opening dBase Files directly
3. Opening an Excel Database directly
4. Display Excel Values directly on Form
5. Database Connection String Property
6. Source ConnectStr Property and ODBC
7. Link External Tables with VBA
8. Lost Links of External Tables
9. MS-Access Live data in Excel
10. MS-Access Live data in Excel-2

As you can see from the list above, methods 1 through 6 show different ways of bringing external data into Access without keeping the tables permanently linked to the database.

When working with dBase or FoxPro tables, the folder path where the table resides is treated as the database name.

If you’ve already read the second article, Opening dBase Files Directly, you should now have a good idea of what we are about to explore—namely, how to send output data into external databases without linking them to Microsoft Access.

Sample SQL for External dBase Table.

Before we dive into output operations, let’s take a closer look at a sample SQL statement that retrieves data from a dBase table through a query—without linking the table to the Access database.

Note: If you don’t already have a dBase table to test with, you can easily create one by exporting one or more of your Access tables into a separate folder on your disk. You don’t need to install a dBase application on your machine—the required ODBC driver files are already included with Microsoft Office.

SELECT Employees.* FROM Employees IN 'C:\MydBase'[DBASE IV;];

The SELECT query shown above will return all records from the Employees.dbf table located in the dBase database folder C:\MydBase. The text [DBASE IV;] specifies the database type and version. The clause

IN 'C:\MydBase' [DBASE IV;];

creates a direct connection to the Employees.dbf table—without establishing a permanent physical link. In other words, the data from Employees.dbf is accessible only through this query and not as a linked table in Access.

Up to this point, we have been focusing on how to bring data from external databases into Access without linking them. Now, let’s take it a step further and explore how to update or add data to these external databases.

Updating Data into an External dBase Table.

A sample SQL that updates an external dBase Table is given below:

UPDATE Products IN 'C:\MydBase'[DBASE 5.0;] SET Products.TARGET_LEV = 45
WHERE (((Products.TARGET_LEV)=40) AND ((Products.REORDER_LE)=10));

With the above SQL, we are updating the Products stock Target level to 45 from 40, for items with Re-order Level (Minimum Stock Level) is 10, and the current stock quantity target level is 40.

Appending Data into an External dBase Table.

Let us append some data from the Products_Tmp Table from the current MS-Access Database to the Products.dbf Table of C:\MydBase dBase Database.  The sample SQL is given below:

INSERT INTO Products
  SELECT Products_Tmp.*
  FROM Products_Tmp IN 'C:\MydBase'[DBASE 5.0;];

IN Clause and Query Property Setting

Source Database and Source Connect Str Properties.

Let us examine the Property Sheet of one of the above Queries to check for any indication about whether the SQL IN Clause setting is in there or not.

1. Open one of the above Queries in Design View.

2. Display the Property Sheet of the Query. Press F4 or ALT+Enter to display the property sheet and make sure that it is the Query Property Sheet. Under the Title of the Property Sheet, there will be a description: Selection Type Query Property.

3. You may click on an empty area to the right of the Table on the Query Design surface to make sure that the Property Sheet displayed is the Query's Property Sheet, not the Table or Query Column Property Sheet. Check the sample image given below.

   

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

4. Check the Source Database and Source Connect Str Property Values. If you find it difficult to memorize the correct syntax of the IN Clause in SQL, then you can populate the respective values in these properties of the Query as shown. This will automatically insert the Connection String with the correct syntax in the SQL.

You can find the correct syntax for Access, Excel, Paradox, and ODBC connection strings for IBM iSeries machines, SQL Server, etc., from the above-quoted Articles.

Caution:

While the above methods offer convenient ways to work with external tables without permanently linking them to an Access database, relying on this approach too heavily can cause problems down the line if not managed carefully. To avoid confusion or data management issues, it is essential to maintain proper documentation of these queries and keep them safely stored for future reference.

Constant Location Reference Issues?

Let’s consider the case of an external Microsoft Access database. The SQL example below demonstrates how to append data directly into the Employees table of another Access database located on a LAN server. This type of operation is commonly performed on a scheduled basis—daily, weekly, or at other regular intervals.

INSERT INTO Employees IN 'T:\Sales\mdbFolder\Database1.accdb' 
SELECT Employees_tmp.*
FROM Employees_tmp;

Everything works smoothly, and over time, you may even forget about this particular query—or others like it. After six months, suppose you decide to shift or copy the databases from their current folder into a new location on the server (say, T:\Export\mdbFolder), while leaving a copy in the old folder as a backup. The database seems to function perfectly in the new location, no errors appear, and the users are satisfied.

However, some of your queries still contain the original connection strings hard-coded in their SQL statements. Since these were never updated to point to the new folder, the queries continue working against the old database copy in ...\Sales..., instead of the intended ...\Export... location. When users eventually report data discrepancies, it may not immediately occur to you that the culprit is the IN clause of your SQL. By the time you discover the oversight, you may already have wasted hours—or even days—troubleshooting the wrong problem.

Despite this drawback, the method remains useful when external databases are needed only occasionally. If the frequency of use is minimal, it is often better than keeping the external tables permanently attached to the front-end.

• Invoke Word Mail-Merge from Access 2007
• Copy-Paste Data from Excel to Access 2007
• User-level Access Security and Access 2007
• Macro and Temporary Variables
• Easy-read Reports

Read More

Users and Groups Listing from Workgroup Information File

Introduction

How about taking a printout of Users and Groups from the active Workgroup Information File (the Microsoft Access Security File)?

This applies only to users of Microsoft Access 2003 or earlier databases that were implemented with Access Security. Such databases can still be opened and used in Access 2007 or Access 2010 without conversion.

To link to the Workgroup Information File (.mdw) from Access 2007, you can run the Workgroup Administrator program. For step-by-step instructions, please refer to the article: Running Workgroup Admin Program from Access 2007.

On a personal note, I am not particularly fond of the frequent version changes in Access. While new features are always exciting to explore, they often come at the cost of what we have already learned and implemented. For example, I still wonder why the familiar Menus and Toolbars were reorganized and rebranded as the Ribbon in Access 2007. Such changes often add confusion and waste time, forcing users to relearn tasks they could once do quickly. By the time users finally become comfortable with the new interface, a newer version appears—and the cycle starts all over again.

Upgrades are always welcome—provided they correct the drawbacks and bugs of earlier versions and implement genuine enhancements that users can easily locate and benefit from.

Coming back to the topic of printing Users and Groups from the active Workgroup Information File, Microsoft Access already provides a built-in option. The only limitation is that the listing is sent directly to the printer, which may not be ideal.

To use this option in Access 2003 or earlier:

Select Tools → Security → Users and Group Accounts → Users → Print Users and Groups.

In Access 2007 (with an Access 2003 or earlier database open):

Go to Database Tools → Administer → Users and Permissions → User and Group Accounts → Users → Print Users and Groups.

But why waste stationery when there’s a more flexible option? You can generate a list of Users and Groups and save it to a text file on your disk instead. Below is a simple VBA procedure you can use for this purpose. Copy and paste the following code into a Standard Module, then save and run it:

Database UsersList() Function.

Public Function UsersList() 
'------------------------------------------------------------- 
'Author : a.p.r. pillai 
'Date   : Oct 2011 
'Rights : All Rights Reserved by www.msaccesstips.com 
'Remarks: Creates a list of Users & UserGroups from the 
'       : active Workgroup Information File and saves 
'       : the list into a text file: UserGroup.txt 
'       : in the current database path 
'------------------------------------------------------------- 
Dim wsp As Workspace, grp As Group, usr As User 
Dim fs As Object, cp_path As String 
Dim a, txt 
Const ten As Integer = 10 

cp_path = CurrentProject.Path 
Set wsp = DBEngine.Workspaces(0) 
'Create a Text file: UserGroup.txt with FileSystemObject 
Set fs = CreateObject("Scripting.FileSystemObject") 
Set a = fs.CreateTextFile(cp_path & "\UserGroup.txt", True) 

'Write headings 
a.writeline "SYSTEM-DEFAULT GROUPS" 
a.writeline "-----------------------" 
a.writeline ("User-Groups  User-Names") 
a.writeline ("-----------  ----------") 

'List Default Admins & Users Group First 
For Each grp In wsp.Groups 
   txt = grp.Name 
   If txt = "Admins" Or txt = "Users" Then 
     a.writeline txt & Space(ten - Len(grp.Name)) & Space(3) & "..." 
     For Each usr In grp.Users 
        txt = Space(Len(grp.Name) + (ten - Len(grp.Name))) & Space(3) & usr.Name 
        a.writeline txt 
     Next: a.writeline crlf 
   End If 
Next 

'Groups, except Default Admins & Users 
a.writeline "ADMINISTRATOR-DEFINED GROUPS" 
a.writeline "----------------------------" 
a.writeline ("User-Groups  User-Names") 
a.writeline ("-----------  ----------") 

For Each grp In wsp.Groups 
   txt = grp.Name 
   If txt = "Admins" Or txt = "Users" Then 
      GoTo nextitem 
   Else 
      a.writeline txt & Space(ten - Len(grp.Name)) & Space(3) & "..." 
      For Each usr In grp.Users 
         txt = Space(Len(grp.Name) + (ten - Len(grp.Name))) & Space(3) & usr.Name 
         a.writeline txt 
      Next: a.writeline crlf 
   End If 
nextitem: 
Next 
a.Close 
'Open UserGroup.txt file with the list User list. 
Call Shell("Notepad.exe " & CurrentProject.Path & "\UserGroup.txt", vbNormalFocus) 

End Function

Running UsersList() Function

You can run this program either directly from the Immediate Window (Debug Window) or by calling it from the Click event of a Command Button on a Form.

When executed, the program will create a text file named UserGroup.txt in the same folder where the current database resides. Once the file is generated, it will automatically open in Windows Notepad for review.

Keep in mind that each time you run this program, the existing file will be overwritten. If you would like to preserve previous listings, remember to rename or move the file to a safe location before running the procedure again.

The result of a sample run of the program is given below:

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

If you need a printout of Users and Groups, then you may print it from the text file.

• Copy-Paste Data from Excel to Access 2007
• User-level Access Security and Access 2007
• Macro and Temporary Variables
• Easy-read Reports
• Top N Records in Query

Read More