LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Report Module in Class Module-2

a.p.r. pillaiSeptember 23, 2023 No comments

Introduction.

Hiding Report Lines Conditionally.

Last week we saw that the highlighting of Report lines in the Report Print Event Subroutines can be moved into the standalone Class Module and execute the Code from there when the Report is PrintPreviewed or Printed. To highlight certain Report Line records that don't meet specific Marks criteria marked by drawing a red-colored ellipse around the TextBox. The traditional way of this procedure is Coded in the Detail_Print Event Subroutine in the Report Class Module.

The Report Detail_Format, Report_Page Events.

The Report Detail Section Format Event runs before the Print Event. The Report Detail Format Event places the Data Records line-by-line in the Report Detail Section. The Report Header, Footer, Page Header, Footer, and Report Page Events also take place in the Report Formatting phase and if these Section Events are enabled we can write the Code in those Sections too.

Here, we will try some tricks in the Detail_Format Event of the Report and use the earlier Students' Exam Result listing differently, rather than mark the failed cases with an ellipse around the obtained mark percentage as we did in the earlier episode. With this new method the Report will be printed in two different categories from the same Report.

Exam Passed Students Listing.
Exam Failed Students Listing.

Both listings will be taken from the same Report based on one of the above options selection.

I know we can do this very easily with other means, like Passing the OpenArgs Value and filtering the Report Records. Or setting the Query Criteria to the Form-based TextBox Value on the Report launching Form. Then we will not know how we can do it through the Detail Section Format Event VBA Code. Learning to do something differently is fun and you will know you can do it more than one way when the need arises.

The Report Listing image for the Exam Passed Cases Option above is given below:

The Exam Failed Cases Listing.

The Report Launching Form Image.

The left-side Command Button launches the Report with the Normal Coding on the Report Module itself. The Report Page is drawn with a double-lined Border also.

Since there are three Command Buttons on the Main Form we created a Wrapper Class with the name ClsCmdButton to launch the Reports from there. The ClsCmdButton Class VBA Code is listed below:

Option Compare Database
Option Explicit

Private cmdfrm As Form
Private WithEvents cmdNor As CommandButton 'For Normal Report Module Coded Report launching
Private WithEvents cmdCls As CommandButton 'Report Module Code in Class Module
Private WithEvents cmdQuit As CommandButton'Close the Main Form
Dim Opt As Variant
Dim param As String

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Command Button Events
'Author: a.p.r. pillai
'Date  : 22/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get cmd_Frm() As Form
    Set cmd_Frm = cmdfrm
End Property

Public Property Set cmd_Frm(ByRef cfrm As Form)
    Set cmdfrm = cfrm
    Call class_Init
End Property

Private Sub class_Init()
Const EP = "[Event Procedure]"
    Set cmdNor = cmdfrm.cmdNormal
        cmdNor.OnClick = EP
        
    Set cmdCls = cmdfrm.cmdClass
        cmdCls.OnClick = EP
        
    Set cmdQuit = cmdfrm.cmdClose
        cmdQuit.OnClick = EP
End Sub

Private Sub cmdNor_Click()
Dim RptOpt As Integer

RptOpt = ReportOption()

    param = cmdfrm!Pass 'PassPercentage
    param = param & "," & RptOpt ' and report option
    
    If Nz(cmdfrm.Pass, 0) = 0 Then
        MsgBox "Enter pass-Mark Percentage?" & vbCr & "e.g.: default 60"
    Else
        DoCmd.OpenReport "StudentsPassFail_Normal", acViewPreview, , , , param
    End If
End Sub

Private Sub cmdCls_Click()
Dim RptOpt As Integer

RptOpt = ReportOption()
    param = cmdfrm!Pass 'PassPercentage
    param = param & "," & RptOpt ' and report option
    
    If Nz(cmdfrm.Pass, 0) = 0 Then
        MsgBox "Enter pass-Mark Percentage?" & vbCr & "e.g.: default 60"
    Else
        DoCmd.OpenReport "StudentsPassFail_Normal", acViewPreview, , , , param
    End If
End sub

'Event Subroutines
Private Sub cmdQuit_Click()
    If MsgBox("Close " & cmdfrm.Name & " Form?", vbYesNo + vbQuestion, "cmd_Click") = vbYes Then
        DoCmd.Close acForm, cmdfrm.Name
        Exit Sub
    End If
End Sub

In the global declaration area, the Main Form and Command Button Instances are declared, along with two simple Variable declarations. The Opt Variable will hold the Report Type Option selection Value returned from a Standard Module Public Function ReportOption(). The param String Variable will be assigned with the Report OpenArgs input Values.

The report Option selection Menu is displayed from the ReportOption() Function in the Standard Module.

In the Form Object Set Property Procedure, the Class_Init() Subroutine is called. In the Class_Init() Subroutine the CommandButton Click Events are enabled.

In the CmdNor_Click() Subroutine the ReportOption() Function is called and the returned value is stored in the RptOpt Variable. The PassPercentage Marks value is retrieved from the Form is saved in the param Variable and the Report Option selection is also added into the Param variable both separated with a comma.

The Report is open in PrintPreview and the value in the param is passed as the Report OpenArgs.

The same steps are run for the CmdCls_Click() Subroutine that opens the StudentsPassFail_Class Report also.

The CmdQuit_Click() Event Subroutine Closes the Form.

ReportOption Function VBA Code.

Option Compare Database

'Report Option Selection
Public Function ReportOption() As Integer
Dim msg As String

  msg = "1. Passed List" & vbCr & "2. Failed List"
  
  Opt = 0
  Do While Opt < 1 Or Opt > 2
    Opt = InputBox(msg, "Report Options", 1)
    If Opt = "" Then Opt = 0
  Loop
  
ReportOption = Opt
End Function

The Report StudentsPassFail_Normal Module VBA Code.

The VBA Code in the Report Module, showing how it is done in the traditional way of Coding, is given below for reference.

Option Compare Database
Option Explicit

Dim Opt As Variant

'---------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'---------------------------------------------------------
'Sreamlining Report Module Code in Standalone Class Module
'Author: a.p.r. pillai
'Date  : 22/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'---------------------------------------------------------
Private Sub Report_Load()
Dim x As Variant
x = Split(OpenArgs, ",")

Me!PassPercentage = Val(x(0))
Opt = Val(x(1))

End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Report Format Pass Calls this Subroutine
Dim Curval As Double
Dim pf As Double
Dim yn As Boolean

On Error GoTo Detail_Format_Err

pf = Me!PassPercentage
Curval = Me!Percentage 'Student's Marks percentage
yn = (Curval >= pf) 'check for pass/fail case

'Detail Section Detault Setting hidden
'During the Report Formatting phase the Items which meets
'the criteria only visible on the Report Detail Section when PrintPreviewed

Detail.Visible = False
If FormatCount = 1 Then 'The Report's Formatting Count=1 
       If yn Then ' yn=True - student Passed
          With Me
            .lblRemarks.Caption = "PASSED"
            .lblRemarks.ForeColor = RGB(0, 255, 0) 'Green Color
                If Opt = 1 Then 'Report Option Passed Students List
                    Detail.Visible = True 'Make the Detail Section Visible
                End If
          End With
        Else 'yn=False an Option=2 Cases
          With Me
            .lblRemarks.Caption = "FAILED"
            .lblRemarks.ForeColor = RGB(255, 0, 0) 'Red Color
                If Opt = 2 Then
                    Detail.Visible = True
                End If
          End With
        End If
End If


Detail_Format_Exit:
Exit Sub

Detail_Format_Err:
MsgBox Err.Description, , "Detail_Format()"
Resume Detail_Format_Exit
End Sub

Private Sub Report_Page()
    PageBorder Me.Name
End Sub

The StudentPassFail_Normal Report Module Segmentwise Code Review.

At the Global declaration area, the Variant Variable Opt is declared for holding the Option selected for Exam Result Print previewing of Passed or Failed Students' List.

The Report_Load Event Subroutine.

 
Option Compare Database
Option Explicit

Dim Opt As Variant

'---------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'---------------------------------------------------------
'Sreamlining Report Module Code in Standalone Class Module
'Author: a.p.r. pillai
'Date  : 22/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'---------------------------------------------------------
Private Sub Report_Load()
Dim x As Variant
x = Split(OpenArgs, ",")

Me!PassPercentage = Val(x(0))
Opt = Val(x(1))

End Sub

In the Form_Load() Event Procedure the PassPercentage and Report selection Option received as Report OpenArgs, both values separated with a comma are split into an Array of two elements. The x(0) element with the value PassPercentage marks is saved into the Report TextBox passpercentage. The Report Type option selection value x(1) is saved in the Opt variable

The PassPercentage value is copied into the Local Variable pf. The next two lines read the first student's Obtained Marks Percentage from Percentage TextBox into Variable CurVal. The Student's Mark is compared with the Passmark Percentage (pf) and the result is obtained in the yn Boolean Variable.

The next Code segment, given below, tests to see if the current student's record is qualified to be placed in the Passed category in the Detail Section.

Detail.Visible = False 'Hide the Detail Section
If FormatCount = 1 Then 'The first Format Pass on this Page.
       If yn Then
            lblRemarks.Caption = "PASSED"
            lblRemarks.ForeColor = RGB(0, 255, 0)
                If Opt = 1 Then 'Passed Students Listing
                    Detail.Visible = True
                End If
        Else
            lblRemarks.Caption = "FAILED"
            lblRemarks.ForeColor = RGB(255, 0, 0)
                If Opt = 2 Then
                    Detail.Visible = True
                End If
        End If
End If

The statement Detail.Visible = False makes the Detail Section of the Report hidden first. The next statement checks the OnFormat Event action is passing through the Report Detail Section for the first time to lay the student record on the Report. The Report may undergo more Formatting passes on a Report Page in preparation for Printing/PrintPreviewing. If the Student is qualified to be placed in the Passed Category and the Report Option selected also matches to 1 then the Detail Section of the Report is made visible for such records on the Report.

Otherwise, if the Student's Obtained Mark Percentage is less and the option selected is 2 then those Students' Cases in the Detail Section are made visible. In either case, the TRUE/FALSE value in the Variable yn and the Option selection should both match (TRUE & 1 = Passed List otherwise FALSE & 2 = Failed List) to display the Passed cases or failed cases to appear on the Report.

Next, the Report_Page() Subroutine calls the PageBorder() Function in the Standard Module to draw the PageBorder on the Report.

Private Sub Report_Page()
    PageBorder Me.Name
End Sub

The PageBorder() Function Code In Standard Module.

Public Function PageBorder(ByVal strName As String)
Dim Rpt As Report, lngColor As Long
Dim sngTop As Single
Dim sngLeft As Single
Dim sngwidth As Single
Dim sngheight As Single

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Draw Report PageBorder
'Author: a.p.r. pillai
'Date  : 18/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

lngColor = RGB(0, 0, 255)
On Error GoTo DrawBox_Err

    Set Rpt = Reports(strName)
    ' Set scale to pixels.
    Rpt.ScaleMode = 3
    
    'outer Border
       sngTop = Rpt.ScaleTop        'Top Value After Margin
      sngLeft = Rpt.ScaleLeft       'Left Value After Margin
     sngwidth = Rpt.ScaleWidth - 7  ' Right Margin -7 pixels
    sngheight = Rpt.ScaleHeight - 7 'Bottom Margin -7 pixels
       
    ' Draw line as a box.
Rpt.Line (sngTop, sngLeft)-(sngwidth, sngheight), lngColor, B

  'Draw Box inside the outer Box
   sngTop = Rpt.ScaleTop + 5
  sngLeft = Rpt.ScaleLeft + 5
 sngwidth = Rpt.ScaleWidth - 13
sngheight = Rpt.ScaleHeight - 13

'Draw second Box within the Borders of the First Box
Rpt.Line (sngTop, sngLeft)-(sngwidth, sngheight), lngColor, B

DrawBox_Exit:
Exit Function

DrawBox_Err:
MsgBox Err.Description, , "DrawBox"
Resume DrawBox_Exit
End Function

The PageBorder() Function accepts the open Report's Name as a Parameter. The Function draws two Border Lines, one inside the other, inside the Report 4 sides of the Margin area. It uses the LINE Command with the X, and Y coordinates of the Left Top corner, after the left and top margin areas, and the right bottom corner value before the margin area to draw a diagonal Line. The Color parameter is given as an RGB Value and is used to draw the Line diagonally. The next Parameter value B draws a Box using the Diagonal Value.

The Rpt.ScaleTop and Rpt.ScaleLeft value calculates the left top corner point, after the Left and Top Margin area, and some plus-minus Offset values ensure that when the Box is drawn it doesn't overlap, or draw a second Border inside the outer Border.

The Streamlining Procedure of the Report VBA Code.

Let us create the Wrapper Standalone Class Module to move the Report Module Code into it. The Wrapper Class Module ClsStudentsList Code is listed below.

Option Compare Database
Option Explicit

Private WithEvents Rpt As Access.Report
Private WithEvents SecDetail As Access.[_SectionInReport]

Private RequiredMarks As Access.TextBox
Private Obtained As Access.TextBox
Private lblRem As Access.Label

Private Opt As Variant

'---------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'---------------------------------------------------------
'Sreamlining Report Module Code in Standalone Class Module
'Author: a.p.r. pillai
'Date  : 22/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'---------------------------------------------------------

Public Property Get mRpt() As Access.Report
   Set mRpt = Rpt
End Property

Public Property Set mRpt(RptNewVal As Access.Report)
  Set Rpt = RptNewVal

Call class_Init
End Property

Private Sub class_Init()
On Error GoTo Class_Init_Err:
Dim msg As String
Dim x As Variant

Const strEvent = "[Event Procedure]"
x = Split(Rpt.OpenArgs,",")

  With Rpt
    !PassPercentage = Val(x(0)) ' Pass Mark% save on Report
    Set RequiredMarks = .PassPercentage 'Assign this Control Reference to pct
    Opt = Val(x(1))
    Set Obtained = .Percentage     'Student's Obtained Percentage
    Set lblRem = .lblRemarks 'Passed/Failed Display Label
    Set SecDetail = .Section(acDetail) 'Detail Section Reference
    
     SecDetail.OnFormat = strEvent 'Enable Detail Section Format Event
     .OnPage = strEvent 'Enable Report_Page Event
  End With
  
Class_Init_Exit:
Exit Sub

Class_Init_Err:
MsgBox Err & ": " & Err.Description, vbCritical + vbOK, "Class_Init()"

Resume Class_Init_Exit
End Sub

Private Sub secDetail_Format(Cancel As Integer, FormatCount As Integer)
'Report Format Pass Calls this Subroutine
Dim Curval As Double
Dim pf As Double    'pass/fail
Dim yn As Boolean

On Error GoTo secDetail_Format_Err

Curval = Nz(Obtained.Value, 0)
pf = Nz(RequiredMarks.Value, 0)
yn = (Curval >= pf)

'Start Laying the Detail Section items from Format Count 1 onwards.
'All the Records are placed on the Detail Section, but the
'Lines which meets the Criteria only made visible on the Report.

SecDetail.Visible = False 'Hide the Detail Section
If FormatCount = 1 Then 'The first Format Pass on this Page.
       If yn Then
            lblRem.Caption = "PASSED"
            lblRem.ForeColor = RGB(0, 255, 0)
                If Opt = 1 Then 'Passed Students Listing
                    SecDetail.Visible = True
                End If
        Else
            lblRem.Caption = "FAILED"
            lblRem.ForeColor = RGB(255, 0, 0)
                If Opt = 2 Then
                    SecDetail.Visible = True
                End If
        End If
End If

secDetail_Format_Exit:
Exit Sub

secDetail_Format_Err:
MsgBox Err & ": " & Err.Description, , "secDetail()"
Resume secDetail_Format_Exit
End Sub

Private Sub Rpt_Page()
    PageBorder Rpt.Name
End Sub

In the global Declaration area, a Report Object with the name Rpt is declared and qualified with the keyword WithEvents to capture the Report-level Events (like Report_Page()) and execute the Event Subroutines. Another Report Section object SecDetail is also qualified with the keyword WithEvents declared to capture the Report Detail Section Format Events.

Two TextBox Controls and a Label Control are declared. The first TextBox RequiredMarks will hold the Pass Marks Percentage entered on the Report launching Form TextBox control and passed as Report Open Argument. The Obtained TextBox object instance in the Class module will read the Obtained Marks Percentage of the Student from the Report. The lblRem Label Control holds the reference of the Label control under the Remarks Column on the Report and will display the PASSED/FAILED Text depending on the Report Type Option selected. The Opt Variant Variable will receive the Report type option selected in the ReportOption() Function.

Next, the Report Object Property Get/Set Procedure assigns the Report Reference to the Rpt object in the ClsStudentsList Wrapper Class. From the Property Set m_Rpt() Procedure calls the Class_Int() Subroutine. The Class_Init() Subroutine Code segment is given below:

Private Sub class_Init()
On Error GoTo Class_Init_Err:
Dim msg As String
Dim x As Variant

Const strEvent = "[Event Procedure]"
x = Split(Rpt.OpenArgs,",")

  With Rpt
    !PassPercentage = Val(x(0)) ' Pass Mark% save on Report
    Set RequiredMarks = .PassPercentage 'Assign this Control Reference to pct
    Opt = Val(x(1))
    Set Obtained = .Percentage     'Student's Obtained Percentage
    Set lblRem = .lblRemarks 'Passed/Failed Display Label
    Set SecDetail = .Section(acDetail) 'Detail Section Reference
    
     SecDetail.OnFormat = strEvent 'Enable Detail Section Format Event
     .OnPage = strEvent 'Enable Report_Page Event
  End With
  
Class_Init_Exit:
Exit Sub

Class_Init_Err:
MsgBox Err & ": " & Err.Description, vbCritical + vbOK, "Class_Init()"

Resume Class_Init_Exit
End Sub

At the beginning of the Class_Init() Subroutine, one local variable msg and a Constant Variable strEvent assigned with the Text "[Event Procedure]" are declared.

The next VBA Code Segment initializes the declared object variables with the References of the Controls from the Report.

x = Split(Rpt.OpenArgs,",")

  With Rpt
    !PassPercentage = Val(x(0)) ' Pass Mark% save on Report
    Set RequiredMarks = .PassPercentage 'Assign this Control Reference to pct
    Opt = Val(x(1))
    Set Obtained = .Percentage     'Student's Obtained Percentage
    Set lblRem = .lblRemarks 'Passed/Failed Display Label
    Set SecDetail = .Section(acDetail) 'Detail Section Reference
    
     SecDetail.OnFormat = strEvent 'Enable Detail Section Format Event
     .OnPage = strEvent 'Enable Report_Page Event
  End With

The x = Split(Rpt.OpenArgs,",") statement Splits the values into two parts received in the OpenArgs String Variable. The first part x(0) array element is assigned to the PassPercentage Marks TextBox on the Report. The second part, Report category Option x(1) is assigned to the variable Opt.

The Student's Obtained Marks Percentage reference is assigned to the Obtained TextBox instance in the Class Module. The lblRem Label object instance is assigned with the lblRemarks Label control reference under the Remarks Column. The SecDetail Section Object is assigned with the Report Detail Section Reference.

Note: What does the meaning of the word Reference in the context of a Form, Control, or Object? When an Object/Control is loaded into memory it occupies a certain area of the computer's memory with an addressable reference or memory Pointer. Even though this memory location number is not known to us it is directly related to the object name like Text0, or Quantity.

When this reference address is assigned to an Instance of the same Object Type, like Set Txt = myForm.Quantity We can work with visible/physical objects, like Form, TextBox, or Report indirectly with an instance of the same Object, as we do in the Wrapper Class, like Txt.Value = 20 will store the Value 20 in the Quantity TextBox on the Form.

When we call a Function with the Form or TextBox as a Parameter actually we are passing the Reference of those Objects. This is the central concept of Streamlining the Form/Report Module Code in the Standalone Class Module is based on.

Next, the Report Detail Section Format and Report_Page() Events are enabled. After that, the Report type option selection Menu is displayed to select.

Once the Report Type Option is selected the Report Formatting starts and we have specific requirements as far as the Detail Section Formatting is concerned. We have already written the Code in the Detail_Section Formatting Event Procedure and explained it earlier.

Next, the Report Page level Event Subroutine calls the PageBorder() Function from the Standard Module to draw the Report Page Border.

To execute the Code in the ClsStudentsList Class Module we must load this Module into memory through the Report_Load() Event Subroutine. It cannot load itself into memory. The Report Module VBA Code is given below.

Option Compare Database
Option Explicit

Private Marks As New ClsStudentsList

Private Sub Report_Load()
  Set Marks.mRpt = Me
End Sub

The above four lines of Code help to bring the Standalone Class Module into Memory and to run the entire action in the Standalone Class Module.

Hope you enjoy VBA Coding in the Standalone Class Module and are aware of its potential to save time by separating the task of Report Design and VBA Coding so that it is easy to transport reusable Code into other Projects and use the Code with/without change as the case may be.

Demo Database Download Link. Change Database.

StudentsMarkList.zip

Streamlining Report Module Code in Class Module.

a.p.r. pillaiSeptember 12, 2023 No comments

Introduction.

Report Module VBA Code in Standalone Class Module.

After going through the earlier Episodes of this Topic: Streamlining Form Module Code in the Standalone Class Module, we could write the Event Subroutines, for frequently used Access Controls on the Form, in the Standalone Class Module without interfering with the Form design task. If you landed on this page directly and have not yet gone through the earlier sessions of this tutorial I suggest you start with the first page of this series Streamlining/Reusing Form Module Code for new Projects to understand this new concept and implementation methods fully. All the links to this Series of Articles are available at the end of this Page.

The Report Section Print Event Subroutines.

This is a repeat of the two Blog Posts published earlier on Highlighting Report Lines conditionally, as part of the new concept of Streamlining Form/Report Module Code in the Standalone Class Module. This demonstrates how to transfer the existing Event Subroutine Codes from the Report Module to the Standalone Class Module. Generally speaking, this is double work and nobody would like to go into the Code again, which was written for a particular task after incorporating several refinements and final touches.

But, I think if you would like to do some trial runs and try to convert a few existing Form/Report Module Codes to the new way of Coding you will know how much extra work you put into the traditional method and it will give you more insights into the new way of coding in comparison with the traditional way.

Organize the Existing Form Module Code for the Standalone Class Module.

Before you start trying to go forward with this idea it is very important that you organize the existing Code of the Controls Category-wise together in the Form Module.
Make a Copy of the Form, you wish to try implementing the new method, with a new name. Plan to transfer this Form's all TextBox's Event Subroutine Code into a TextBox Wrapper Class first.
In preparation, move the TextBox Event Subroutines next to each other on the Form Module itself first. If a single TextBox has more than one Event Subroutine then arrange them next to each other too.
Create a TextBox Wrapper Class.
Create the Intermediate Class Module and enable the Events of the TextBoxes in the Class_Init() Subroutine.
Copy and Paste the TextBox Event Subroutines from the Form Module to the Wrapper TextBox Class and modify the Event Subroutine Names with the correct TextBox Object Instance name as Subroutine name Prefix. Use the Select Case . . . End Select structure inside each category of Event to position the Code belongs to a particular TextBox on the Form.
Delete the Copied Subroutines from the Form Module.
At the Form Module global declaration area define the Intermediate Class Object Instance and in the Form_Load() Event Procedure pass the Me Form object to the Set Frm() Property Procedure.
Save the Form after changes.
Before we try this new Form with the Wrapper Class-based Event Subroutines in place, rename the original Form with a new name with a suffix of X or Y.
Rename the new Form to the Original name.
Open the Form as you do from your Main Menu, or whatever method inbuilt in your Project, and ensure that all the Subroutines of all the TextBoxes on the Form functions as before. If any errors then find and correct them and see that everything works normally as before.
Repeat this process for all other controls on the Form.

The Traditional Way of Coding.

The traditional method of Coding is the best way for beginners to learn VBA and that is how we all started learning VBA. My first PC was the Spectravideo Model SV-328 with built-in Microsoft Extended Basic and started learning the BASIC Language from there, and Writing Programs and saving them in Audio Cassette Tapes at that time, the cheapest mode of storage.

It is interesting now to look at the Bootup Screen Image of this Computer given below (courtesy of Wikipedia.org):

Then came the GW-Basic, Quick BASIC, Visual Basic, and VBA.

There is a very interesting Computer Buying Guide from 1986 with me, published by the Consumer Guide Magazine, for Computers with a Price Range of $299 (Commodore 128) to $4550 (Kaypro 286i, with 512K RAM, 6 MHz, 80286 microprocessor, compatible with IBM PC AT).

The configuration of the machines, their speed, and the Prices of the machines on those days is very interesting to read and compare with the present-day machines.

Coming back to the topic the following two links of earlier Blog Posts on Report Module Coding for highlighting some Report lines on certain conditions during the Printing cycle of the Report.

1. Highlighting Reports. - August 16, 2007

2. WithEvents and Report Line Highlighting. - June 21, 2019

Let us see how we can run the Report Print Event Subroutines Code in the Standalone Class Module.

The Students Examination Result Report Image.

Note: If you attempt to run the Report (StudentHighlight_Normal) directly by double-clicking on the Report, the Report may open in the ReportPreview Mode and not in PrintPreview Mode. In that case, you will not find the display as shown above. Right-click on the Report, and select the PrintPreview option from the displayed Menu to view the Report correctly.

The Students List with their Final Examination Result published in the form of a Report shows that the minimum Pass Marks required is 60% (360 Marks) out of 600 Total marks. The unsuccessful students' cases are marked with a Red ellipse around the obtained marks percentage. The Legend Label at the Footer of the Report is also drawn with a Circle within the dimension of the Label.

To try the procedure with different Pass Mark Percentages there is a Form with a TextBox to input the Pass Mark Percentage before launching the Exam Result Report for Print Previewing. The Pass Mark Percentage is passed in the Open-Argument parameter to the Report opening Command. The Form Image is given below for reference.

The VBA Code run earlier in the Report Class Module is listed below for reference. This Code will be transferred to the Standalone Class Module to run the procedure in the new Coding method.

Option Compare Database
Option Explicit

Private Curval As Double
Private MinTarget As Double

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intPrintCircle As Boolean

On Error GoTo secRpt_Print_Err

MinTarget = Me!PassPercentage
Curval = Me!Percentage

If (MinTarget > 0) And (Curval < MinTarget) Then
  intPrintCircle = True
Else
  intPrintCircle = False
End If

DrawCircle intPrintCircle, Me.Percentage

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print()"
Resume secRpt_Print_Exit
End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
Dim yn As Boolean, lbl As Control

On Error GoTo secFutr_Print_Err

yn = True 'set the flag false to draw oval shape
Set lbl = Me.Legend 'pass label control in Page Footer

DrawCircle yn, lbl 'draw circle in legend label

secFutr_Print_Exit:
Exit Sub

secFutr_Print_Err:
MsgBox Err.Description, , "secFutr_Print"
Resume secFutr_Print_Exit

End Sub

Private Sub DrawCircle(ByVal bool As Boolean, ovlCtl As Control)
Dim ctl As Control
Dim bolPrintCircle As Boolean
Dim sngAspect As Single
Dim intShapeHeight As Integer
Dim intShapeWidth As Integer
Dim sngXCoord As Single
Dim sngYCoord As Single

On Error GoTo DrawCircle_Err

If bool Then 'if pass no highlighting, change logic for pass cases
    bolPrintCircle = True
Else 'highlight failed cases
    bolPrintCircle = False
End If

Set ctl = ovlCtl
        
    If Not IsNull(ctl) Then
        If bolPrintCircle Then
           ' change this value to adjust the oval shape of the circle.
            sngAspect = 0.25
   
            ' Determine coordinates of ctl and to draw ellipse.
            ' Determine height and width of ellipse.
            intShapeHeight = ctl.Height
            intShapeWidth = ctl.Width
    
            'calculate circle vertical Y coordinate
            sngYCoord = ctl.Top + (intShapeHeight \ 2)

            'calculate horizontal X coordinate of circile
            sngXCoord = ctl.Left + (intShapeWidth \ 2)
            
            'draw an ellipse around the Total TextBox
            Report.Circle (sngXCoord, sngYCoord), intShapeWidth \ 2, RGB(255, 0, 0), , , sngAspect
            bolPrintCircle = False
        End If
    End If

DrawCircle_Exit:
Exit Sub

DrawCircle_Err:
MsgBox Err.Description, , "DrawCircle()"
Resume DrawCircle_Exit
End Sub

Review of the VBA Code in the Report Class Module.

There are two Event Subroutines, the Detail_Print() and the ReportFooter_Print() Subroutines. Then the DrawCircle() Subroutine that is called from both Detail_Print() and ReportFooter_Print() Event Subroutines to draw the ellipse around the data in the TextBox on the Report where the Marks Percentage doesn't meet the Criteria, passed as Report Open Argument from the TextBox on the Form.

The Print_Event Subroutines Code is written on the Report StudentHighLight_Normal Module as we normally do. This demo Report can be run directly, from the Demo Database given at the end of this Page for download, by Clicking on the Command Button, on the Form shown above, with the Caption Normal Report.

The Changed VBA Code in the Standalone Class Module.

The same Event Subroutines and the DrawCircle() Subroutine Codes are removed from the Report Class Module and placed in the Standalone Wrapper Class Module as given below:

Option Compare Database
Option Explicit

Private WithEvents Rpt As Access.Report
Private WithEvents secRpt As Access.[_SectionInReport]
Private WithEvents secFutr As Access.[_SectionInReport]

Private pct As Access.TextBox  'Percentage
Private lgnd As Access.Label
Private Pass As Integer 'OpenArgs Value
'------------------------------------------------------
'Streamlining Report Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Streamlining Report Module Code
'Author: a.p.r. pillai
'Date  : 12/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Property Get mRpt() As Access.Report
   Set mRpt = Rpt
End Property

Public Property Set mRpt(RptNewVal As Access.Report)
  Set Rpt = RptNewVal
  
  Call Class_Init
End Property

Private Sub Class_Init()
On Error GoTo mRpt_Err
Const strEvent = "[Event Procedure]"

'Pass Percentage value
  Pass = Nz(Rpt.OpenArgs, 0) 'OpenArgument
  Rpt.PassPercentage.Value = Pass
  
  With Rpt
     Set secRpt = .Section(acDetail)
     Set secFutr = .Section(acFooter)
     
     secRpt.OnPrint = strEvent  'Enable Event
     secFutr.OnPrint = strEvent '    "
  End With
  
  Set pct = Rpt!Percentage
  Set lgnd = Rpt.Legend
 
mRpt_Exit:
Exit Sub

mRpt_Err:
MsgBox Err.Description, , "mRpt()"
Resume mRpt_Exit

End Sub

'Draw ellipse around controls that meet specified criteria.
Private Sub secRpt_Print(Cancel As Integer, PrintCount As Integer)

Dim obtPct As Double
Dim yn As Boolean

On Error GoTo secRpt_Print_Err

    obtPct = pct.Value         'read Pass Percentage TextBox value
    yn = (obtPct < Pass)      'Passed or Not (TRUE/FALSE)

'call the DrawCircle Subroutine with Fail flag
'and the Control as parameters
    Call DrawCircle(yn, pct)

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print"
Resume secRpt_Print_Exit

End Sub

'Draw oval shape around Label in Report_Footer Section
Private Sub secFutr_Print(Cancel As Integer, PrintCount As Integer)
Dim yn As Boolean, lbl As Control

On Error GoTo secFutr_Print_Err

Set lbl = lgnd 'Fail label control in Page Footer
yn = True 'set the flag True to draw oval shape

Call DrawCircle(yn, lbl) 'draw circle in legend label

secFutr_Print_Exit:
Exit Sub

secFutr_Print_Err:
MsgBox Err.Description, , "secFutr_Print"
Resume secFutr_Print_Exit

End Sub

Private Sub DrawCircle(ByVal bool As Boolean, ovlCtl As Control)
Dim ctl As Control
Dim bolPrintCircle As Boolean
Dim sngAspect As Single
Dim intShapeHeight As Integer
Dim intShapeWidth As Integer
Dim sngXCoord As Single
Dim sngYCoord As Single

On Error GoTo DrawCircle_Err

If bool Then 'Highlight Failed Cases
    bolPrintCircle = True
Else
    bolPrintCircle = False
End If

Set ctl = ovlCtl
    If Not IsNull(ctl) Then
        If bolPrintCircle Then
        ' change this value to adjust the oval shape of the circle.
            sngAspect = 0.25
   
        ' Determine coordinates of ctl and to draw ellipse.
        ' Get height and width of Control.
            intShapeHeight = ctl.Height
            intShapeWidth = ctl.Width
    
        'calculate circle vertical Y coordinate
            sngYCoord = ctl.Top + (intShapeHeight \ 2)

        'calculate horizontal X coordinate of circile
            sngXCoord = ctl.Left + (intShapeWidth \ 2)
            
        'Draw an ellipse within the Percentage TextBox Boundaries
        'in Red Color
            Rpt.Circle (sngXCoord, sngYCoord), intShapeWidth \ 2, RGB(255, 0, 0), , , sngAspect
            bolPrintCircle = False
        End If
    End If

DrawCircle_Exit:
Exit Sub

DrawCircle_Err:
MsgBox Err.Description, , "DrawCircle()"
Resume DrawCircle_Exit

End Sub

Review of the Report Wrapper Class VBA Code.

To execute the Standalone Class Module Code, when the Report is open, we must get the reference of the Report and assign it to the Report object Instance Rpt in our Standalone Class Module. Let us review the Code from the beginning to the Class_Init() Segment.

Option Compare Database
Option Explicit

Private WithEvents Rpt As Access.Report
Private WithEvents secRpt As Access.[_SectionInReport]
Private WithEvents secFutr As Access.[_SectionInReport]

Private pct As Access.TextBox  'Percentage
Private lgnd As Access.Label
Private Pass As Integer 'OpenArgs Value
'------------------------------------------------------
'Streamlining Report Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Streamlining Report Module Code
'Author: a.p.r. pillai
'Date  : 12/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Property Get mRpt() As Access.Report
   Set mRpt = Rpt
End Property

Public Property Set mRpt(RptNewVal As Access.Report)
  Set Rpt = RptNewVal
  
  Call Class_Init
End Property

Private Sub Class_Init()
On Error GoTo mRpt_Err
Const strEvent = "[Event Procedure]"

'Pass Percentage value
  Pass = Nz(Rpt.OpenArgs, 0) 'OpenArgument
  Rpt.PassPercentage.Value = Pass
  
  With Rpt
     Set secRpt = .Section(acDetail)
     Set secFutr = .Section(acFooter)
     
     secRpt.OnPrint = strEvent  'Enable Event
     secFutr.OnPrint = strEvent '    "
  End With
  
  Set pct = Rpt.Percentage
  Set lgnd = Rpt.Legend
 
mRpt_Exit:
Exit Sub

mRpt_Err:
MsgBox Err.Description, , "mRpt()"
Resume mRpt_Exit

End Sub

The first three lines, below the Option Explicit line, declare a Report Object Instance with the object name Rpt and qualified with the keyword WithEvents to capture the Report Events. Similarly, two Report Sections Access.[_SectionInReport] are declared, followed by two Report Control objects, and an Integer Variable.

Next the Set/Get Property Procedures for the Report Object. After the Report object is assigned to the Rpt object instance variable, the Set mRpt() Property Procedure Calls the Class_Init() Subroutine.

The strEvent Constant Variable is assigned with the string value "[Event Procedure]". The statement Pass = Nz(Rpt.OpenArgs, 0) reads the Pass Mark percentage passed as Report Open Argument through the DoCmd.OpenReport from the Form is saved in the Integer Variable Pass. The next line assigns this value to the PassPercentage TextBox on the Report.

Next, the Report Detail Section and Footer Section References are assigned to the secRpt and secFutr Section Objects respectively. The next two lines of Code enable the Print Events.

The statement Set pct = Rpt.Percentage assigns the Obtained Marks Percentage TextBox reference and the lgnd Label Object is assigned with the Reference of the Legend label control at the Report Footer.

These initialisation steps run in the standalone Class Module, when the Report is Open from the cmdClass Click Event Subroutine.

Private Sub cmdClass_Click()
    If Nz(Me.Pass, 0) = 0 Then
        MsgBox "Enter pass-Mark Percentage?" & vbCr & "e.g.: 60"
    Else
        'Me.Pass - Pass Mark Percentage value passed as OpenArgs
        DoCmd.OpenReport "StudentsHighlight_Class", acViewPreview, , , , Me.Pass
    End If
End Sub

Report Class Module VBA Code.

In the Report Class Module, declares an Instance of the Standalone Class Module Object ClsStudentHighlight with the name Marks in the global area. The New keyword in Private Marks As New ClsStudentHighlight statement creates an instance of the Standalone Class Module in memory.

In the Report_Load() Event Subroutine the current Report Object Me is passed to the Set mRpt() Property Procedure in the ClsStudentHighlight Class Module Object.

Option Compare Database
Option Explicit

Private Marks As New ClsStudentHighlight

Private Sub Report_Load()
  Set Marks.mRpt = Me
End Sub

The Report Detail_Print() Event.

When the Report is open the ClsStudentHighlight standalone Class Module Instance is also open in memory by the Report Module global declaration statement. The current Report Reference is passed to the Set mRpt() Property Procedure and the standalone Class Module Code starts executing.

'Draw ellipse around controls that meet specified criteria.
Private Sub secRpt_Print(Cancel As Integer, PrintCount As Integer)

Dim obtPct As Double
Dim yn As Boolean

On Error GoTo secRpt_Print_Err

    obtPct = pct.Value         'read Pass Percentage TextBox value
    yn = (obtPct < Pass)      'Passed or Not (TRUE/FALSE)

'call the DrawCircle Subroutine with Fail flag
'and the Control as parameters
    Call DrawCircle(yn, pct)

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print"
Resume secRpt_Print_Exit

End Sub

There are two major Events that take place when the Report is Open, the Report Format Event and the Report Print Event. The Report Format Event lays the data line by line and calculates how many lines can go into one Page based on the type of Paper Size, the design height of the Detail Section, and based on Page Orientation. We will highlight the Exam Failed cases only in the Report Print Event execution time only.

There are two Variables declared for reading the Obtained Percentage of Marks from the Percentage TextBox on the Report into the Variable obtPct. If the obtained mark is less than the Pass Mark percentage (received in the OpenArgs) then the Boolean Variable yn value is set as TRUE.

Next, the DrawCircle() Subroutine is called, with the yn Test Result and pct the Percentage TextBox object as parameters, to draw an oval shape in Red Color around the Percentage TextBox on the Report indicating that the student is not successful in his/her examination.

The DrawCircle() Subroutine Code.

Private Sub DrawCircle(ByVal bool As Boolean, ovlCtl As Control)
Dim ctl As Control
Dim bolPrintCircle As Boolean
Dim sngAspect As Single
Dim intShapeHeight As Integer
Dim intShapeWidth As Integer
Dim sngXCoord As Single
Dim sngYCoord As Single

On Error GoTo DrawCircle_Err

If bool Then 'Highlight Failed Cases
    bolPrintCircle = True
Else
    bolPrintCircle = False
End If

Set ctl = ovlCtl
    If Not IsNull(ctl) Then
        If bolPrintCircle Then
        ' Increase/Decrease this value to adjust the oval shape of the circle.
            sngAspect = 0.25
   
        ' Calculate the coordinates of the ctl to draw the ellipse.
        ' Get height and width of Control.
            intShapeHeight = ctl.Height
            intShapeWidth = ctl.Width
    
        'calculate circle vertical Y coordinate
            sngYCoord = ctl.Top + (intShapeHeight \ 2)

        'calculate horizontal X coordinate of circle
            sngXCoord = ctl.Left + (intShapeWidth \ 2)
            
        'Draw an ellipse within the Percentage TextBox Boundaries
        'in Red Color
            Rpt.Circle (sngXCoord, sngYCoord), intShapeWidth \ 2, RGB(255, 0, 0), , , sngAspect
            bolPrintCircle = False
        End If
    End If

DrawCircle_Exit:
Exit Sub

DrawCircle_Err:
MsgBox Err.Description, , "DrawCircle()"
Resume DrawCircle_Exit

End Sub

The DrawCircle() Subroutine VBA Code Review.

At the beginning, the essential Variables are declared. The first parameter bool value is TRUE then the local Variable bolPrintCircle Variable is Set to True to draw the ellipse..

The sngAspect is initialized with the value 0.25 which gives an oval shape around the TextBox. This value can be increased or decreased to keep the shape within the control's dimensions without overflowing the boundaries of the Percentage TextBox. This depends on the Width and Height of the TextBox, and these values are taken and stored in the intShapeHeight and intShapeWidth Variables.

The next two statements calculate the center point (X, Y coordinates) of the ellipse (oval shape) shape.

The Rpt.Circle() statement Draws the Circle using the value intShapeWidth\2 as the Radius Value of the Circle with the Red Color.

The ReportFooter_Print() Event Subroutine.

'Draw oval shape around Label in Report_Footer Section
Private Sub secFutr_Print(Cancel As Integer, PrintCount As Integer)
Dim yn As Boolean, lbl As Control

On Error GoTo secFutr_Print_Err

Set lbl = lgnd 'Fail label control in Page Footer
yn = True 'set the flag True to draw oval shape

Call DrawCircle(yn, lbl) 'draw circle in legend label

secFutr_Print_Exit:
Exit Sub

secFutr_Print_Err:
MsgBox Err.Description, , "secFutr_Print"
Resume secFutr_Print_Exit

End Sub

The ReportFooter_Print() Subroutine Calls the DrawCircle() Subroutine to draw the ellipse within the Legend Label Control.

Demo Database Download Link.

MarksHighlight.zip

Streamlining Form Module Code - Part Eleven

a.p.r. pillaiAugust 31, 2023 No comments

Introduction.

In this Episode of Streamlining Form Module VBA Code, we will create Wrapper Classes for ComboBox and OptionGroup Controls. Having gone through the previous Episodes, you are now acquainted with the process of creating Wrapper Class Modules for other controls, such as TextBox, ListBox, CommandButton, and TabControl. You have also learned to write Event Subroutines within these modules instead of placing them in the Form Module.

ComboBox and OptionGroup Control.

This time we will learn the usage of ComboBox and OptionGroup Controls as shown in the form image given below.

The Order Detail data in the ListBox are filtered in OrderDetailQ1 based on the Employee ID selected in the ComboBox with the name cboEmp above the ListBox Control. The SQL of the Query is given below:

OrderDetailQ1 SQL.

SELECT Orders.EmployeeID, Employees.LastName, Orders.OrderID, Val(Format([OrderDate],"yyyy")) AS [Year], Orders.Freight
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE (((Orders.EmployeeID)=[Forms]![Frm_OptionGroup]![cboEmp]));

The Freight Value in OrderDetailQ1 is summarised Year-wise in OrderSummaryQ1. The OrderSummaryQ1 Query has the year-wise Freight Value Source Data for the Graph Chart on the Form. OrderSummary SQL is given below.

OrderSummaryQ1 SQL.

SELECT [OrderDetailQ1].EmployeeID, [OrderDetailQ1].LastName, [OrderDetailQ1].Year, Sum([OrderDetailQ1].freight) AS Freight
FROM OrderDetailQ1
GROUP BY [OrderDetailQ1].EmployeeID, [OrderDetailQ1].LastName, [OrderDetailQ1].Year;

Freight Summary Data for the Chart.

Employee	Last Name	Year	Freight
Davolio, Nancy	Davolio	        1996	₹ 1,871.09
Davolio, Nancy	Davolio	        1997	₹ 4,584.47
Davolio, Nancy	Davolio	        1998	₹ 2,381.13

How ComboBox, ListBox, and the Chart linked Together?

When the Employee Code is selected in the ComboBox the AfterUpdate Event is fired and the statement cbofrm.List0.Requery updates the ListBox contents.

There is a hidden Unbound TextBox on the Form with an expression to copy the changed Employee ID Value from the cboEmp ComboBox. This is used for the "Link Master Field" Property in the Graph Chart to update the Freight Year-wise Summary Data in the Graph Chart.

So, by changing the Employee ID (Combobox displays the Employee Name only, and the first column EmployeeID width Property value is set to zero) the ComboBox refreshes the ListBox OrderDetail and the Freight Year-wise Values on the Graph Chart instantly.

The OptionGroup Control.

The OptionsGroup Control can have a group of Radio Buttons or Check Boxes or Toggle Buttons and all of them are placed within a Frame. Here we are using the Radio Buttons within the OptionGroup Frame with the name Frame7. The Frame7 has three Radio Buttons with their Labels and is placed below the ListBox and Graph Chart objects.

There are three options to display the Employee's Freight Sales Values in three different categories.

- The highest Freight Sales Value of the Employee.
- The Lowest Freight Sales Value.
- The Total Fright Sales Value.

The Unbound TextBox at the left side of the OptionGroup Control will display the Value when the option Radio Button is selected. The selected option description will appear in a label control, at the left side of the Unbound TextBox in an animated style, by moving the Text from right to left.

The Command Button Click will close the Form.

We already created wrapper classes for the ComboBox and ListBox in the earlier episodes. The OptionGroup Control is new in this Series of Tutorials and needs a Wrapper Class. When we place an OptionGroup Control on the Form the default name used by Microsoft Access is something like Frame7. So we will create a Wrapper Class for the OptionGroup Control with the name OptFrame.

The OptFrame Wrapper Class VBA Code.

Option Compare Database
Option Explicit

Private WithEvents Opt As Access.OptionGroup
Private frm As Access.Form

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'OptionGroup Wrapper Class
'Author: a.p.r. pillai
'Date  : 31/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get opt_Frm() As Form
    Set opt_Frm = frm
End Property

Public Property Set opt_Frm(ByRef ofrm As Form)
    Set frm = ofrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get o_opt() As OptionGroup
    Set o_opt = Opt
End Property

Public Property Set o_opt(ByRef mopt As OptionGroup)
    Set Opt = mopt
End Property

'Event Subroutines Code
Private Sub opt_Click()
Dim Rslt As Variant
Dim Cap(1 To 3) As String
Static strText As String

Cap(1) = "Highest Freight Value:"
Cap(2) = " Lowest Freight Value:"
Cap(3) = "  Total Freight Value:"

Select Case Opt.Name
    Case "Frame7"
        Select Case Opt.Value
            Case 1
            'Repeated Clicks on the same option is ignored.
            If strText = Cap(1) Then Exit Sub
                Rslt = DMax("Freight", "OrderDetailQ1")
            Case 2
                If strText = Cap(2) Then Exit Sub
                Rslt = DMin("Freight", "OrderDetailQ1")
                
            Case 3
                If strText = Cap(3) Then Exit Sub
                Rslt = DSum("Freight", "OrderDetailQ1")
        End Select
End Select
       frm!Result = Rslt
       strText = Cap(Opt)
       
       Call Animate(strText) 'Label Animation
End Sub

Private Sub Animate(ByVal txt As String)
'Label Animation
Dim L As Double
Dim n As String
Dim T As Double
Dim j As Integer

L = Len(txt)
txt = Space(L) & txt

For j = 1 To Len(txt) - L
 n = Left(txt, 1)
 txt = Right(txt, Len(txt) - 1)
 txt = txt & n
 frm.lblResult.Caption = Left(txt, L)

    Delay 0.02 'delay 20 milliseconds

Next
End Sub

The OptionGroup Control object Opt Property is qualified with the Keyword WithEvents is declared in the global area of the Class Module. The Wrapper Class Module name is OptFrame. There is a Form object Property with the name frm also declared in the Global Area followed by the Property Procedures for the Global Properties.

The Opt_Click() Event Subroutine.

Private Sub opt_Click()
Dim Rslt As Variant
Dim Cap(1 To 3) As String
Static strText As String

Cap(1) = "Highest Freight Value:"
Cap(2) = " Lowest Freight Value:"
Cap(3) = "  Total Freight Value:"

Select Case Opt.Name
    Case "Frame7"
        Select Case Opt.Value
            Case 1
                If strText = Cap(1) Then Exit Sub
                Rslt = DMax("Freight", "OrderDetailQ1")
            Case 2
                If strText = Cap(2) Then Exit Sub
                Rslt = DMin("Freight", "OrderDetailQ1")
                
            Case 3
                If strText = Cap(3) Then Exit Sub
                Rslt = DSum("Freight", "OrderDetailQ1")
        End Select
End Select
       frm!Result = Rslt
       strText = Cap(Opt)
       
       Call Animate(strText) 'Label Animation
End Sub

There are three options in the OptionGroup Control on the Form to extract the Highest, Lowest, and Total Freight Values from the Order Sales transactions, for the selected Employee ID in the ComboBox cboEmp.

The OptionGroup Button Click will retrieve the values from OrderDetailQ1 using the DMax(), DMin(), and DSum() Functions from the filtered Order Details data and display it in the Rslt TextBox, on the left side of the OptionGroup Control.

The displayed freight value's category description, defined in the Cap() Array is picked using the selected OptionGroup Control's Radio Button Index Number. This text is passed over to the Animate() Subroutine as the parameter. This is displayed on the Label control Caption at the left side of the TextBox Rslt. The Text is displayed in an animated style, exposing the description character by character moving from right to left till the full text is exposed.

The statement If strText = Cap(n) Then Exit Sub ignores the Animation from repeating when Clicked on the Body of the OptionGroup Frame.

The Label Animation.

The Animate() Subroutine Code segment is given below:

Private Sub Animate(ByVal txt As String)
'Label Animation
Dim L As Double
Dim n As String
Dim T As Double
Dim j As Integer

L = Len(txt)
txt = Space(L) & txt 'Add spaces at the left side

For j = 1 To Len(txt) - L
 n = Left(txt, 1)
 txt = Right(txt, Len(txt) - 1)
 txt = txt & n
 frm.lblResult.Caption = Left(txt, L)
 
 Delay 0.02 ' Pause 20 Milliseconds
 
 Next
End Sub

The length of the Parameter value in the Variable txt is calculated and stored in variable L. The parameter variable txt content is modified by adding an equal number of spaces of its original length at the left side.

The Animation Sequence.

The For . . . Next Loop is set to run to the original length in Variable L. In the next three lines of Code, removes one character from the left side of the String and add it to the right end of the String. Then the leftmost L length of characters is displayed in the Caption Property of the lblResult Label Control.

The next batch of characters display is delayed by 20 milliseconds and the same action is repeated till the complete description is displayed on the Label Control.

The Delay() Function VBA Code in the Standard Module is given below for information:

Public Sub Delay(ByVal Sleep As Double)
Dim T As Double

T = Timer
Do While Timer < T + Sleep
    DoEvents
Loop
End Sub

The OptObject_Init Class Module VBA Code.

Option Compare Database
Option Explicit

Private iFrm As Access.Form

Private LstB As OptListBox
Private txt As OptTextBox
Private Fram As OptFrame
Private wcbo As optCombo
Private wcmd As OptCmdButton

Private Coll As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Combo and Option Group Controls
'Author: a.p.r. pillai
'Date  : 31/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get m_Frm() As Form
    Set m_Frm = iFrm
End Property

Public Property Set m_Frm(ByRef mfrm As Form)
    Set iFrm = mfrm
    
    iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1)
    iFrm.List0.Requery

Call Class_Init
End Property

'Events Enabling Subroutine
Private Sub Class_Init()
Dim ctl As Control

Const EP = "[Event Procedure]"


For Each ctl In iFrm.Controls 'Scan for Controls

    Select Case TypeName(ctl)
            Case "OptionGroup"
              Select Case ctl.Name
                Case "Frame7" 'Option Group Name
                    Set Fram = New OptFrame 'Create Instance
                    Set Fram.opt_Frm = iFrm 'Assign Form Object
                    Set Fram.o_opt = ctl   'TextBox
                    
                        Fram.o_opt.OnClick = EP
                        
                    Coll.Add Fram 'Save EmpTextBox Class
                    Set Fram = Nothing 'Erase temp Instance
              End Select


        Case "ComboBox"
                    Set wcbo = New optCombo
                    Set wcbo.cbo_Frm = iFrm
                    Set wcbo.c_cbo = ctl
                    
                   wcbo.c_cbo.AfterUpdate = EP
                   wcbo.c_cbo.OnGotFocus = EP
                   wcbo.c_cbo.OnLostFocus = EP
                    
                    Coll.Add wcbo
                    Set wcbo = Nothing

        Case "TextBox"
                    Set txt = New OptTextBox
                    Set txt.tx_Frm = iFrm
                    Set txt.t_Txt = ctl
                    
                        txt.t_Txt.OnGotFocus = EP
                        txt.t_Txt.OnLostFocus = EP
                    
                    Coll.Add txt
                    Set txt = Nothing
                    
        Case "ListBox"
                    Set LstB = New OptListBox
                    Set LstB.lst_Frm = iFrm
                    Set LstB.m_Lst = ctl
                    
                        LstB.m_Lst.OnGotFocus = EP
                        LstB.m_Lst.OnLostFocus = EP
                    
                    Coll.Add LstB
                    Set LstB = Nothing
                    
        Case "CommandButton"
                    Set wcmd = New OptCmdButton
                    Set wcmd.cmd_Frm = iFrm
                    Set wcmd.c_cmd = ctl
                    
                   wcmd.c_cmd.OnClick = EP
                    
                    Coll.Add wcmd
                    Set wcmd = Nothing
    
    End Select
Next

End Sub

Private Sub Class_Terminate()
'Delete Collection Object contents
    Do While Coll.Count > 0
        Coll.Remove 1
    Loop
    
    Set iFrm = Nothing
End Sub

As usual, all the Access Object Wrapper Classes are defined as Properties in the Global Area of the Module followed by the Property Procedures. After the Form Object is assigned to the iFrm object, in the Set m_frm() Property Procedure two statements are executed for refreshing the ComboBox and ListBox objects to initialize with default values in both these Form Controls. From there the Class_Init() Subroutine is called.

The procedures written there are explained in detail in earlier episodes and I am sure you are well versed in the procedure.

But, I would like to draw your attention to the controls on the Form. There is only one instance each of Combobox, ListBox, TextBox, Command Button, OptionGroup Control, and Chart Object on the Form. Even though Chart Object also has several Events and can have a Wrapper Class, if we plan to capture those Events in a standalone Class Module.

When to create a Wrapper Class?

There may be more than one instance of a particular object of the same type on the Form (like TextBox) but if there is only one instance of any Control, that needs one or more Event Subroutines, then it is not necessary to use the Wrapper Class for it. We can create a single Instance of such Objects in the Declaration area of the OptObject_Init Class Module (the intermediate Class Module), qualified with the WithEvents keyword. Then assign the Control instances references from the Form, enable their Events, and write the Event Subroutines in the OptObject_Init Class Module.

We need a Wrapper Class only when more than one instance of the Objects of the same Type needs to be enabled with the Event Procedures. In this frm_OptionGroup Form, there is only one Instance of the TextBox, ComboBox, ListBox, and Command Button on the Form.

Since the OptionGroup Control (Frame7) is the new entry, in the Streamlining of Form Module Code Series of Articles, we created a Wrapper Class OptFrame for that control alone, and for others we will create a single instance of each control qualified with the WithEvents keyword, to capture the Events in the OptObject_Init Class Module. They will be enabled with their required Events and write their Event Subroutines in this Intermediate Class Module.

Even though we have already created and used Wrapper Classes for these Objects earlier, using them in this case involves more VBA Code than necessary for a single instance for those Objects.

So, I have created two Forms for Demo purposes:

frm_OptionGroup - All Control's Wrapper Classes are used in the OptObject_Init Class

frm_OptionGroup2 - Only Opt_Frame Wrapper Class is in the Opt_Object_Init2 Class.

The Opt_Object_Init2 Class Module Code.

The frm_OptionGroup2 Form's Intermediate Class Module (Opt_Object_Init2) Code is given below:

Option Compare Database
Option Explicit

Private WithEvents txt As Access.TextBox
Private WithEvents cmd As Access.CommandButton
Private WithEvents cbo As Access.ComboBox
Private WithEvents Lst As Access.ListBox

Private Fram As Opt_Frame2
Private iFrm As Access.Form
Private Coll As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Combo and Option Group Controls
'Author: a.p.r. pillai
'Date  : 31/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get m_Frm() As Form
    Set m_Frm = iFrm
End Property

Public Property Set m_Frm(ByRef mfrm As Form)
    Set iFrm = mfrm
    
    iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1)
    iFrm.List0.Requery
    
    Set txt = iFrm.Result
    Set cmd = iFrm.cmdClose
    Set cbo = iFrm.cboEmp
    Set Lst = iFrm.List0
    
Call Class_Init
End Property

'Events Enabling Subroutine
Private Sub Class_Init()
Dim ctl As Control

Const EP = "[Event Procedure]"

'Scan for Form Controls
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls 'Find TextBox, ComboBox & CommandButtons

    Select Case TypeName(ctl)
            Case "OptionGroup"
              Select Case ctl.Name
                Case "Frame7" 'Option Group Name
                    Set Fram = New Opt_Frame2 'Create Instance
                    Set Fram.opt_Frm = iFrm 'Assign Form Object
                    Set Fram.o_opt = ctl   'TextBox
                    
                        Fram.o_opt.OnClick = EP
                        
                    Coll.Add Fram 'Save EmpTextBox Class
                    Set Fram = Nothing 'Erase temp Instance
              End Select

        Case "CommandButton"
                   cmd.OnClick = EP
                    

        Case "ComboBox"
                   cbo.AfterUpdate = EP
                   cbo.OnGotFocus = EP
                   cbo.OnLostFocus = EP
          
        Case "TextBox"
            Select Case ctl.Name
                Case "Result"
                   txt.OnGotFocus = EP
                   txt.OnLostFocus = EP
            End Select
                    
        Case "ListBox"
                Lst.OnGotFocus = EP
                Lst.OnLostFocus = EP
                        
    End Select
Next

End Sub

Private Sub Class_Terminate()
'Delete Collection Object contents
    Do While Coll.Count > 0
        Coll.Remove 1
    Loop
    
    Set iFrm = Nothing
End Sub

'Event Subroutines
Private Sub txt_GotFocus()
        GFColor iFrm, txt 'Field Highlight
End Sub

Private Sub txt_LostFocus()
    LFColor iFrm, txt 'Field Highlight
End Sub

Private Sub cmd_Click()
    If MsgBox("Close " & iFrm.Name & " Form?", vbYesNo + vbQuestion, "cmd_Click") = vbYes Then
        DoCmd.Close acForm, iFrm.Name
        Exit Sub
    End If
End Sub

Private Sub cbo_GotFocus()
    GFColor iFrm, cbo 'ComboBox highlight
    
    'Reset OptionGroup to default settings
    iFrm.Frame7 = Null 'Reset earlier selection of OptionGroup option
    iFrm!lblResult.Caption = "Result"
    iFrm.Result.Value = 0
    
End Sub

Private Sub cbo_LostFocus()
    LFColor iFrm, cbo 'ComboBox highlight
End Sub

Private Sub cbo_AfterUpdate()
  iFrm.List0.Requery
  
End Sub

'Event Subroutines Code
Private Sub lst_GotFocus()
    GFColor iFrm, Lst 'ListBox highlight
End Sub

Private Sub lst_LostFocus()
    LFColor iFrm, Lst 'ListBox highlight
End Sub

Segmentwise Review of the VBA Code.

The Global Declaration Code segment is given below for review:

Option Compare Database
Option Explicit

Private WithEvents txt As Access.TextBox
Private WithEvents cmd As Access.CommandButton
Private WithEvents cbo As Access.ComboBox
Private WithEvents Lst As Access.ListBox

Private Fram As Opt_Frame2
Private iFrm As Access.Form
Private Coll As New Collection

All single object instance declarations are given at the global area of the Module, as we did at the beginning of this series of Tutorials.

Since OptionGroup Control is a new entry in this series its Wrapper Class is included here. The Form and Collection Object declarations comes next. The Collection Object declaration is included only for the OptionGroup Control with the name Frame7.

The Property Procedure Segment.

Next, the iFrm's Property Procedure Code Segment is given below:

'Form's Property GET/SET Procedures
Public Property Get m_Frm() As Form
    Set m_Frm = iFrm
End Property

Public Property Set m_Frm(ByRef mfrm As Form)
    Set iFrm = mfrm
    
    'Set the ComboBox EmployeeID first item as default value
    iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1)
    iFrm.List0.Requery 'Refresh the Order Details ListBox
    
    Set txt = iFrm.Result
    Set cmd = iFrm.cmdClose
    Set cbo = iFrm.cboEmp
    Set Lst = iFrm.List0
    
Call Class_Init
End Property

In the Set m_Frm() Property Procedure the ComboBox and ListBox default values are assigned when the Form is open.

Next, all the single object references on the Form are assigned to the Objects declared in the global area and then Called the Class_Init() Subroutine.

The Class_Init() Subroutine.

'Events Enabling Subroutine
Private Sub Class_Init()
Dim ctl As Control

Const EP = "[Event Procedure]"

'Scan for Form Controls
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls 'Find TextBox, ComboBox & CommandButtons

    Select Case TypeName(ctl)
            Case "OptionGroup"
              Select Case ctl.Name
                Case "Frame7" 'Option Group Name
                    Set Fram = New Opt_Frame2 'Create Instance
                    Set Fram.opt_Frm = iFrm 'Assign Form Object
                    Set Fram.o_opt = ctl   'TextBox
                    
                        Fram.o_opt.OnClick = EP
                        
                    Coll.Add Fram 'Save EmpTextBox Class
                    Set Fram = Nothing 'Erase temp Instance
              End Select

        Case "CommandButton"
                   cmd.OnClick = EP
                    

        Case "ComboBox"
                   cbo.AfterUpdate = EP
                   cbo.OnGotFocus = EP
                   cbo.OnLostFocus = EP
          
        Case "TextBox"
            Select Case ctl.Name
                Case "Result"
                   txt.OnGotFocus = EP
                   txt.OnLostFocus = EP
            End Select
                   
        Case "ListBox"
                Lst.OnGotFocus = EP
                Lst.OnLostFocus = EP
                        
    End Select
Next

End Sub

The Class_Init() Subroutine starts with the usual way of scanning for the Controls and checks for the OptionGroup control with the name Frame7, enables the Click Event, and then adds it to the Collection Object.

Next, other Form Controls' references are already assigned in the Set m_frm() Property Procedure and are enabled with the Events. We will be writing the Event Subroutine Code in this Class Module itself.

Next, in the TextBox's case, we have another TextBox (EID) that is kept hidden on the Form. Even though it is hidden it will appear in the scanning cycle and it will be enabled with the Events. Since there is no Event Procedure Code for that TextBox in this Module it will not have any impact. But, we would like to check for that specific TextBox (Resul) for clarity and enable its Events. This will ignore the EID TextBox. Next, the ListBox is also enabled with the required Events.

The Event Subroutines of Single Control Instance Cases.

Next, the Event Subroutines Segment Code which runs in the Opt_Object_Init2 Class.

'Event Runs automatically when the Form is Closed.
Private Sub Class_Terminate()
'Delete Collection Object contents
    Do While Coll.Count > 0
        Coll.Remove 1
    Loop
    
    Set iFrm = Nothing
End Sub

'TextBox Event Subroutines for highlighting the control
Private Sub txt_GotFocus()
        GFColor iFrm, txt 'Field Highlight
End Sub

Private Sub txt_LostFocus()
    LFColor iFrm, txt 'Field Highlight
End Sub

'Command Button Subroutines
Private Sub cmd_Click()
    If MsgBox("Close " & iFrm.Name & " Form?", vbYesNo + vbQuestion, "cmd_Click") = vbYes Then
        DoCmd.Close acForm, iFrm.Name
        Exit Sub
    End If
End Sub

'ComboBox Subroutines
Private Sub cbo_GotFocus()
    GFColor iFrm, cbo 'ComboBox highlight
    
    'Reset OptionGroup to default settings
    iFrm.Frame7 = Null 'Reset earlier selection of OptionGroup option
    iFrm!lblResult.Caption = "Result"
    iFrm.Result.Value = 0
    
End Sub


Private Sub cbo_LostFocus()
    LFColor iFrm, cbo 'ComboBox highlight
End Sub

Private Sub cbo_AfterUpdate()
  iFrm.List0.Requery
  
End Sub

'ListBox Event Subroutines Code
Private Sub lst_GotFocus()
    GFColor iFrm, Lst 'ListBox highlight
End Sub

Private Sub lst_LostFocus()
    LFColor iFrm, Lst 'ListBox highlight
End Sub

All Event Subroutines are written with the Object Name declared in the Global Declaration Area in the Opt_Object_Init2 Class Module.

Demo Database Download

Streamline11.zip

Streamlining Form Module Code in Standalone Class Module.

Earlier Pages.

Streamlining Form Module Code - Part Ten

a.p.r. pillaiAugust 19, 2023 No comments

Creating Access Menu using Tabcontrol, ListBox, and Command Buttons.

We will take up the TabControl-based Menu design task after a few minutes.

Organizing the Wrapper Classes for Different Forms.

Let us see how we can organize the Wrapper Classes when there are several Forms and several Wrapper Classes for each Form in a database. They must be organized properly to avoid mix-ups like the wrapper class containing the Event Subroutines of Form A is added in the WrapObject_Init Class of Form B.

Wrapper Class Templates.

Create one set of Wrapper Class Templates for all frequently used controls like TextBoxes, CommandButtons, ComboBoxes, and others. Make Copies of Wrapper Classes and change their names, with some short name prefixes to the Wrapper Class Names like EmpTextBox (Emp for Employees Form) to relate the Wrapper Classes to the Employee's Form. All other controls ComboBoxes, Command Buttons and others for the Employees Form will have the same name Prefix Emp_TextBox format, or EmpTextBox format, Emp_CmdButton, Emp_ComboBox, and so on.

After going through the earlier nine Articles on the Streamlining of Form Module VBA Code topic you are now familiar with this new VBA Coding method and know how to create a new Wrapper Class if it is not available among the existing Templates when a new Control is added on the Form.

Forms With SubForms.

Form with SubForms don't need any separate Classes, need only one Wrapper Class for the Main Form. You can see an example of the Employees Main Form with the Orders Subform in episode nine. The TextBox and ComboBox controls are scanned in the EmpObject_Init Class in a separate For . . . Next Loop with the SubForm Reference and how the SubForm Control references are being mapped and added to the Collection Object. Their Event Subroutines can be written using the main Form Property like EmpTextBox wrapper Class Property name Txt for the TextBox on the Order SubForm like:

'Data Update Reconfirm to Save the Change
Private Sub txt_BeforeUpdate(Cancel As Integer)
Dim msg As String

msg = "Field Name: " & Txt.Name & vbCr & _
        "Original Value '" & UCase(Txt.OldValue) & "'" & _
        vbCr & "Change to: '" & UCase(Txt.Value) & "'"
    
    If MsgBox(msg, vbYesNo + vbQuestion, _
        Txt.Name & "_BeforeUpdate()") = vbNo Then
        Cancel = True
    End If


End Sub

The Txt object will have the correct reference of the SubForm TextBox and will execute the code for the TextBox in the Orders SubForm.

Let us see an example in the Sub Txt_GotFocus() Event Subroutine for not highlighting the 'OrderDate' TextBox on the Orders SubForm in the Employees Main Form.

Private Sub txt_GotFocus()
    If Txt.Name = "OrderDate" Then
         'No highlight
    Else
          GFColor frm, Txt
    End If
End Sub

We can address a control like any other control on the Main Form. The OrderDate field is not highlighted when it becomes current.

The next sample Code skips highlighting all TextBoxes on the Orders SubForm.

Private Sub txt_GotFocus()
'https://learn.microsoft.com/en-us/office/vba/api/access.subform.parent
   Select Case Txt.Parent.Name         
    	Case "Orders"   'Orders SubForm has the parent Property set
          'Do Nothing
    	Case Else
           GFColor frm, Txt 'Field Highlight
   End Select
End Sub

Some examples of Wrapper Class Names are given below.

Wrapper Classes for the Employees Form.

EmpObject_Init
EmpTextBox
EmpCmdButton
EmpComboBox
EmpOptionGrp

Wrapper Classes for Orders Main Form.

Order_Object_Init
Order_TextBox
Order_CmdButton
Order_ListBox
Order_TabCtl

Reusing Streamlined Coding Procedures in Other Projects.

Now the Question of how to Reuse the Classes in another Database?

Different Projects and different requirements. Most of the Subroutines written for a particular Form, based on its specific requirements, cannot be used without changes in another Project. But there are Event Subroutine Codes mentioned earlier (TextBox highlight, OnDirty, OnBeforeUpdate) or similar tasks you find can be used without change in other Projects.

In either case, the Backbone of this new streamlined Coding Procedure with less Code in the Wrapper Classes can be Exported into other Projects. Working with the Wrapper Classes for Coding independently and not mixing the Coding work with the Form design task will make the way for faster completion of Projects.

You can create a set of Wrapper Class Templates for frequently used Form Controls like:

Access.TextBox
Access.CommandButton
Access.ComboBox and others

with reusable Event Subroutines, like TextBox, ComboBox, ListBox highlighting OnGotFocus, OnLostFocus Event Subroutines, TextBox OnDirty, OnBeforeUpdate Event Subroutines, for safeguarding against inadvertent changes, and similar reusable code you find can be included in the Wrapper Class Templates. Any Standard Module-based Common Functions in use in the Event Subroutines, like the GFColor, and LFColor Functions we used for highlighting the TextBox Controls must accompany the Wrapper Classes.

All Wrapper Classes we create will have a Form Property along with the Control Object Property like TextBox and the SET/GET Property Procedures for both the above objects as their body. The Wrapper Class Templates can be saved in a separate database. The Classes saved in this database can have the prefix .cls like:

clsObject_Init
clsTextBox
clsCmdButton and others.

How to use the Code from the Template Database.

Attach this database to your New Project as a Library database.
Create a new Class Module in the new Database.
Change its name to match the first Wrapper Class in the attached database.
Right-Click on the Wrapper Class Module of the attached Database and select View Code from the displayed shortcut Menu. The Code will be displayed in the VBA Window.
Copy the Code and Paste it into the new Class Module and save it.
Repeat this procedure to transfer all the Wrapper Class Templates to the New Database.
Then remove the attached database from the Reference Library.

Alternative Method.

Even better, you can Export the Wrapper Class Templates from the VBA Window into a dedicated Folder in the Disk-Drive as separate Class Files with the file extension .cls, and you can Import them one by one into your new Projects.

Access Menu with TabControl.

The finished Menu View Image is given below:

There are three layers of Menu Options (Tables, Forms, and Reports) that will appear in the same place when selected by clicking on the Command Buttons given on the left side.

The Menu Design Image is given below.

There are three Pages in the Tab Control, with Page Names: Tables, Forms, and Reports.

Each Tab Page has a ListBox Control with the same dimension and is positioned on the same left and top values. The idea is to display all the Menu Pages: Tables, Forms, and Reports one after the other in the same position on the TabControl.

The following Property settings will change the display style of the Tab Control to the style shown in the first Image given above:

Tab Style: None
Back Style: Transparent
Border Style: Transparent

When the Tab Pages are hidden the Command Button Click will change the Pages and the Page-Change Event will Fire. The Click Event of the Command Button will highlight the Border of the Command Button to indicate the current selection. Besides that, the current Menu selection is announced by a female Voice also.

Double-Clicking on the List Item will display the Table or Form or Report on the screen. This action also will announce the type of object that is currently on display.

The ListBox Menu Source Items Employees, Orders, and Customers are added in the Value List in the format 1;"Employees";2;"Orders";3;"Customers" for all the three Menus Tables, Forms & Reports.

The TabLst_Object_Init Class

The TabLst_Objectr_Init Class Module Code is given below:

Option Compare Database
Option Explicit

Private iFrm As Access.Form
Private Coll As New Collection

Private tbc  As TabLst_TabCtl
Private wcmd As TabLst_CmdButton
Private lst  As TabLst_ListBox

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Tab Control Class_Init Class
'Author: a.p.r. pillai
'Date  : 16/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get m_Frm() As Form
    Set m_Frm = iFrm
End Property

Public Property Set m_Frm(ByRef mfrm As Form)
    Set iFrm = mfrm
	Call Class_Init
End Property

'Events Enabling Subroutine
Private Sub Class_Init()
Dim ctl As Control

Const EP = "[Event Procedure]"

'Scan for Tab Control, CommandButton & ListBox Controls
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls

    Select Case ctl.ControlType
        Case acTabCtl
            Set tbc = New TabLst_TabCtl
            Set tbc.tb_Frm = iFrm
            Set tbc.tb_tab = ctl
                tbc.tb_tab.OnChange = EP
                
                Coll.Add tbc
            Set tbc = Nothing
    
        Case acListBox
            Set lst = New TabLst_ListBox
            Set lst.lst_Frm = iFrm
            Set lst.m_lst = ctl
            
            lst.m_lst.OnDblClick = EP
           
            Coll.Add lst
            Set lst = Nothing

        Case acCommandButton
            Select Case ctl.Name
                Case "cmdTables", "cmdForms", "cmdReports", "cmdExit"
                    Set wcmd = New TabLst_CmdButton
                    Set wcmd.cmd_Frm = iFrm
                    Set wcmd.c_cmd = ctl
                    
                    wcmd.c_cmd.OnClick = EP
                    Coll.Add wcmd
                    Set wcmd = Nothing
            End Select
            
   End Select
Next

End Sub

Private Sub Class_Terminate()
'Delete Collection Object contents
    Do While Coll.Count > 0
        Coll.Remove 1
    Loop
    
    Set iFrm = Nothing
End Sub

In the Global Declaration area, the Form and Collection Property declarations are made, followed by the Tab Control, Command Button, and ListBox Wrapper Class Property Declarations.

Next, the Set/Get Property Procedures for the Form. The Class_Init() Subroutine is Called From the Set Property Procedure of the Form object. In the For . . . Next Loop we look for our TabCtl, ListBox, and Command Button Controls.

The TabCtl control Wrapper Class TabLst_TabCtl Instance Properties tb_Frm and tb_Tab are assigned with the iFrm, ctl References. The TabCtl Control's Tab Page Change Event is enabled and then the TabLst_TabCtl Wrapper Class Instance is added to the Collection Object.

All three ListBox Properties are enabled with the DblClick() Event in the TabLst_ListBox Wrapper Class Instances and added to the Collection Object. Similarly, the Command Button Wrapper Class TabLst_CmdButton Instances are enabled with the Click Event and added to the Collection Object.

The TabLst_TabCtl Class

The TabLst_TabCtl Wrapper Class Module Code is given below.

Option Compare Database
Option Explicit

Private tbfrm As Access.Form
Private WithEvents Tb As Access.TabControl

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'TabCtl Wrapper Class
'Author: a.p.r. pillai
'Date  : 16/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get tb_Frm() As Form
    Set tb_Frm = tbfrm
End Property

Public Property Set tb_Frm(ByRef tabfrm As Form)
    Set tbfrm = tabfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get tb_tab() As Access.TabControl
    Set tb_tab = Tb
End Property

Public Property Set tb_tab(ByRef ptab As Access.TabControl)
    Set Tb = ptab
End Property

'Event Subroutines Code
Private Sub tb_Change()
   Select Case Tb.Value
        Case 0
            MsgBox "Change Event: Page(0)"
        Case 1
            MsgBox "Change Event: Page(1)"
        Case 2
            MsgBox "Change Event: Page(2)"
    End Select
End Sub

When the TabControl TabPage Change Event is fired will be captured here and displays the Page Index Number in the MsgBox.

The Command Button Wrapper Class.

 
Option Compare Database
Option Explicit

Private WithEvents cmdfrm As Form
Private WithEvents cmd As CommandButton 'CommandButton object
Dim L As Integer
Dim ForeColor As Long

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Command Button Events
'Author: a.p.r. pillai
'Date  : 16/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get cmd_Frm() As Form
    Set cmd_Frm = cmdfrm
End Property

Public Property Set cmd_Frm(ByRef cfrm As Form)
    Set cmdfrm = cfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get c_cmd() As CommandButton
    Set c_cmd = cmd
End Property

Public Property Set c_cmd(ByRef pcmd As CommandButton)
    Set cmd = pcmd
End Property

'Event Subroutines
Private Sub cmd_Click()
Select Case cmd.Name
  Case "cmdExit"
  Announce "Close the Form Now?"
    If MsgBox("Close the Form Now?", vbOKCancel + vbQuestion, "cmd_Click") = vbOK Then
        DoCmd.Close acForm, cmdfrm.Name
        Exit Sub
    End If
    
  Case "cmdTables"
    'Command Button Border Highlight
      cmd.BorderWidth = 2
      cmdfrm.cmdForms.BorderWidth = 0
      cmdfrm.cmdReports.BorderWidth = 0
      
    cmdfrm.TabCtl0.Pages(0).SetFocus
    Announce "Tables Menu Active."
    
  Case "cmdForms"
    'Command Button Border Highlight
    cmd.BorderWidth = 2
    cmdfrm.cmdTables.BorderWidth = 0
    cmdfrm.cmdReports.BorderWidth = 0
    
    cmdfrm.TabCtl0.Pages(1).SetFocus
        Announce "Forms Menu Active."

  Case "cmdReports"
  'Command Button Border Highlight
    cmd.BorderWidth = 2
    cmdfrm.cmdForms.BorderWidth = 0
    cmdfrm.cmdTables.BorderWidth = 0
    
    cmdfrm.TabCtl0.Pages(2).SetFocus
        Announce "Reports Menu Active."
End Select
End Sub

The three Command Buttons to the left side of the TabControl is the replacement of TabControl Page Buttons to hide the identity features of the TabControl. This is the secret of several layers of different Menus appearing one at a time in the same location. More layers of Menu Pages can be added to the TabControl with ListBoxes and Command Button.

The following Properties of the TabControl are set to hide the features of the TabControl:

Style: None
Back Style: Transparent
Border Style: Transparent

The ListBox Wrapper Class.

Option Compare Database
Option Explicit

Private lstfrm As Access.Form
Private WithEvents lst As Access.ListBox

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'ListBox Wrapper Class
'Author: a.p.r. pillai
'Date  : 16/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get lst_Frm() As Form
    Set lst_Frm = lstfrm
End Property

Public Property Set lst_Frm(ByRef mFrm As Form)
    Set lstfrm = mFrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get m_lst() As ListBox
    Set m_lst = lst
End Property

Public Property Set m_lst(ByRef mLst As ListBox)
    Set lst = mLst
End Property

Private Sub lst_DblClick(Cancel As Integer)
Dim i As Integer
Dim Menu(1 To 3) As String
Dim Obj(1 To 3) As String

i = Nz(lst.Value, 0)

Const Opn = "Opening "

Menu(1) = "Table "
Menu(2) = "Form "
Menu(3) = "Report "

Obj(1) = "Employees"
Obj(2) = "Orders"
Obj(3) = "Customers"

Select Case lst.Name
    Case "LstTables"
        Select Case i
            Case 1, 2, 3
                Announce Opn & Menu(1) & Obj(i) 'Speak
                DoCmd.OpenTable Obj(i), acViewNormal
        End Select
        
    Case "LstForms"
        Select Case i
            Case 1, 2, 3
                Announce Opn & Menu(2) & Obj(i) 'Speak
                DoCmd.OpenForm Obj(i), acViewNormal
        End Select
        
    Case "LstReports"
        Select Case i
            Case 1, 2, 3
                Announce Opn & Menu(3) & Obj(i) 'Speak
                DoCmd.OpenReport Obj(i), acViewReport
        End Select
End Select
End Sub

At the beginning of the Listbox's Double-Click Event Subroutine, a few Array Variables are Initialised with the Menu Names and menu item names to compose the Speech Text to Announce the Menu item selection. This method also reduces the File opening statements from six to two lines each (excluding the array lines) for Tables, Forms, and Reports.

The normal Coding will look like the following:

Select Case lst.Name
    Case "LstTables"
        Select Case i
            Case 1
                Announce "Opening Table Employees" 'Speak
                DoCmd.OpenTable "Employees", acViewNormal
            Case 2
                Announce "Opening Table Orders" 'Speak
                DoCmd.OpenTable "Orders", acViewNormal
            Case 3
                Announce "Opening Table Customers" 'speak
                DoCmd.OpenTable "Customers", acViewNormal
        End Select
        
    Case "LstForms"
        Select Case i
            Case 1
                Announce "Opening Form Employees" 'Speak
                DoCmd.OpenForm "Employees", acViewNormal
            Case 2
                Announce "Opening Form Orders" 'Speak
                DoCmd.OpenForm "Orders", acViewNormal
            Case 3
                Announce "Opening Form Customers" 'speak
                DoCmd.OpenForm "Customers", acViewNormal
        End Select
        
    Case "LstReports"
        Select Case i
            Case 1
                Announce "Opening Report Employees" 'Speak
                DoCmd.OpenReport "Employees", acViewReport
            Case 2
                Announce "Opening Report Orders" 'Speak
                DoCmd.OpenReport "Orders", acViewReport
            Case 3
                Announce "Opening Report Customers" 'speak
                DoCmd.OpenReport "Customers", acViewReport

        End Select
End Select

Microsoft Speech-Service VBA Code is given below. The Subroutine Code is in the Standard Module.

Sub Announce(ByVal Txt As String, Optional Gender As String = "Female")
'https://learn.microsoft.com/en-us/azure/ai-services/speech-service/
    Dim obj As Object
    Set obj = CreateObject("SAPI.SpVoice")
    Set obj.Voice = obj.GetVoices("Gender = " & Gender).Item(0)
    obj.Speak Txt
End Sub

The TabLst_ListBox Wrapper Class DblClick() Event Subroutines Runs the Menu Options Tables, Forms, and Reports opening activities.

Streamline10.zip

LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Report Module in Class Module-2

Introduction.

Hiding Report Lines Conditionally.

The Report Detail_Format, Report_Page Events.

ReportOption Function VBA Code.

The Report StudentsPassFail_Normal Module VBA Code.

The StudentPassFail_Normal Report Module Segmentwise Code Review.

The Report_Load Event Subroutine.

The PageBorder() Function Code In Standard Module.

The Streamlining Procedure of the Report VBA Code.

Demo Database Download Link. Change Database.

Streamlining Report Module Code in Class Module.

Introduction.

Report Module VBA Code in Standalone Class Module.

The Report Section Print Event Subroutines.

Organize the Existing Form Module Code for the Standalone Class Module.

The Traditional Way of Coding.

The Students Examination Result Report Image.

Review of the VBA Code in the Report Class Module.

The Changed VBA Code in the Standalone Class Module.

Review of the Report Wrapper Class VBA Code.

Report Class Module VBA Code.

The Report Detail_Print() Event.

The DrawCircle() Subroutine Code.

The DrawCircle() Subroutine VBA Code Review.

The ReportFooter_Print() Event Subroutine.

Demo Database Download Link.

Streamlining Form Module Code - Part Eleven

Introduction.

ComboBox and OptionGroup Control.

OrderDetailQ1 SQL.

OrderSummaryQ1 SQL.

Freight Summary Data for the Chart.

How ComboBox, ListBox, and the Chart linked Together?

The OptionGroup Control.

The OptFrame Wrapper Class VBA Code.

The Opt_Click() Event Subroutine.

The Label Animation.

The Animation Sequence.

The OptObject_Init Class Module VBA Code.

When to create a Wrapper Class?

The Opt_Object_Init2 Class Module Code.

Segmentwise Review of the VBA Code.

The Property Procedure Segment.

The Class_Init() Subroutine.

The Event Subroutines of Single Control Instance Cases.

Demo Database Download

Streamlining Form Module Code in Standalone Class Module.

Earlier Pages.

Streamlining Form Module Code - Part Ten

Creating Access Menu using Tabcontrol, ListBox, and Command Buttons.

We will take up the TabControl-based Menu design task after a few minutes.

Organizing the Wrapper Classes for Different Forms.

Wrapper Class Templates.

Forms With SubForms.

Reusing Streamlined Coding Procedures in Other Projects.

How to use the Code from the Template Database.

Alternative Method.

Access Menu with TabControl.

The TabLst_Object_Init Class

The TabLst_TabCtl Class

The Command Button Wrapper Class.

The ListBox Wrapper Class.

Streamlining Form Module Code in Standalone Class Module.

Earlier Pages.

Buy Progress Bar Control

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Useful-Links

Popular Posts

About Me

Blog Archive

Labels

Report Abuse

Statcounter