Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Assigning Module Level Error Trap Routines

Introduction.

Last week I introduced a Function to insert Error Handling lines into a VBA Function or Sub-Routine automatically. Readers commented saying that it is a good utility, but its usage is somewhat cumbersome.

Before running that function the user has to spot some text to search for and then run the function with that search text as a parameter. The utility function searches for the text,.Find() method of the Module Object, to find the search text and select that line within the target Function/Sub-Routine.  Based on the selected line, we can read other details of the function/subroutine, like the total number of lines within that function/subroutine, function header line number, and function ending line number.  These parameters must be available to insert the error handling lines in appropriate locations within the procedure.

If there are several functions or sub-routines to insert error handling lines, then this method takes some time to cover all of them, one by one.

Here, we will look at a different version of the same function that scans through the entire Module and inserts error handling lines in all of the Functions/Sub-Routines in one go.

Before that, Links to the earlier Articles are given below, just in case you would like to take a look at the simple methods of the Module Object we tried earlier:

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"

Module3 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 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 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, Access2007 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:
Share:

Utility for inserting VBA Error Handler Code

Introduction

We have tried a few examples of working with VBA Module Object Properties and Methods. We have learned how to insert a Sub-Routine into a Form Module with expression.InsertLines() method of Module Object with Program. We have also seen how to upload VBA Programs from a Text File into a Form Module with expression.AddFromFile() method of Module Object. We have prepared a list of Functions and Sub-routines from a particular Module with the program.

Now, we are going to write a very useful Utility Program for inserting general Error Handler Code Lines into VBA Functions or Sub-Routines automatically. Let us take a look at the Error Trap Routine that we normally write in a Sub-Routine to take care of unexpected errors and to exit the program gracefully, without stopping the code and giving trouble to the User in the middle of 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 are the Error Handler lines and the dotted area will hold the actual program.  Normally, we concentrate on writing code for the actual action we intended to execute within the procedure (the dotted line area) and the Error Handler part can wait for the later finishing touches stage.  File handling programs or areas where validation checks are performed gets more attention in setting up error trap routines.

Our idea is to insert the Error Handler lines at the beginning and end of the program automatically.  Any serious program that you will write needs these lines of code and writing them manually everywhere will take some of your valuable time in a busy schedule.  If you left out some of your programs without adding the Error Handler lines earlier, you can add them now with the Utility Program that we are going to write.

As you can see in the above code the lines suffixed with _Exit:, _Error: etc., have the program name attached to them like cmdRunReport_Click_Exit: and these values are taken from the Sub-Routine or Function Names.  The first line of the error handler will be inserted immediately after the Program Name and other lines at the end of the Program.  So we must know a few details about the program before we are able to insert the Error Trap Lines into appropriate locations in the program.  For that, we must address a few Property Values of any line of code located within the Function or Sub-Routine and get the property values from the Module Object.

A plan to find the specific location in the Module.

To make it more clear, let us draw out a plan for our program as below:

  1. First, search for some unique text within the VBA Module, located within our target Function or Sub-Routine.  For this, we can use the Find() method of the Module Object.

  2. Once the search stops on the target line within the Function or Sub-Routine we can read several details of the program we are in now.  The Find() method not only finds the target line of our program with the search text, but also the program line number within the Module (all the lines within a Module are sequentially numbered including blank lines), the Column Number at which the search text starts, which column the search text ends, etc.  The Find() method Syntax is as given below:

    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 is the search text to find.

    • The next four parameters tell where to start and where to stop looking for the search text.  For example, if you want to search for the second occurrence of the text “myReport” located somewhere beyond line number 25 then you will set lngStart_Line=25.  If “myReport” is in Docmd.OpenReport “myReport” then the lngStart_Column value can be about 10 or leave it at 0 to start searching from the beginning of the line.  Once the search text is located by the Find method all four variables will be loaded with the search text-related values as below:

      • The lngStart_Line = line number on which the search text is located.

      • lngStart_Column = Column Number (or the first character of the search text starts on which character position from left)

      • lngEnd_Line = Line on which the Search Text found and search stopped.

      • lngEnd_Column = Column on which the search text ends.
    • Once the search text is located on a line the lngStart_Line and lngEnd_line will refer to the same program line on which the search text is located.  The start and end column values will be loaded into the lngStart_Column and lngEnd_Column variables.

    • When the search operation is successful, we can extract information related to that program line to use for working within that particular Function or Sub-Routine. We will read the following information of 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 Error Handler.

      • Get the Number of Lines in this particular procedure, we are in, from 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 take correct 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/Sub-Routine.

    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, stops within the program where the search finds a text match and inserts 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 wherever 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 error 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:

    Earlier Post Link References:

Share:

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 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 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: ,
Share:

VBA Module Object and Methods

Introduction.

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 the frm.Module.CreateEventProc() 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 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:

Earlier Post Link References:

Share:

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 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.  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 Command Button’s name "cmdRun" is 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 Macro Name, and if found empty, then 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:

Share:

Control SetFocus on Tab Page Click

Introduction

"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 on the Tab page, how?"

The above question was raised in an MS-Access Discussion Forum on the Net.  The user tried a similar set of the sample code given below (with one of the three lines, inter-changeably) on the Page2_Click() Event procedure to move the focus to the "Ship City" Field on the tab page, but none of those lines worked, why?:

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.

Tab-Control is an interesting piece of Object to use on a Form. I have used this control mainly for building form-based Menus with List Boxes on them. You can find a sample image of a Control Screen below with List box-based Menus on it:

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.

The Click event fires only when you click on the top border area, to the right of the Tab Pages. So you need two clicks, one click on the Tab-Page button to make that page content visible followed by another click on the top border of Tab Control to run the Event Procedure so that whatever Code you put in the procedure is executed.  This is not an attractive proposition, but we will take an alternative route to do it with a single click, using a different approach.

If you have already visited the above text links that I have suggested, then you are armed with a few ideas and you are already ahead of me on what I am going to say 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 going into the detailed design of the above steps I can give you a very simple solution if you are not interested to go into all the fancy work. Set the Tab Index Property Value of the Text box (like [Ship City]) to 0 (zero).

Don’t mix up Tab Index with Tab Control and Tab Page.  When you tap on the Tab-Key on the Keyboard the cursor jumps from one control (Text Box, Combo box, Check-box, etc.) to the next based on the sequence of the TabIndex Property Value.

This value is sequentially numbered from 0 to the number of such controls with TabIndex Property on a Form.  This is automatically set sequentially in the order in which you place the controls on the form at design time manually or through Form Wizards.  When a Form opens the control with Tab Index value 0 will get focused by default, irrespective of its physical placement on the form.

So, if the [Ship City] Field is not the starting point on your form and you want to make it so 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 in 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 them, even if you place a different group of fields of the current record.

Showing your Professionalism.

Now, that you are already armed with an easy solution, you may be interested to learn some fancy tricks on the Tab Control programming too.

A database can be filled with data very easily.  Any Tom, Dick, and Harry can build a database to do that, with whatever easy method available to him.  If it is for his own use then no issues.  But, when it is presented to a Client/User it should have an impressive appearance and should be user-friendly.  Besides that, it gives you a chance to advertise your professionalism in your work too.

Coming back to the topic, we will now take the first three steps of action, we have defined above, for a different approach to solve the problem.  A sample design of a Form, with a Tab Control with three pages to hold different groups of information from the Orders Table of Northwind sample database.  You may use any table you like to design a similar Form, with three Command Buttons on the left side of the Tab Control for a trial run:


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, over-writing 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 code, we have changed the Tab page reference Page(0) to Page(1) refers to the second page of the Tab Control.  Here we have added one more line Me.Ship_City.SetFocus to move the insertion point to the “Ship City” field, wherever it is physically placed.  So, with one click on the Command Button will select the second page of the Tab Control and will set the focus on the Ship City field too.

      We are addressing the control (Me.Ship_City.SetFocus) as if it is directly placed on the Form surface rather than as a child control on the Tab Page.  Remember, each group of fields on each Tab Page has a separate set of Tab Index sequence numbers starting from 0, to move the cursor around on that page.

      So, if you set the reference of the “Ship City” field as a child control on Tab Page2, like Me.TabCtl0.Pages(1).Controls("Ship City").SetFocus, it 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 look like the image given below, without the Tab Page indicators. Clicking on the Command Buttons will turn the Pages, as before. You can do a magic trick by completely hiding the Tab Control's identity marks by setting the Back Style Property Value Transparent.

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

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

    No sign of the Tab Control now, except 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.

Share:

Adding Data directly into External Databases

Introduction.

 The Back-End, Front-End database designs are common in MS-Access.  The back-end can be MS-Access, dBase, SQL Server, Excel, or Paradox databases with their linked Tables.  Once the tables are linked they function as if they are the native tables of the Access Database.  You can design Queries, Forms, Report on them and manage them from FE.

But, can we manage without directly linking them to the FE?  For example; can we create a Query in the current database using an external Table (not a linked table) from another MS-Access database?

This topic we have already discussed earlier proved that it is possible.  Check the following Blog Posts to learn this trick on different types of external data sources:

  1. Opening External Data Sources
  2. Opening dBase Files directly
  3. Opening 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 above list that serial numbers 1 to 6 methods are used for bringing external data into Access in different ways without keeping them linked to the Access Database.  When working with dBase or FoxPro Tables, the path to the Folder, where the dBase/FoxPro Table is located stands as the database name.

If you have gone through the second Article Opening dBase Files directly, then you already know what we are going to explore here and have a general idea by now as to how to send output data into external databases without linking them to MS-Access.

Sample SQL for External dBase Table.

Before going into that, let us take a closer look at the sample SQL that brings in data from a dBase Table through a Query, without linking the table into the MS-Access database.

NB:  If you don’t have a dBase Table, to try out these examples, you can create dBase Tables by exporting one or more of your Access Tables into a separate folder on your disk.  You don’t have to install a dBase Application on your machine. Necessary ODBC Driver Files are already installed by MS Office on your machine.

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

The SELECT Query will return the entire records from the Employees.dbf Table from the dBase database ‘C:\MydBase’.  The text  [DBASE IV;] is the database type and version indicator.  The SQL IN 'C:\MydBase'[DBASE IV;]; clause creates a direct link to the Employees.dbf Table without a physical link.  That means the Employees.dbf data are available to other processes through this query only.

Throughout the above articles, we were discussing bringing data from external databases, without keeping them linked to Access.  This time we will explore how to update or add data to the external databases.

Updating Data into 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 External dBase Table.

Let us append some data from 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 Query's Property Sheet, not the Table or Query Column Property Sheet. Check the sample image given below.

  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 the 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.

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

Caution:

Even though the above methods provide some convenient way to manage external tables, without keeping them permanently linked to the MS-Access database, extensive use of this method can lead to issues at a later stage if you are not careful. It is important that you maintain some form of documentation of these Queries for safekeeping.

Constant Location Reference Issues?

Let us take the example of an external MS-Access database itself. The SQL given below appends some data into the Employees table in another Microsoft Access database on LAN Server directly. This is a routine process done daily or weekly etc.

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

Everything works just fine and you forgot about this specific Query or other Queries similar to this one. After about six month’s time you thought of shifting or copying the databases from the current location into another Folder on the Server (say T:\Export\mdbFolder), leaving a copy in the old folder as a backup, and installed in the new . . .\Export\ folder. Everything was found to work OK without triggering any error in the new location and the Users are also happy.

Your database has a few Queries with the above Connection Strings in their SQL, which never crossed your mind of attending to them and changing the SQL to point them correctly to the databases to the new location. The Queries will keep on servicing the Table in the old location . . .\Sales\. . . , instead of the Table in . . .\Export\. . . Location. The data missing problem when reported by the user may not trigger the Query IN Clause button in your mind immediately and you may be pulling your hair to find out what went wrong, wasting hours or days, till you arrive at the accident spot.

In spite of these drawbacks, it is a good way to use the external databases when needed only, if the frequency is minimal, rather than keeping them always attached to the FE. 

Share:

Users and Groups Listing from Workgroup Information File

Introduction

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

This is only for users of Microsoft Access 2003 or earlier version databases implemented with Microsoft Access Security. You can use earlier version databases in Access2007 or in Access 2010 without converting them.

You can run the Workgroup Administrator program from Access2007 to link to the Workgroup Information File (.mdw). Please refer to the Article: Running Workgroup Admin Program from Access2007 for details.

Frankly speaking, I am not happy with the idea of frequent changes in versions, irrespective of what advantages they provide. It is true that I am really excited to learn new features, but not at the cost of what we have already learned and implemented. I still wonder why the Menus and Toolbars are jumbled around and presented with fancy names like Ribbons in Access2007. These kinds of changes will only add to the confusion and waste of time to find out things we are already familiar with in the earlier versions.  By the time users are out of the woods with the new changes, they are slapped with a new version and start all over again looking for things, which were in easy reach earlier.

Upgrades are good if drawbacks and bugs of earlier Versions are corrected and implemented with real enhancements, which users can find them easily. 

Coming back to the topic of taking print out of Users and Groups, there is an option already available in Access.  The only problem is that the listing will be dumped directly into the Printer.

To use this option select Tools - - > Security - - > Users and Group Accounts - - > Users - - > Print Users and Groups.  

In Microsoft Access2007, open a database of Access2003 or earlier version first, then select Database Tools - - > Administer - - > Users and Permissions - - > User and Group Accounts . . . - - > Users - - > Print Users and Groups.

But, why you should waste stationery when you have a better option?  You can create a list of Users and Groups in a Text file and save it on your disk with the VBA Program given below.  Copy and paste the following Code into a Standard Module and save 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 the Code directly from the Debug Window or from a Command Button Click on a Form.  The Text File will be created in the current database folder with the name UserGroup.txt and it will be opened in Windows Notepad automatically. 

Whenever you run this program the earlier file will be over-written.  You may rename the file, for the safe-keep.

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

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

Share:

Access Security Key Diagram

Introduction - Access 2003.

As you are aware implementing Microsoft Access Security is a serious business. Even though this has been deprecated from Access2007 and later versions, thousands of Access Developers are still using this feature.  There are several pages of MS-Access Help text explaining the complexities of this feature and it is difficult to visualize how all of them fit together to form the security key.

I made an attempt here to put the main elements of Microsoft Access Security elements together into the form of a picture so that we will get a general idea of what all components are involved and where they are all kept for implementing Microsoft Access Security.  

It is important to regulate Users' roles and maintain the security of data, the integrity of various objects, and the VBA Code.

We already have several Articles discussing Microsoft Access Security. You can access these articles from the Security Sub-Menu from the Main Menu Bar on this site.

Microsoft Access Security - Two Sections.

  1. The first part of the Security elements (Workgroup File Id elements and User/Group Names, Personal IDs, and Passwords), resides within the Workgroup Information File. 
  2. Object-level access rights information that resides within the Database forms the second part.

When both parts are combined, consisting of fourteen pieces of security elements, becomes the full security key of a User.  See the diagram given below:


Workgroup FileID.

The first three elements: Workgroup Name, Organization & Workgroup Id form the unique Workgroup Information File identification elements. You must keep this information in a safe place, after creating the Workgroup Information File.  If you somehow got lost this file you must give this specific information to create this Workgroup Information File again.  MS-Access distinguishes one Workgroup Information File from the other using this unique information.

User Specific Credentials.

The next three elements: User or Group Name, Personal ID & Password, are User-specific information.  Group-Account have only Group Names and Personal IDs, no passwords.  It is very important that you keep a record of the User/Group Names and their Personal ID information in a safe place.

The Group Security Account is only a means of organizing Users into different groups so that their access privileges can be assigned at the Group level.  Users inherit the access privileges assigned to the Users’ Group Account when they are added to the Group.

When you create a new Workgroup Information File, by default, there will be only one User Account: Admin and two Group Accounts: Admins & Users.  The Admin User account is a member of the Admins & Users Group Accounts.  You cannot delete these two Group Accounts and any new User Account, you create will be a member of the Users Group Account by default.  You cannot delete the Admin User Account either, but it can be removed from the Admins Group Account as part of a security measure.

Members of the Admins Group have the full administrative power to assign permissions to Objects and transfer ownership of objects (except the Database Object) to any other User/Group accounts.

Database Owner.

Here, one important aspect you have to keep in mind is that the Owner of the Database (the User who created the Database)/Object has equal privileges of an Administrator, a member of the Admins Group Account.  The owner of an object can assign permissions, like an administrator, for other Users or transfer his ownership of the object to another User.  

Ownership of a Database Object cannot be transferred to anybody.  But, one who likes to take ownership of a Database, must create a new database and import all the objects (if, he has enough privileges to do that) into the new Database.

Share:

Updating Sub-Form Recordset from Main Form

Introduction

Sub-Form on the Main Form is the common design that we follow to display several related records on the record on the Main Form. Order Detail records related to Orders, Bank accounts and their transactions, Mark-List and Student's Id, and in short almost all databases have this kind of one-to-many relationship, and records are displayed in this way to get a quick view of maximum information.

When we open a form with a Table or Query as Record Source the Form when loaded opens a parallel record set in memory with unique bookmarking of each record. We can search through this virtual record set in memory without directly touching the actual Table or Query. But, when we add or update a record in this virtual record set that change is saved in the actual table. We call this virtual record set the RecordsetClone of the Form.

Working with Recordset Clone.

The sample VBA Code given below shows how to address the Form's RecordsetClone to find a record based on some criteria.

Private Sub FindPID_Click()
'Find Record matching Product ID
Dim m_find, rst As Recordset

'validation check of search key value
m_find = Me![xFind]
If IsNull(m_find) Then
   Me.FilterOn = False
   Exit Sub
End If

'validation check of search key value
If Val(m_find) = 0 Then
  MsgBox "Give Product ID Number..!"
   Exit Sub
End If

'Find a record that matches the ProductID
'and make that record current using the recordset bookmark.
If Val(m_find) > 0 Then
   Set rst = Me.RecordsetClone 'declare the recordset of the form
   rst.FindFirst "ProductID = " & m_find
   If Not rst.NoMatch Then '<if record found then make that record current
      Me.Bookmark = rst.Bookmark
   End If
   rst.Close
End If

End Sub

We have a Blog Post on Data search and filter on Form through the above code. If you would like to take a look at it then click here.

In the above example, we were using the RecordsetClone Object of the Main Form on the main form module itself. But how do we address the RecordsetClone Object of the Sub-Form, from Main-Form, to update the current record set on the Sub-Form? Here, what we have to keep in mind is that the records, which appear in the Sub-Form Datasheet View are related to the current record on the Main Form and only those records can be accessed for whatever operation we planned to do with them. Not all the records of the Record Source Table/Query.

On the Main Form, all records of the Record Source Table/Query can be accessed through the RecordsetClone object, for search or update operations. But, the RecordsetClone of the sub-form will have only those records displayed on the sub-form, related to the current record on the main form.

Accessing Sub-Form Recordset from Main Form.

Let us try an example to learn how to access the sub-form record set from the main form and update records.

  1. Import the following two Tables from the Northwind (or Northwind.mdb) database:

    • Orders
    • Order Details

    Create a New Field for testing purposes.

  2. Open the Order Details Table in the design view.

  3. Add a new field: Sale Value with Data Type Number and Field Size Double.

  4. Save the Order Details Table with the new field.

  5. Design the Main form for Orders Table in column format.

    If you have used the Form Wizard and created a Split Form then delete the Datasheet sub-form or table (Access2007). Display the Property Sheet of the Form, find the Default View property, and change the Split Form value in Single Form.

    Create a Sub-Form.

  6. Design a Datasheet Sub-Form for Order Details Table.

  7. Expand the Footer of the Sub-Form and create a Text box there.

  8. Change the Name Property value to TotSale.

  9. Write the expression =Sum([Sale Value]) in the Control Source property.

  10. Save and close the Form with the name: Order Details.

  11. Insert the Order Details sub-form in the Detail Section of the Orders Form below the Orders Form controls. See the image given below:

    Sub-Form Link with the Main form.

  12. While the Sub-Form is still in the selected state display its Property Sheet (F4).

  13. Set the Link Master Field property value to [Order ID].

  14. Change the Link Child Field property value to [Order ID].

  15. Add a Command Button above the sub-form as shown on the design above

    .
  16. Display the Property Sheet of the Command Button (F4 or Alt+Enter.

  17. Change the Name property value to cmdUpdate

  18. Select the On Click Event property and select [Event Procedure] from the drop-down list.

  19. Click on the Build (. . .) Button at the right end of the property to open the VBA Module of the Form.

  20. Copy and Paste the following VBA Code into the VBA Module overwriting the skeleton lines of the Sub-Routine there.

    Sub-form Module Code.

    Private Sub cmdUpdate_Click()
    Dim rst As dao.Recordset
    Dim m_UnitPrice As Double
    Dim m_Discount As Double
    Dim m_Quantity As Long
    Dim m_SaleValue As Double
    
    'Address the recordset on the Sub-Form [Order Details]
    Set rst = [Order Details].Form.RecordsetClone
    rst.MoveFirst
    Do While Not rst.EOF
        m_UnitPrice = rst![Unit Price]
        m_Discount = rst![Discount]
        m_Quantity = rst![Quantity]
        m_SaleValue = m_Quantity * ((1 - m_Discount) * m_UnitPrice)
        rst.Edit
        rst![SaleValue] = m_SaleValue
        rst.Update
        [Order Details].Form.Bookmark = rst.Bookmark
    rst.MoveNext
    Loop
    rst.Close
    
    Set rst = Nothing
    
    End Sub
  21. Create a Textbox to the right of the Command Button.

  22. Set the Caption property value of the Child Label to Order Value:

  23. Write the expression =[Order Details].[Form]![totSale]. The idea of this expression is to bring the Summary Value from the Text box, we have created in the Footer Section of the sub-form, into the Order Form.

  24. Save and close the Orders Form.

    Open the Order Form.

  25. Open the Orders Form in normal View.
  26. If the Sale Value column is not appearing in the visible area of the Datasheet then move the bottom scroll bar to the right, highlight the Sale Value column, click and hold the mouse button and drag it to the left and place it into the visible area.

    You can now see the Sub-Form shows some records related to the Order ID on the main Form. The new Text box we have created to the right of the Command Button is empty because we have not updated the [Sale Value] field on the Datasheet.

  27. Click on the Command Button to calculate and update the Sale Value of each record on the Datasheet Sub-Form.

Now you will find the Sale Value column of all records on the sub-form, updated and the Summary value of all records appearing in the Text box to the right of the Command Button. The records updated are only those Order Detail records related to Order Id on the Orders Form. If you move the Orders table record forward their related record sale value is not updated. They will be updated only when you click on the Command Button.

The statement in the above code Set rst = [Order Details].Form.RecordsetClone is setting a reference to the RecordsetClone Object of the Sub-Form [Order Details]. Subsequent lines calculate the Sale Value after Discount, if any, and update the sale value into the new field [Sale Value] we have created in the Order Details Table.

The statement [Order Details].Form.Bookmark = rst.Bookmark overwrites the form's Bookmark with the record set's current record Bookmark. The result of this action is that the current record processed in the record set clone becomes the current record on the sub-form. If you have several records on the sub-form you can see some visible action on the sub-form moving the cursor from one record to another very fast, starting from the first record to the last one by one as the updating action progress through the records.

Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code