Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Streamlining Event Procedures RGBColor Wizard

RGB Color Wizard.

Create your own RGB Color Palette for Form Design.

This is a special episode focusing on streamlining the Form Module Code. For our RGB Color Wizard, we utilize an ActiveX ScrollBar Control. It's important to note that you cannot instantiate ActiveX controls such as Scrollbars, Sliders, TreeView, and ListView Controls in the standalone class module. Therefore, all event procedures for these controls must be written in the Form Module exclusively.

We employ three ScrollBar Controls dedicated to Red, Green, and Blue color code values. Each control spans from 0 to 255, representing the RGB color settings. Collectively, these values are utilized to generate a wide range of colors in the Color Wizard, logically providing the capability to create up to 16.7 million colors.

The RGB Color Wizard Image is given below.

Let us take a look at the User Interface of the Color Wizard.

Color Wizard

The Wizard Controls.

To the left of the scrollbars, three textboxes are positioned. When you adjust the scrollbar slider to the right or left, the selected color number will be displayed in the textbox on the left side. The color range spans from 0 to 255 for red, green, and blue colors. Additionally, you have the option to manually input color numbers for a specific RGB color by typing them into the text boxes.

To the right of the scroll bars, three label controls display the intensity of the selected color number as you move the scrollbar, resembling a graph chart for Red, Green, and Blue. The RGB() function blends all three selected colors together, resulting in the generation of the RGB color. This final RGB color is prominently displayed in the large rectangle control below.

Once you are ready to save the generated new Color, click on the RGB Color rectangle Control to select the Color.

Next, Click on one of the 25 Color Boxes to save the selected Color. The existing Color in the color box will be replaced with the new one. The RGB Color Number is displayed in the RGBColor TextBox Control below the Colorbox Grid.

You can store and preserve up to 25 colors at any given time. When you wish to apply one of the saved colors to a control, such as a TextBox or Label Control, for properties like ForeColor, BackColor, or BorderColor, simply highlight the number in the RGB Color TextBox and copy-paste the color number into the desired color attribute property.

To copy the RGB Number to the Clipboard, Click on the Label, with the Caption 'Copy to Clipboard' below the RGB Color TextBox and paste the RGB Color number where you want it.

The Form Module Code with the ActiveX Control's OnChange() Event Procedures is given below.

Option Compare Database
Option Explicit

Private CWiz As CWiz_ObjInit 'Intermediary Class

Const GraphFactor = (1 / 255) * 1440 'The Color Graph Width is 1 inch
Dim intR As Long, intG As Long, intB As Long
Dim cdb As Database, doc As Document

Private Sub Form_Load()

Set CWiz = New CWiz_ObjInit 'Instantiate CWiz_ObjInit Class
Set CWiz.o_Frm = Me 'Assign the Form object to its Property

End Sub

Private Sub form_Unload(Cancel As Integer)
    Set CWiz = Nothing
End Sub

'ActiveX Control
Private Sub Ctl_B_Change() 'Blue Color ScrollBar Control
Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("ColorPalette")

intB = Ctl_B.Value
With Me
    ![BN] = intB
    .B.Width = GraphFactor * intB
    .B.BackColor = RGB(0, 0, intB)
    .Color.BackColor = RGB(intR, intG, intB)
    .RGBColor = .Color.BackColor

'Save RGBColor and BN TextBoxes contents in Form Custom Properties
doc.Properties("RGBColor").Value = .Color.BackColor
doc.Properties("BN").Value = intB

    .Controls("Color").SpecialEffect = 0
    .CheckBox.Value = False
End With
End Sub

'ActiveX Control
Private Sub Ctl_G_Change()
Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("ColorPalette")

intG = Me.Ctl_G.Value

With Me
    intG = .Ctl_G.Value
    ![GN] = intG
    .G.Width = GraphFactor * intG
    .G.BackColor = RGB(0, intG, 0)
    .Color.BackColor = RGB(intR, intG, intB)
    .RGBColor = Color.BackColor

doc.Properties("RGBColor").Value = .Color.BackColor
doc.Properties("GN").Value = intG

    .Controls("Color").SpecialEffect = 0
    .CheckBox.Value = False
End With

End Sub

'ActiveX Control
Private Sub Ctl_R_Change()
Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("ColorPalette")

intR = Me.Ctl_R.Value
With Me
    ![RN] = intR
    .R.Width = GraphFactor * intR
    .R.BackColor = RGB(intR, 0, 0)
    .Color.BackColor = RGB(intR, intG, intB)
    .RGBColor = Color.BackColor

doc.Properties("RGBColor").Value = .Color.BackColor
doc.Properties("RN").Value = intR

    .Controls("Color").SpecialEffect = 0
    .CheckBox.Value = False
End With
End Sub

The ScrollBar Controls.

The three ScrollBars, named Ctl_R, Ctl_G, and Ctl_B, correspond to the Red, Green, and Blue colors. When the slider control on each is moved to adjust the color numbers within the range of 0 to 255, the Change Event is triggered. This event records these actions, updating other related controls, such as the TextBox content on the left side. Additionally, it increases the width of the label controls, dynamically displaying color variations based on the selected color number range.

In addition, the Change Event updates the RGB color displayed in the large rectangular Label Control and the RGB color number in the TextBox located below the Colors Grid.

Since we cannot create an instance of the ScrollBar ActiveX Control in the standalone Class Module we are forced to write the Change Event Subroutines in the Form's Class Module.

The ColorWizard and Run-Time Data.

Normally, we make changes to a control's ForeColor, BackColor, and Border Colors in the Design View of the Form, update the values in those Properties, and save them when the Form is closed. When we open the Form again it will display the changes as we made in the design view.

However, with this RGB Color Wizard, we are modifying control properties in the normal Form View Mode. Any alterations made in this mode are temporary and are not automatically saved, and they will be lost when the form is closed. While a logical solution might be to save all settings in a table, allowing them to be loaded the next time the form is opened.

Contrary to the conventional approach of using tables for everything, we're opting for a different method. In this case, we'll save the entire data within the form itself. While not a novel concept, this method is rarely employed due to its complexity. Specifically, we'll store the data in the form's custom-made properties, akin to the Tag property of a form or control. Creating these properties with VBA programs is possible, although the route taken for this process is somewhat uncommon.

 You can see how these Custom Properties are addressed for storing/retrieving data in them. For an introduction to this method visit this Link: Saving Data on Forms, Not in Table to see a simple practical use of this method.

To preserve data from the ColorGrid, Text Boxes, and other controls during changes or when closing, we implemented custom properties to store this information.

The saving of values to all custom properties occurs when the form is closed. Upon reopening the form, these values are read from the custom properties and displayed on the corresponding controls. These two event procedures are implemented in the CWiz_ObjInit Class Module.

Regarding the ScrollBar Change Event Subroutine, pay attention to the subsequent lines responsible for updating custom properties and the method employed to address and store values into them:

Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("ColorPalette")
.
.
.
doc.Properties("RGBColor").Value = .Color.BackColor
doc.Properties("BN").Value = intB
.

Before saving the values into the Custom Properties we must create the Properties on the Form.  This is a one-time exercise.

Sample Custom Property Management VBA Code.

Let us see an example of creating a Custom Property to save an Employee's Name in Form1. Sample VBA Code is given below:

'Create a Custom Property in Form1
Private Sub CreateProperty()
Dim db As Database
Dim doc As Document
Dim prp As Property

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Form1")

Set prp = doc.CreateProperty("EmpName", dbText, "SampleText")

doc.Properties.Append prp

Set prp = Nothing
Set doc = Nothing
Set db = Nothing

End Sub

'Assign a value to Custom Property in Form1
Private Sub AssignPropertyValue()

Dim db As Database
Dim doc As Document

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Form1")
    doc.Properties("EmpName").Value = "Michael Colins"

Set doc = Nothing
Set db = Nothing

End Sub

'Create a Custom Property in Form1
Private Sub ReadPropertyValue()

Dim db As Database
Dim doc As Document
Dim strName As String

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Form1")
  strName = doc.Properties("EmpName").Value
  MsgBox "Name: " & UCase(strName)

Set doc = Nothing
Set db = Nothing

End Sub

'Create a Custom Property in Form1
Private Sub DeleteProperty()

Dim db As Database
Dim doc As Document

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Form1")
    doc.Properties.Delete "EmpName"
    
Set doc = Nothing
Set db = Nothing
End Sub

All the procedures for creating a custom property to save an employee's name, assigning a name to the property, reading it back, displaying it in a message box, and deleting the custom property from Form1 are outlined in the individual subroutines above.

The CWiz_TextBox Wrapper Class.

The CWiz_TextBox Wrapper Class manages the AfterUpdate() Event, allowing for direct entry of color numbers for Red, Green, and Blue into the TextBoxes named RN, GN, and BN. The subsequent changes are seamlessly reflected in the ScrollBars, the color graphs situated to the right of the ScrollBars, the new color showcased in the large rectangle label background, and the RGB color number displayed in the TextBox.

You may save your new Color in the Color Grid as explained earlier. 

The CWiz_TextBox Wrapper Class Module Code is given below:

Option Compare Database
Option Explicit

Private WithEvents ctxt As Access.TextBox
Private cFrm As Form

Const GraphFactor = (1 / 255) * 1440
Private db As Database
Private doc As Document

Public Property Get c_Frm() As Form
    Set c_Frm = cFrm
End Property

Public Property Set c_Frm(ByRef vcFrm As Form)
    Set cFrm = vcFrm
End Property

Public Property Get c_txt() As Access.TextBox
    Set c_txt = ctxt
End Property

Public Property Set c_txt(ByRef vctxt As Access.TextBox)
    Set ctxt = vctxt
    
    Set db = CurrentDb
    Set doc = db.Containers("Forms").Documents("ColorPalette")
End Property

Private Sub ctxt_AfterUpdate()
 With cFrm
    Select Case ctxt.Name
        Case "RN"
            .Ctl_R.Value = cFrm![RN]
            doc.Properties("RN").Value = cFrm!RN
            .CheckBox.Value = False
        Case "GN"
            .Ctl_G.Value = cFrm![GN]
            doc.Properties("GN").Value = cFrm!GN
            .CheckBox.Value = False
        Case "BN"
            .Ctl_B.Value = cFrm![BN]
            doc.Properties("BN").Value = cFrm!BN
            .CheckBox.Value = False
    End Select
  End With
End Sub

The CWiz_Label Wrapper Class.

The Labels in the ColorGrid, the Color Graph Labels to the right side of the ScrollBars, and the RGB Color Display Label and all their Click Event Subroutines are kept in the CWiz_Label Wrapper Class. 

The CWiz_Label Wrapper Class Module Event Procedure Code is given below:

Option Compare Database
Option Explicit

Private WithEvents clbl As Access.Label
Private sFrm As Form

Const GraphFactor = (1 / 255) * 1440
Private db As Database
Private doc As Document

Private selflag As Boolean
Private lngColor As Long

Public Property Get s_Frm() As Form
    Set s_Frm = sFrm
End Property

Public Property Set s_Frm(ByRef vsFrm As Form)
    Set sFrm = vsFrm
End Property

Public Property Get s_clbl() As Access.Label
    Set s_clbl = clbl
End Property

Public Property Set s_clbl(ByRef vclbl As Access.Label)
    Set clbl = vclbl
    
    Set db = CurrentDb
    Set doc = db.Containers("Forms").Documents("ColorPalette")
End Property

Private Sub clbl_Click()
Dim I As Integer
If Val(Mid(clbl.Name, 5)) > 0 Then
    I = Val(Mid(clbl.Name, 5))
End If
Select Case I
    Case 1 To 25
        Call Boxes(I) 'Click on Color Grid
End Select

Select Case clbl.Name
    Case "Color"
        Call ColorClick 'Click on the RGB Color Display Label
    Case "Clip"
       Call ClipClick   'Click on this Labek to Copy RGB Color number to ClipBoard
End Select
End Sub

Private Sub ColorClick()
With sFrm
    lngColor = .Color.BackColor
    !RGBColor = .Controls("Color").BackColor
    .Controls("Color").SpecialEffect = 2
    
    'Copy the created color to the grid
    !CheckBox.Value = True
End With
End Sub

Private Sub ClipClick()
    If Not IsNull(sFrm![RGBColor]) Then
        ' Copy the TextBox contents to the clipboard
        sFrm.RGBColor.SetFocus
        DoCmd.RunCommand acCmdCopy
        MsgBox "RGB Color Number Copied to Clipboard!", vbInformation
    Else
        ' Display a message if the TextBox is empty
        MsgBox "RGBColor is empty!", vbExclamation
    End If

End Sub

Private Sub Boxes(ByVal bx As Integer)
Dim j As Integer
Dim ctl As String
Dim Colr As Long
Dim intR As Integer
Dim intG As Integer
Dim intB As Integer

selflag = sFrm!CheckBox.Value

For j = 1 To 25
If j = bx Then
   If selflag Then
    With sFrm
      ctl = "lblC" & j
        .Controls(ctl).SpecialEffect = 2
        .Controls(ctl).BackColor = .Color.BackColor
        doc.Properties("Selected").Value = .Controls(ctl).BackColor
        !RGBColor = .Controls(ctl).BackColor
        !CheckBox.Value = False
      ctl = "C" & j
      doc.Properties(ctl).Value = .Color.BackColor
      doc.Properties("Selctl").Value = "C" & j
    End With
   Else
    With sFrm
         ctl = "lblC" & j
            !RGBColor = .Controls(ctl).BackColor
            .Controls(ctl).SpecialEffect = 2
         doc.Properties("Selected").Value = .Controls(ctl).BackColor
         doc.Properties("Selctl").Value = "C" & j
   End With
   
 Colr = sFrm!RGBColor
 'Split into R,G,B
 intR = Colr Mod 256
 intG = Colr \ 256 Mod 256
 intB = Colr \ 256 \ 256 Mod 256
 
 With sFrm
    !RN = intR
    .Ctl_R.Value = sFrm!RN
    !GN = intG
    .Ctl_G.Value = sFrm!GN
    !BN = intB
    .Ctl_B.Value = sFrm!BN
 
    .R.Width = GraphFactor * intR
    .G.Width = GraphFactor * intG
    .B.Width = GraphFactor * intB

    .R.BackColor = RGB(intR, 0, 0)
    .G.BackColor = RGB(0, intG, 0)
    .B.BackColor = RGB(0, 0, intB)

    .Color.BackColor = RGB(intR, intG, intB)
  End With
  With doc
    .Properties("RGBColor").Value = sFrm.Color.BackColor
    .Properties("RN").Value = intR
    .Properties("GN").Value = intG
    .Properties("BN").Value = intB
  End With
  End If
   
Else
   ctl = "lblC" & j
   sFrm.Controls(ctl).SpecialEffect = 0
End If

Next

End Sub

The Intermediary Class Module CWiz_ObjInit VBA Code is given below:

Option Compare Database
Option Explicit

Private cw As CWiz_Label
Private txt As CWiz_TextBox
Private WithEvents cmd As CommandButton

Private WithEvents frm As Form
Private coll As New Collection

Const GraphFactor = (1 / 255) * 1440
Const MaxColor = 25
Private cdb As Database, ctr As Container, doc As Document


Public Property Get o_Frm() As Form
    Set o_Frm = frm
End Property

Public Property Set o_Frm(ByRef voFrm As Form)
    Set frm = voFrm
    
    Call Class_Init
End Property

Private Sub Class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"
Dim I As Integer

Call ColorPalette_Init 'Initialize

Set cmd = frm.cmdClose
    cmd.OnClick = EP
     
For Each ctl In frm.Controls

 I = Val(Mid(ctl.Name, 5))
 
  Select Case TypeName(ctl)
    Case "Label"
      Select Case I
          Case 1 To 25
            Set cw = New CWiz_Label
            Set cw.s_Frm = frm
            Set cw.s_clbl = ctl
                cw.s_clbl.OnClick = EP
            coll.Add cw
            Set cw = Nothing
      End Select
      Select Case ctl.Name
        Case "Color"
            Set cw = New CWiz_Label
            Set cw.s_Frm = frm
            Set cw.s_clbl = ctl
                cw.s_clbl.OnClick = EP
                
            coll.Add cw
            Set cw = Nothing
        Case "Clip"
            Set cw = New CWiz_Label
            Set cw.s_Frm = frm
            Set cw.s_clbl = ctl
                cw.s_clbl.OnClick = EP
            coll.Add cw
            Set cw = Nothing
        End Select
     
      Case "TextBox"
        Select Case ctl.Name
            Case "RN", "GN", "BN"
              Set txt = New CWiz_TextBox
              Set txt.c_Frm = frm
              Set txt.c_txt = ctl
                txt.c_txt.AfterUpdate = EP
                coll.Add txt
              Set txt = Nothing
            Case "RGBColor"
              Set txt = New CWiz_TextBox
              Set txt.c_Frm = frm
              Set txt.c_txt = ctl
                txt.c_txt.OnGotFocus = EP
                coll.Add txt
              Set txt = Nothing
        End Select
    End Select
Next
End Sub

Private Sub ColorPalette_Init()
Dim xRN As Integer
Dim xGN As Integer
Dim xBN As Integer
Dim xRGBColor As Long
Dim j As Integer
Dim cdb As Database
Dim ctr As Container
Dim doc As Document
Dim strctl As String

Set cdb = CurrentDb
Set ctr = cdb.Containers("Forms")
Set doc = ctr.Documents("ColorPalette")

xRN = doc.Properties("RN").Value
xGN = doc.Properties("GN").Value
xBN = doc.Properties("BN").Value
xRGBColor = doc.Properties("RGBColor").Value

With frm 
    ![RN] = xRN
    ![GN] = xGN
    ![BN] = xBN
    .R.Width = xRN * GraphFactor
    .R.BackColor = RGB(xRN, 0, 0)
    
    .G.Width = xGN * GraphFactor
    .G.BackColor = RGB(0, xGN, 0)
    
    .B.Width = xBN * GraphFactor
    .B.BackColor = RGB(0, 0, xBN)
    
.Ctl_R.Value = xRN
.Ctl_G.Value = xGN
.Ctl_B.Value = xBN

.Color.BackColor = RGB(xRN, xGN, xBN)
.RGBColor = .Color.BackColor
End With

For j = 1 To MaxColor
   strctl = "lblC" & j
   frm.Controls(strctl).BackColor = doc.Properties("C" & j).Value
   If ("C" & j) = doc.Properties("Selctl").Value Then
      frm.Controls(strctl).SpecialEffect = 2
   End If

Next j

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err.Description, , "Form_Load"
Resume Form_Load_Exit

End Sub

Private Sub cmd_Click()
Dim msg As String
Dim ctl As String, strC1 As String, j As Integer

msg = "Close the Color Wizard?"
If MsgBox(msg, vbYesNo + vbQuestion, "cmd_Click()") = vbYes Then

    Set cdb = CurrentDb
    Set ctr = cdb.Containers("Forms")
    Set doc = ctr.Documents("ColorPalette")

For j = 1 To MaxColor
  ctl = "lblC" & j
  strC1 = "C" & j
    doc.Properties(strC1).Value = frm.Controls(ctl).BackColor
  If frm.Controls(ctl).SpecialEffect = 2 Then
      doc.Properties("Selected").Value = frm.Controls(ctl).BackColor
      doc.Properties("SelCtl").Value = strC1
  End If
Next
    doc.Properties("RGBColor").Value = Nz(frm.Controls("RGBColor").Value, 0)
    DoCmd.Close acForm, frm.Name
End If
End Sub

Private Sub Class_Terminate()
Do While coll.Count > 1
    coll.Remove 1
Loop
End Sub

When the form is opened, the form object is passed to the intermediary class module, initiating the execution of the Class_Init subroutine. The first subroutine, ColorPalette_Init, is invoked from within the Class_Init subroutine. This procedure retrieves all the values saved in the form's custom properties and assigns them to the labels, scroll bars, and text boxes on the form.

This procedure is normally run in the Form_Load() Event Procedure and the current Values on the Form Controls are saved when the Form is Closed.

There is a single command button to close the form. A singular command button object instance is created in the Intermediary Class Module, and its Click Event is enabled. Consequently, when the cmdClose command button is clicked, the form close event procedure is executed in the CWiz_ObjInit Module. Before closing the form, all the values of the Color Wizard form controls are saved in the form's custom properties.

This topic was initially published in October 2010, featuring a color palette of 15 colors, and all the wizard VBA code was implemented in the Form Module. The older version of the wizard form is included in the demo database, labeled ColorPaletteOld. Feel free to open and review the code, assessing how it has been transformed into a form that can now be executed from the standalone class module, excluding the VBA code related to the ActiveX Control ScrollBar.

Demo Database Download Link.

Streamlining Form Module Code in Standalone Class Module.

  1. Re-using Form Module VBA Code for New Projects.
  2. Streamlining Form Module Code Part-Two.
  3. Streamlining Form Module Code Part-Three.
  4. Streamlining Form Module Code Part-Four.
  5. Streamlining Form Module Code Part-Five.
  6. Streamlining Form Module Code Part-Six.
  7. Streamlining Form Module Code Part-Seven.
  8. Streamlining Form Module Code Part-Eight.
  9. Streamlining Form Module Code Part-Nine.
  10. Streamlining Form Module Code Part-Ten.
  11. Streamlining Form Module Code Part-Eleven.
  12. Streamlining Report Module Code in Class Module.
  13. Streamlining Report Module Code in Class Module-2.
  14. Streamlining Form Module Code Part-14:All Controls
  15. Streamlining Custom Made Form Wizard-15
  16. Streamlining Custom Report Wizard-16
  17. Streamlining Form VBA External File Browser-17
  18. Streamlining Event Procedures of 3D TextWizard-18



Share:

Streamlining Event Procedures 3DTextWizard

 The 3D Heading Creation Wizard for Form and Report.

The contents of the 3D Text Wizard were originally published in a series of articles back in September 2006. I created this website to share some tips, many of which I had implemented in my projects while working in an Automotive Company in the Sultanate of Oman. These articles, featuring the QBColor version, aimed to provide insights and practical knowledge based on real-world experiences.

The 3D Text Wizard now offers a broader spectrum of colors in RGB format for users to choose from. To enhance customization, a ColorList table has been introduced, allowing users to add additional colors. This feature proves beneficial for creating three-dimensional text, such as Form headings or displaying data field values like employee names or product names, especially when viewing them from a distance.

Example-1: Employee Name.

Example-2:Order Details Form View-2

The 3D Text Wizard Image is given below:

The event procedures and functions within the 3D Text Wizard have been streamlined in adherence to the new Event  Procedure Coding Rules established in the Standalone Class  Module. This approach enhances code organization and readability while promoting efficient maintenance and development practices.

3D Text Creation Technique.

The 3D Text is crafted through layers of Label Controls or Text Boxes, each containing the same text. Alternatively, this effect can be manually achieved by strategically placing layers with an attractive color on top and additional layers with a darker forecolor behind, slightly shifted to one of the four corners—top-left, bottom-left, top-right, or bottom-right. This technique imparts a shadow effect, enhancing the visual appeal of the text.

Performing this task manually each time can be a time-consuming exercise. As you may have observed in earlier episodes, I created headings for my Forms using only two labels, achieving a 3D-like appearance effortlessly.

To the left of the Text Wizard user interface, you'll find a ListBox displaying a variety of colors. This ListBox is linked to a table named 'Colors,' providing the flexibility to add additional color codes as needed.

On the right side of the Colors List, there are two OptionGroup controls. The top one features two options. When the first radio button is selected, the color chosen from the ListBox will be displayed in the top rectangle control. This selected color will then be applied to the topmost label's caption or the font color of the TextBox used for the 3D Text.

If the second radio button is selected, the color chosen from the ListBox is applied to the border color of the first two Wizards, namely Border 2D and Border 3D, within the Options Group Control below. It's worth noting that other Text Style Wizard options exclusively utilize the ForeColor option.

The 3D Text Shadow Positions.

At the top right side, there is a ComboBox with four options (0-3) to specify the positions of the light and shadow for the 3D Text.

Shadow Positions:

0 - Left Top Corner.

1 - Left Bottom Corner.

2 - Right Top Corner

3 - Right Bottom Corner 

The first text style, 2D, creates a white border around the text and doesn't require additional settings. For both 2D and 3D text styles, the wizard achieves the effect by creating five or seven labels with the same text and different ForeColor, positioning them underneath the top label and slightly adjusting their placement towards the shadow position. In the case of the 2D border design, the other labels are moved towards the four corners of the top label.

3D Text Control Types.

The ComboBox positioned below the Shadow Style ComboBox offers two options and utilizes two types of controls to generate the 3D Text.

1 - Label 

2 - TextBox

The first option is good for creating Static Headings on Forms or Reports.

The second option is TextBox-based 3D Text. It is good for displaying data from Form or Report Field(s) by creating Expressions, like the Images given at the top of this Page.

After selecting the desired options, click on the 'Create 3D Text' Command Button to generate the 3D Text. The resulting 3D Text is displayed in the Detail Section of a new Form. Beneath the 3D Text, a label control provides instructions on how to modify the text, font, font size, and font styles, such as bold, italics, and underline, if necessary. Users can carefully select the top label or TextBox without shifting it from its original position to change the label caption or TextBox contents.

After making changes, select all the Labels/TextBoxes Controls together by clicking near the controls and dragging over all the labels. Copy and paste them to the desired location where you want them to appear.

To display the form field(s) data in the TextBox controls, write the expression, such as =[First Name] & " " & [Last Name], in the Control Source Property after selecting all the text boxes together for the 3D Text.

After generating a text style, you have the option to preserve it within the 3D Text Wizard. You can then import it into other projects, making it available for use with modifications.

With this streamlined event procedure coding in the standalone class module, only a single Wrapper class is needed for Command Buttons on the form.

There's only one ListBox control on the form, and its Click-Event can be captured in a subroutine within the intermediary Class Module.

Similarly, there are two Option Group Controls on the form—one with the Style of 3D Text options and the other managing Fore-Color and Back-Color parameter selection activities. The Back-Color option specifically applies to the first two text styles, for 2D and 3D Border Color Options. When either of these two options is selected, the Back-Color option will be in an enabled state; otherwise, it will be disabled.

Since both of these actions can be controlled from the TextStyle Selection Option Group Control, a separate wrapper class module is not required. In both the ListBox and Options Group Control, we declared the object Instances of the ListBox and Options Group Control in the Intermediary Class Module, qualified with the keyword WithEvents. The two ComboBoxes on the Form are used for setting the required 3D Text Shadow Options and the other ComboBox is for selecting the Label or Text Control option. There are no Event Procedures to run for these two Controls. 

The Form Module VBA Code.

Both the ListBox and the Option Group Control's Click Events are enabled in the Class_Init() subroutine, and corresponding subroutines are written in this module. First, the Form Module Code is listed below:

Option Compare Database
Option Explicit

Private W As TWiz_Obj_Init

Private Sub Form_Load()
Set W = New TWiz_Obj_Init
Set W.w_frm = Me

End Sub

Private Sub Form_Unload(Cancel As Integer)
Set W = Nothing
End Sub

The TWiz_Obj_Init class is declared in the global area of the module with the object name 'W.' In the Form_Load() event procedure, the object is instantiated, and the current form object (Me) is passed to the W.w_frm() Property Procedure. When the form is closed, the class object 'W' is released from memory.

The TWiz_Obj_Init Class Module Code is Listed Below.

Option Compare Database

Private wcmd As TWiz_CmdButton

Private WithEvents lst As Access.ListBox
Private WithEvents opt As Access.OptionGroup

Private wfrm As Access.Form
Private Coll As New Collection

Public Property Get w_frm() As Form
  Set w_frm = wfrm
End Property

Public Property Set w_frm(ByRef vfrm As Form)
Set wfrm = vfrm
DoCmd.Restore
 
Call Class_Init
End Property

Private Sub Class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

Set opt = wfrm.TxtStyle '3D Text Styles
    opt.OnClick = EP

Set lst = wfrm.ColorList 'List of Colors
    lst.OnClick = EP

For Each ctl In wfrm.Controls
    Select Case TypeName(ctl)
        Case "CommandButton"
          Select Case ctl.Name
            Case "cmd3D", "cmdClose"
              Set wcmd = New TWiz_CmdButton
              Set wcmd.c_Frm = wfrm
            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 lst_Click()
Dim cl As Long
cl = lst.Value
Select Case lst.Name
    Case "ColorList"
    If wfrm.FBack = 1 Then
        wfrm.Fore.BackColor = cl
        wfrm.CFore = cl
    Else
        wfrm.Back.BackColor = cl
        wfrm.CBack = cl
    End If

End Select
End Sub

Private Sub opt_Click()
Dim opval As Integer

Select Case opt.Name
    Case "TxtStyle"
        opval = opt.Value
        With wfrm.cboStyle
            If opval > 1 Then
                .Enabled = True
            Else
                .Enabled = False
            End If
        End With
        With wfrm.Opt2
            Select Case opval
                Case 1, 2
                    .Enabled = True
                Case Else
                    .Enabled = False
            End Select
        End With
End Select
End Sub

Private Sub Class_Terminate()
Do While Coll.Count > 0
    Coll.Remove 1
Loop
End Sub

The following ListBox and OptionGroup Controls declarations are placed in the Global area of the Class Module.

 
Private WithEvents lst As Access.ListBox
Private WithEvents opt As Access.OptionGroup

The following statements in the Class_Init() Subroutine assign the References from these Objects in the Form and enable their Click Events, by assigning the "[Event Procedure]" text in their Event Properties:

Set opt = wfrm.TxtStyle '3D Text Styles
    opt.OnClick = EP

Set lst = wfrm.ColorList 'List of Colors
    lst.OnClick = EP

Both these objects Sub lst_Click() and Sub opt_Click() Event Subroutines are written below the Sub Class_Int() Procedure.

The Command Button Wrapper Class Module.

There is only one Wrapper Class for both the Command Buttons on the Form. All the Wizard Functions are called from the Command Button with the Caption 'Create 3D Text' Click Event Procedure, depending on the 3D Text Style Option selected.

The Command Button Wrapper Class Subroutine that Calls the Wizard Functions is listed below for reference.

Option Compare Database
Option Explicit

Private WithEvents cmd As Access.CommandButton
Private cfrm As Access.Form

Public Property Get c_Frm() As Form
   Set c_Frm = cfrm
End Property

Public Property Set c_Frm(ByRef vfrm As Form)
   Set cfrm = vfrm
End Property

Public Property Get c_cmd() As CommandButton
   Set c_cmd = cmd
End Property

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

Private Sub cmd_Click()
Select Case cmd.Name
    Case "cmd3D"
        Call Create3D(cfrm) 'Call the 3D Text Wizard
        
    Case "cmdClose"
If MsgBox("Close the 3DTextWizard? ", vbYesNo + vbQuestion, "cmdClose_Click()") = vbYes Then
    DoCmd.Close acForm, cfrm.Name
End If
End Select

End Sub

The Cmd3D Click Event Subroutine invokes the Create3D(cfrm) Subroutine and passes the Form Object as a Parameter. This Subroutine in the Standard Module gathers the 3D Text Wizard option settings from the Form into related variables and then calls the wizard function based on the selected text style. Each Wizard function, such as Border2D, and others, calls three different programs to create the 3D Text.

For example, the Border2D Wizard calls the following three Functions to complete the full task of creating the 3D Text:

  1. FormTxtLabels() ' Creates a Form and the Label or Text Controls
  2. Validate_Dup() ' Performs a Validation check.

  3. MsgLabel()  'Creates a Label control with instructions to use the 3D Text.

Listing all the Wizard VBA codes here is not feasible due to its large volume. However, the 3DTextWizard Demo Database is attached with all the code. You can download it from the link provided at the end of this Page. All the Wizard VBA codes are available in the TxtWizard Standard Module.

Visit the following Links of Articles published earlier for more details on the Wizard Functions:

  1. Create 3D Headings on Form
  2. Border 2D Heading Text
  3. Border3D Heading Style
  4. Shadow3D Heading Style

Demo Database Download Link.

Streamlining Form Module Code in Standalone Class Module.

  1. Re-using Form Module VBA Code for New Projects.
  2. Streamlining Form Module Code Part-Two.
  3. Streamlining Form Module Code Part-Three.
  4. Streamlining Form Module Code Part-Four.
  5. Streamlining Form Module Code Part-Five.
  6. Streamlining Form Module Code Part-Six.
  7. Streamlining Form Module Code Part-Seven.
  8. Streamlining Form Module Code Part-Eight.
  9. Streamlining Form Module Code Part-Nine.
  10. Streamlining Form Module Code Part-Ten.
  11. Streamlining Form Module Code Part-Eleven.
  12. Streamlining Report Module Code in Class Module.
  13. Streamlining Report Module Code in Class Module-2.
  14. Streamlining Form Module Code Part-14:All Controls
  15. Streamlining Custom Made Form Wizard-15
  16. Streamlining Custom Report Wizard-16
  17. Streamlining Form VBA External File Browser-17
  18. Streamlining Event Procedures of 3D TextWizard-18
Share:

Streamlining Form VBA External Files List HyperLinks

 External Files List in Hyperlinks in Form.

The Office.FileDialog Control 

The FileDialog Control displays files from the selected folder as hyperlinks in the form. Clicking on a hyperlink will open the file in its native application, if available.

The File Dialog control features user-defined filters that allow users to display specific categories of files, such as Word Documents, Excel Worksheet files, and Access Databases, or view all files within a folder. Select the required files and click on the 'Create File Link' Command Button to add the selected files to a table and display them in the form in Datasheet View as hyperlinks. The full file pathnames are shown in the second column for reference.

Files' List Display Image.

File Browser

After setting the File Filter in the TextBox with the name Pathname, Click on the Create File Links Command Button to open the following File Browser control to display the Files and Folders. 

File List View

At this point, you can select any folder to search for files if needed. To select several adjoining files, click on the first file, hold the Shift key, click on the last file, and then click the 'Open' command button. The selected files will appear in the list, as shown in the first image.

The Form Module VBA Code.

Option Compare Database
Option Explicit

Private FD As New FLst_Object_Init

Private Sub Form_Load()
    Set FD.fl_Frm = Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set FD = Nothing
End Sub

In the Global Declaration area, the Class_Init Class Module with the name FLst_Object_Init Object is declared with the name FD. An Instance of the Object is created in memory using the keyword New in the declaration.

In the Form_Load() Event Procedure the current Form Object is passed to the FD.fl_Frm Property to the FD Object Instance.

The FLst_Object_Init Class Module Code.

The FLst_Object_Init with the Class_Init() Subroutine VBA Code is given below:

Option Compare Database
Option Explicit

Private cmd As FLst_CmdButton
Private frm As Access.Form
Private Coll As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Disk Directory Listing in Hyperlinks
'Author: a.p.r. pillai
'Date  : 25/10/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

Public Property Get fl_Frm() As Access.Form
    Set fl_Frm = frm.m_cFrm
End Property

Public Property Set fl_Frm(ByRef pNewValue As Access.Form)
    Set frm = pNewValue
    
    Call Class_Init
End Property

Private Sub Class_Init()
Dim ctl As Control
Dim listcount As Long
Const EP = "[Event Procedure]"

'=============================
'Calling the Public Function ButtonStatus() From FLst_CmdButton Class
'from the Flst_CmdButton Class directly,
Set cmd = New FLst_CmdButton 'Create a separate instance
Set cmd.cmd_Frm = frm 'Pass the Form Object to the Property

Call cmd.ButtonStatus 'Call the Public Function, with Param, if any
Set cmd = Nothing 'Remove the instance
'=============================

For Each ctl In frm.Controls
Select Case TypeName(ctl)
      Case "CommandButton"
        Select Case ctl.Name
            Case "cmdHelp", "cmdFileDialog", _
            "cmdDelLink", "cmdDelFile", _
            "cmdClose", "cmdDelAll"
            
                Set cmd = New FLst_CmdButton
                Set cmd.cmd_Frm = frm
                Set cmd.c_cmd = ctl
        
                    cmd.c_cmd.OnClick = EP
                Coll.Add cmd
                Set cmd = Nothing
        End Select
End Select
Next

End Sub

Private Sub Class_Terminate()
Do While Coll.Count > 0
    Coll.Remove 1
Loop
End Sub

The following two Subroutines, if present in the Class Module, run automatically.  

  1. Class_Initialize()

  2. Class_Terminate()

Assume that we have both the above Subroutines in ClassA.

You create an Instance of ClassA in ClassB, like Dim A As ClassA, Set A = New ClassA. The Class_Initialize() Subroutine in ClassA runs, if present. You can put any initializing steps of Code within this Subroutine.

When you execute the Statement: Set A = Nothing or ClassB object Unloads the Class_Terminate() Subroutine in ClassA executes.  You can do the clean-up work, like Set Obj = Nothing, in this Subroutine to clear memory for other use.

This is useful when other objects like Collection Object, Dictionary Object, or other Class Module declarations are present in the Class Module. The following Class1 Module is Instantiated in the Form Module.

'Class1
Dim DT As ClsDateTime

Private Sub Class_Initialize()
  Set DT = New ClsDateTime
  Forms("Form2").Text2 = DT.DateTime
End Sub

Private Sub Class_Terminate()
 Set DT = Nothing

End Sub

The Class_Initialize() Subroutine, if present in the class module, runs immediately upon instantiating the Class Object. However, in our streamlined VBA coding, we are unable to utilize this feature. This limitation arises because we need to obtain the Form Object in the Class Module before executing the Class_Init() Subroutine. Consequently, we cannot use the Class_Initialize Subroutine. Instead, we call the Class_Init() Subroutine immediately after acquiring the Form Object in the Set Property Procedure of the Form Object.

That doesn't mean that we cannot use it at all. We can use it to instantiate the Collection Object or any other Objects used in this Class Module, like the following example:

Private Sub Class_Initialize()
	Set Coll = New Collection
End Sub

The Collection Object declaration is made in the Global declaration area of the Class Module. Since we used the New keyword in the Declaration Statement these extra lines of Code are not required within the Class_Initialize() Subroutine.

The Class_Terminate() Subroutine is useful to clean up memory and works like the Form_Unload() Event Procedure.

Private Sub Class_Terminate()
Do While Coll.Count > 0
    Coll.Remove 1
Loop
End Sub

The above Code clears the Collection Object contents when the FLst_Object_Init Class Module Unloads from memory.

In this Project, we need only a single Wrapper Class Module for the Command Buttons on the Form, besides the Class Module FLst_Object_Init with the Class_Init() Subroutine.

The Command Button Class FLst_CmdButton has several subroutines and all of them are called individually from under each command Button Name from the Click Event Subroutine for clarity, rather than writing the entire Code directly under the Command Button name.

The FLst_CmdButton Class Module Code.

'The Click Event Subroutines
Private Sub cmd_Click()
Select Case cmd.Name
  Case "cmdClose"
    If MsgBox("Close this Form?", vbOKCancel + vbQuestion, "cmd_Click") = vbOK Then
        DoCmd.Close acForm, cmdfrm.Name
        Exit Sub
    End If

    Case "cmdFileDialog"
        Call cmdFileDialog 'Display selected Path & files
        
    Case "cmdDelLink"
        Call cmdDelLink 'Delete Selected Link from list
    
    Case "cmdDelAll"
        Call cmdDelAll 'Delete All Links from list
    
    Case "cmdDelFile"
        Call cmdDelFile 'Delete Link and File from Disk
        
    Case "cmdHelp"
        DoCmd.OpenForm "Help", acNormal 'Show help Form
End Select
End Sub

The cmdFileDialog() Subroutine.

This Subroutine is run by clicking on the Command Button with the Caption Create File Links.

Private Sub cmdFileDialog()
On Error GoTo cmdFileDialog_Click_Err

'Requires reference to Microsoft Office 12.0 Object Library.
Dim fDialog As office.FileDialog
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim defPath As String
Dim varFile As Variant
Dim strfiles As String

   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections of disk files.
      .AllowMultiSelect = True
      .InitialFileName = Dir(strPath)
      .InitialView = msoFileDialogViewDetails
      'Set the title of the dialog box.
      .Title = "Please select one or more files"

      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Access Databases", "*.mdb; *.accdb"
      .Filters.Add "Excel WorkBooks", "*.xlsx; *.xlsm; *.xls; *.csv"
      .Filters.Add "Word Documents", "*.docx; *.doc"
      .Filters.Add "Access Projects", "*.adp"
      .Filters.Add "All Files", "*.*"
      .FilterIndex = 1
      '.Execute
      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
    If .Show = True Then
        Set db = CurrentDb
        Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
        'Add all selected files to the DirectoryList Table
        defPath = ""
      For Each varFile In .SelectedItems
         If defPath = "" Then
            defPath = Left(varFile, InStrRev(varFile, "\"))
            defPath = defPath & "*.*"
            cmdfrm.PathName = defPath
            cmdfrm.PathName.Requery
            strPath = defPath
         End If
            rst.AddNew
            'Create Hyperlink in 4 segments
            '1st segment: only the File Name
            strfiles = Mid(varFile, InStrRev(varFile, "\") + 1)
            '2nd segment:Full File PathName,3rd Empty,4th TipText
            strfiles = strfiles & "#" & varFile & "##Click"
            rst![FileLinks] = strfiles
            rst![Path] = varFile
            rst.Update
    Next
        
    Call ButtonStatus

        Else
            MsgBox "You clicked Cancel in the file dialog box."
        End If
      
   End With

cmdFileDialog_Click_Exit:
Exit Sub

cmdFileDialog_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdFileDialog_Click()"
Resume cmdFileDialog_Click_Exit
End Sub

The statement 'Set fDialog = Application.FileDialog(msoFileDialogFilePicker)' opens the File Browser Dialog Control and initializes its various properties. Within this control, we can define file type filters, allowing users to select specific file types from the filter display when the FileDialog control is open and displaying files from the default path setting. If there are any uncertainties about the file selection procedure, users can click on the Help Command Button located on the top right side of the form. This button provides detailed information about the functions of each command button on the form, explaining what they do and how to select files in different ways.

There is a table named DirectoryList designed to store the selected files in hyperlink format in the table's first column. The second column displays the full path of the files for reference. Clicking on the hyperlink will open the file in its native application, such as MS Word or Excel.

The Call ButtonStatus() statement invokes the ButtonStatus() subroutine, which checks whether the DirectoryList table is empty or not. If the table is empty, all command buttons except the 'Create File Links' and 'Help' buttons are disabled. This subroutine is called from within other subroutines and from the FLst_Object_Init class module. Please refer to the VBA code highlighted in red inside the Class_Init() subroutine above.

If you create a function within a stand-alone class module with public scope, it becomes accessible across other class modules or standard modules within the program. This allows you to call and utilize this function from outside the class module. 

We will do some trial runs to learn how to call a Function from another Class Module, from the Standard Module, and from the Form Module after this Subroutines review. 

The cmdDelLink Subroutine.

Select a single Record by clicking on the Record Selector Button, to delete it from the Hyperlink List, and click on the Delete Link Command Button. Before deleting the record a message is displayed to reconfirm the action or to Cancel it.

'Delete the Link From the List
Private Sub cmdDelLink()
On Error GoTo cmdDelLink_Click_Err
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFile As String
Dim msg As String

'Read the current record Pathname
strFile = cmdfrm.DirectoryList.Form!Path
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
rst.FindFirst "Path = '" & strFile & "'"
If Not rst.NoMatch Then
    msg = UCase("Link: " & strFile & vbCr & "DELETE from above List?")
    
If MsgBox(msg, vbQuestion + vbYesNo, "cmddelLink_Click()") = vbYes Then
    rst.Delete
    rst.Requery
    cmdfrm.DirectoryList.Form.Requery
    MsgBox UCase("File Link: " & strFile & " Deleted.")
End If
Else
    MsgBox UCase("Link: " & strFile & " Not Found!!")
End If

Call ButtonStatus

rst.Close
Set rst = Nothing
Set db = Nothing

cmdDelLink_Click_Exit:
Exit Sub

cmdDelLink_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelLink_Click()"
Resume cmdDelLink_Click_Exit
End Sub

The cmdDelAll() Subroutine.

This Subroutine Deletes all the Records from the DirectoryList Table. All three Command Buttons with Delete actions are disabled and remain disabled till at least one file is added to the Hyperlink List.

Private Sub cmdDelAll()
Dim msg As String
Dim yn As Integer
Dim listcount As Long

On Error GoTo cmdDelAll_Click_Err
listcount = DCount("*", "DirectoryList")
If listcount = 0 Then
    cmdfrm.cmdDelAll.Enabled = False
    Exit Sub
Else
    cmdfrm.cmdDelAll.Enabled = True
End If

msg = "All File Links in the List will be Deleted!"
msg = msg & vbCr & "Are You sure?"
If MsgBox(msg, vbYesNo + vbCritical, "cmdDelAll()") = vbYes Then
    If MsgBox("Deleting All File Links?", vbOKCancel + vbInformation, "cmdDelAll()") = vbOK Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "DeleteAll_LinksQ", acViewNormal
        DoCmd.SetWarnings True
        cmdfrm.DirectoryList.Form.Requery
        cmdfrm.cmdDelAll.Enabled = False
    End If
End If

Call ButtonStatus

cmdDelAll_Click_Exit:
Exit Sub

cmdDelAll_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelAll_Click()"
Resume cmdDelAll_Click_Exit
End Sub

The cmdDelFile() Subroutine.

Caution:

Be careful before using this Command Button. This Button Click will delete the File from the Disk and the Link from the list. Use this Command Button only to delete the file from the Disk.

'Caution: Deletes the File from Disk
'1. Delete the File from Disk
'2. Remove selected link from List
Private Sub cmdDelFile()
On Error GoTo cmdDelFile_Click_Err
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFile As String
Dim msg As String

'Read selected Record Pathinfo
strFile = cmdfrm.DirectoryList.Form!Path
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
rst.FindFirst "Path = '" & strFile & "'"
If Not rst.NoMatch Then
    msg = UCase("File: " & strFile & vbCr & "DELETE from Disk?")
If MsgBox(msg, vbQuestion + vbYesNo, "cmdDelFile_Click") = vbYes Then
    
   If MsgBox(UCase("Are you sure you want to Delete") & vbCr _
   & UCase(rst!Path & " File from DISK?"), vbCritical + vbYesNo, "cmdDelFile_Click()") = vbNo Then
    GoTo cmdDelFile_Click_Exit
   End If
   'Delete record entry from Table DirectoryList
    rst.Delete
    rst.Requery
    
Call ButtonStatus

    'Delete file from Disk
    If Len(Dir(strFile)) > 0 Then
        Kill strFile
        MsgBox "File: " & strFile & " Deleted."
    Else
        MsgBox "File: " & strFile & vbCr & "Not Found on Disk!"
    End If
  End If
Else
    MsgBox "File: " & strFile & " Not Found!!"
End If

cmdDelFile_Click_Exit:
    rst.Close
    Set rst = Nothing
    Set db = Nothing
Exit Sub

cmdDelFile_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelFile_Click()"
Resume cmdDelFile_Click_Exit
End Sub

The ButtonStatus()

All three Delete Subroutines in the Flst_CmdButton and also from the FLst_Object_Init Class call this Public Subroutine ButtonStatus() to Disable the Command Buttons if the DirectoryList Table is Empty.

Public Sub ButtonStatus()
Dim listcount As Long

On Error GoTo ButtonsStatus_Err:

listcount = DCount("*", "DirectoryList")
cmdfrm.DirectoryList.Form.Requery

If listcount = 0 Then
    cmdfrm.cmdDelLink.Enabled = False
    cmdfrm.cmdDelAll.Enabled = False
    cmdfrm.cmdDelFile.Enabled = False
Else
    cmdfrm.cmdDelLink.Enabled = True
    cmdfrm.cmdDelAll.Enabled = True
    cmdfrm.cmdDelFile.Enabled = True
End If

ButtonsStatus_Exit:
Exit Sub

ButtonsStatus_Err:
MsgBox Err & " : " & Err.Description, , "ButtonsStatus()"
Resume ButtonsStatus_Exit
End Sub

Calling Public Function from Class Module.

  1. Create a Class Module with the name ClsDateTime.

  2. Copy and Paste the following Function Code into the Class Module:

    Option Compare Database
    Option Explicit
    
    Public Function DateTime() As String
    Dim fmt As String
    
    fmt = "dd/mm/yyyy hh:nn:ss"
    DateTime = "DateTime: " & Format(Now(), fmt)
    
    End Function
    
     
  3. Save the Class Module.

  4. Create a New Form with the name Form1, or any other name you prefer and open it in Design View.

  5. Add a TextBox Control on the Form and make sure the TextBox Name is Text0.

  6. Display the Property Sheet of the Form and select the Other Tab in the Property Sheet.

  7. Set the Has Module Property value to Yes to add a Class Module to the Form.
  8. Display the Code Module of Form1, Copy and Paste the following Code in the Form Module, Save and Close the Form:

    Private Sub Form_Load()
    Dim DT As New ClsDateTime
    
    Me.Text0 = DT.DateTime
    
    End Sub
    
  9. Open Form1 in Normal View. The current Date and Time will appear in the TextBox.

In the Form_Load() Event Procedure creates an Instance of the ClsDateTime Class Module with the object name DT. The next statement while entering DT. the DateTime() Function name will appear and all we have to do is to add the Function. When the Form is open the Date and Time information is displayed in the TextBox.

The same procedure can be repeated between two Class Modules to call the Function in another Class Module, instead of the Form Module.

We use three levels of Class Modules for our Streamlined Structured VBA Coding examples. Let us try this Function in a similar setup i.e. the Form Module, the Intermediary Class Module, and the Class Module with our DateTime() Function.

  1. Make a Copy of Form1 and name it as Form2.

  2. Rename the TextBox Name to Text2.

  3. Display its Class Module, then Copy and Paste the following two Lines of Code, overwriting the existing lines.

    Option Compare Database
    
    Private test As New Class1
    
    
  4. Create a Class Module with the Name Class1.

  5. Copy and Pase the Following Code into the Class1 Module:

    Option Compare Database
    
    Private D  As New ClsDateTime
    
    Private Sub Class_initialize()
      Forms("Form2").Text2 = D.DateTime
    End Sub
    
  6. Select Save from the File Menu to save all the Files.

  7. Open Form2 in Normal View. The DateTime value should appear in Text2 TextBox on the Form.

Since Class Modules cannot load themselves in Memory we used Form2 Module to create an Instance of Class1 Module into memory. When Class1 Class Module is loaded into memory it creates an Instance of the ClsDateTime Class Module and executes the Class_Initialize() Subroutine. From within this Subroutine Calls the DateTime() Public Function. The result received is saved in the TextBox in Form2.

Hope you understand now how it works.

Try Calling the DateTime() Function from the Standard Module from a Test() Function.

Demo Database Download Link.


Streamlining Form Module Code in Standalone Class Module.

  1. Re-using Form Module VBA Code for New Projects.
  2. Streamlining Form Module Code Part-Two.
  3. Streamlining Form Module Code Part-Three.
  4. Streamlining Form Module Code Part-Four.
  5. Streamlining Form Module Code Part-Five.
  6. Streamlining Form Module Code Part-Six.
  7. Streamlining Form Module Code Part-Seven.
  8. Streamlining Form Module Code Part-Eight.
  9. Streamlining Form Module Code Part-Nine.
  10. Streamlining Form Module Code Part-Ten.
  11. Streamlining Form Module Code Part-Eleven.
  12. Streamlining Report Module Code in Class Module.
  13. Streamlining Report Module Code in Class Module-2.
  14. Streamlining Form Module Code Part-14:All Controls
  15. Streamlining Custom Made Form Wizard-15
  16. Streamlining Custom Report Wizard-16
  17. Streamlining Form VBA External File Browser-17
  18. Streamlining Event Procedures of 3D TextWizard-18

Share:

Streamlining Form VBA Custom Report Wizard - 16

 Streamlining Custom Made Reports Wizard Form Module VBA Code.

Hope you like the Custom Form Wizard of last week, which organized its VBA Code in standalone Class Modules.  You can reach the Code and review and study them without interfering with the Form Design and Form Module.

The custom-made Form Wizard and the Report Wizard Forms have no difference in their User Interface Design. The Report Wizard was also published earlier, way back in December 2008 under Access 2003. Now, the Report Wizard Form Module VBA Codes run from the standalone Class Module to create the Reports.

The Report Wizard is designed using a TabControl with two Pages. The first TabPage displays the Wizard Type Options in a ListBox and the Table/Query list in a ComboBox Control. 

1. Report in Column Format.

2. Report in Tabular Format.

The above two Options are inserted as Value List in the RowSource Property of the ListBox. The Default Value Property is set with the expression: = WizList.Column(0,0) to select the first item by default.

The ComboBox Control displays the list of Tables and Select-Queries filtered from the System Table MSysObjects. The ComboBox's Default Value Property is also set with the expression: =FilesList.Column(0,0) to select the first file as Default.

The SQL of the File Selection Query.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) AND ((Left([Name],4))<>'WizQ') AND ((Left([Name],1))<>'~') AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Type, MSysObjects.Name;

The TabControl first page image is given below:

Report Wizard Page1

Report Wizard Page2 Image:

Report Wizard Page2

The following lines of the VBA Code are only needed in the Form's Class Module. All other Events Subroutines and Functions are placed in the Standalone Class Modules.

Option Compare Database
Option Explicit

Private obj As New RWizObject_Init

Private Sub Form_Load()
    Set obj.fm_fom = Me
End Sub

The RWizObject_Init Intermediary Class Module is Instantiated with the Object Name obj in the global declaration area of the Form Module. In the Form_Load() Event Procedure the Form Object reference is passed to the RWizObject_Init Class Module Property Procedure through the statement Set obj.fm_fom = Me.

The RWizObject_Init Class.

The RWizObject_Init VBA Code is listed below. All the Report creation functions are placed within this Class Module.

Option Compare Database
Option Explicit

Private fom As Access.Form

Private cmdb As RWiz_CmdButton
Private lstb As RWiz_ListBox
Private comb As RWiz_Combo

Private tb As RWiz_TabCtl
Private Coll As New Collection

'Wizard Functions Running Command Button Instance'
'Functions are placed in this Module
Private WithEvents cmdFinish As Access.CommandButton
Dim DarkBlue As Long, twips As Long, xtyp As Integer, strFile As String

Public Property Get fm_fom() As Form
  Set fm_fom = fom
End Property

Public Property Set fm_fom(ByRef mfom As Form)
  Set fom = mfom
    
  Call Class_Init
End Property

Private Sub Class_Init()
Dim Ctl As Control
Const EP = "[Event Procedure]"

'Filter Table/Select Query Names for ComboBox
Call Create_FilesList

For Each Ctl In fom.Controls
    Select Case Ctl.ControlType
        Case acTabCtl
            Set tb = New RWiz_TabCtl
            Set tb.Tb_Frm = fom
            Set tb.Tb_Tab = Ctl
              tb.Tb_Tab.OnChange = EP
              
              Coll.Add tb
            Set tb = Nothing
        
        Case acCommandButton
            Select Case Ctl.Name
                Case "cmdReport"
                    'Not to add in the Collection object
                    'The Click Event Runs the Wizard Functions
                    'from this Class Module, not from the
                    'Wrapper Class - FWiz_CmdButton
                    
                    Set cmdFinish = fom.cmdReport
                    cmdFinish.OnClick = EP
                Case Else
            
            Set cmdb = New RWiz_CmdButton
            Set cmdb.w_Frm = fom
            Set cmdb.w_cmd = Ctl
                cmdb.w_cmd.OnClick = EP
                
              Coll.Add cmdb
            Set cmdb = Nothing
         End Select
            
        Case acComboBox
            Set comb = New RWiz_Combo
            Set comb.cbo_Frm = fom
            Set comb.c_cbo = Ctl
                comb.c_cbo.OnGotFocus = EP
                comb.c_cbo.OnLostFocus = EP
            
        Case acListBox
            Set lstb = New RWiz_ListBox
            Set lstb.lst_Frm = fom
            Set lstb.m_lst = Ctl
                lstb.m_lst.OnGotFocus = EP
                lstb.m_lst.OnLostFocus = EP
                
                Coll.Add lstb
            Set lstb = Nothing
    End Select
Next
            
End Sub

Private Sub cmdFinish_Click()
        xtyp = fom!WizList
        strFile = fom!FilesList
        If xtyp = 1 Then
            Columns strFile
        Else
            Tabular strFile
        End If
          DoCmd.Close acForm, fom.Name
End Sub

Create_FilesList() Subroutine Code.

The Subroutine that creates the Files List for the ComboBox on the first page of the Wizard.

'Create Tables/Queries List for
Private Sub Create_FilesList()
Dim strSQL1 As String
Dim cdb As DAO.Database
Dim Qry As DAO.QueryDef
Dim FList As ComboBox

On Error GoTo Create_FilesList_Err
DoCmd.Restore

strSQL1 = "SELECT MSysObjects.Name " _
& "FROM MSysObjects " _
& "WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) " _
& "AND ((Left([Name],4))<>'WizQ') AND ((Left([Name],1))<>'~') " _
& "AND ((MSysObjects.Flags)=0)) " _
& "ORDER BY MSysObjects.Type, MSysObjects.Name;"

DarkBlue = 8388608
twips = 1440

Set cdb = CurrentDb
Set Qry = cdb.QueryDefs("WizQuery")
If Err = 3265 Then
  Set Qry = cdb.CreateQueryDef("WizQuery")
  Qry.SQL = strSQL1
  cdb.QueryDefs.Append Qry
  cdb.QueryDefs.Refresh
  Err.Clear
End If

With Forms("ReportWizard")
Set FList = .FilesList
    .FilesList.RowSource = "WizQuery"
    .FilesList.Requery
End With

Create_FilesList_Exit:
Exit Sub

Create_FilesList_Err:
MsgBox Err & ": " & Err.Description, , "Create_FilesList()"
Resume Create_FilesList_Exit
End Sub

The Function that Creates the Report in Column Format.

Public Function Columns(ByVal DataSource As String)

Dim cdb As Database
Dim FldList() As String
Dim Ctrl As Control
Dim Rpt As Report
Dim PgSection As Section
Dim DetSection As Section
Dim HdSection As Section

Dim lngTxtLeft As Long
Dim lngTxtTop As Long
Dim lngTxtHeight As Long
Dim lngtxtwidth As Long

Dim lngLblLeft As Long
Dim lngLblTop As Long
Dim lngLblHeight As Long
Dim lngLblWidth As Long

Dim FldCheck As Boolean
Dim strTblQry As String
Dim intflds As Integer
Dim lstcount As Long
Dim RptFields As ListBox
Dim j As Integer


'Create Report with Selected Fields

On Error Resume Next

strFile = DataSource
Set RptFields = fom.SelList
lstcount = RptFields.listcount

If lstcount = 0 Then
   MsgBox "Fields Not Selected for Report!"
   Exit Function
Else
   lstcount = lstcount - 1
End If

ReDim FldList(0 To lstcount) As String

Set cdb = CurrentDb
Set Rpt = CreateReport

Set HdSection = Rpt.Section(acPageHeader)
    HdSection.Height = 0.6667 * twips

Set DetSection = Rpt.Section(acDetail)
    DetSection.Height = 0.166 * twips

For j = 0 To lstcount
  FldList(j) = RptFields.ItemData(j)
Next

With Rpt
    .Caption = strFile
    .RecordSource = strFile
    lngtxtwidth = 1.5 * twips
    lngTxtLeft = 1.1 * twips
    lngTxtTop = 0.0417 * twips
    lngTxtHeight = 0.2181 * twips

    lngLblWidth = lngtxtwidth
    lngLblLeft = 0.073 * twips
    lngLblTop = 0.0417 * twips
    lngLblHeight = 0.2181 * twips
End With

For j = 0 To lstcount

   Set Ctrl = CreateReportControl(Rpt.Name, acTextBox, acDetail, , FldList(j), lngTxtLeft, lngTxtTop, lngtxtwidth, lngTxtHeight)
    With Ctrl
       .ControlSource = FldList(j)
       .FontName = "Comic Sans MS"
       .FontSize = 8
       .FontWeight = 700
       .ForeColor = DarkBlue
       .BorderColor = DarkBlue
       .Name = FldList(j)
       .BackColor = RGB(255, 255, 255)
       .BorderStyle = 1
       .SpecialEffect = 0
     Select Case (j / 9)
     	Case 1,2,3
        	lngTxtTop = (0.0417 * twips)
        	lngTxtLeft = lngTxtLeft + (2.7084 * twips)
        Case Else
        	lngTxtTop = lngTxtTop + .Height + (0.1 * twips)
     End Select
    End With

   Set Ctrl = CreateReportControl(Rpt.Name, acLabel, acDetail, FldList(j), FldList(j), lngLblLeft, lngLblTop, lngLblWidth, lngLblHeight)
    With Ctrl
       .Caption = FldList(j)
       .Height = (0.2181 * twips)
       .Name = FldList(j) & " Label"
       .Width = twips
       .ForeColor = 0
       .BorderStyle = 0
       .FontWeight = 400
       Select Case (j/9)
       		Case 1,2,3
              lngLblTop = (0.0417 * twips)
        	  lngLblLeft = lngLblLeft + (2.7083 * twips)
       		Case Else
        	  lngLblTop = lngLblTop + .Height + (0.1 * twips)
       End Select
    End With
Next

lngLblWidth = 4.5 * twips
lngLblLeft = 0.073 * twips
lngLblTop = 0.0521 * twips
lngLblHeight = 0.323 & twips
lngLblWidth = 4.5 & twips
 Set Ctrl = CreateReportControl(Rpt.Name, acLabel, acPageHeader, , "Head1", lngLblLeft, lngLblTop, lngLblWidth, lngLblHeight)
   With Ctrl
        .Caption = strFile
        .TextAlign = 2
        .Width = 4.5 * twips
        .Height = 0.38 * twips
        .ForeColor = DarkBlue
        .BorderStyle = 0
        .BorderColor = DarkBlue
        .FontName = "Times New Roman"
        .FontSize = 20
        .FontWeight = 700 ' Bold
        .FontItalic = True
        .FontUnderline = True
   End With

Page_Footer Rpt

DoCmd.OpenReport Rpt.Name, acViewPreview

Columns_Exit:
Exit Function

Columns_Err:
MsgBox Err.Description, , "Columns"
Resume Columns_Exit
End Function

The Tabular Type Report Creation Function.

The major part of the Code lines in both these Wizards are Variable declarations for defining the TextBox and for its Child Label Controls, and for their dimension values, other values like Font, Font Size, ForeColor, and other attribute values settings also come after the creation of these controls.

The statement Set Ctrl = CreateReportControl():

    Set Ctrl = CreateReportControl(Rpt.Name, acTextBox, _
   acDetail, , FldList(j), lngTxtLeft, lngTxtTop, lngtxtwidth, lngTxtHeight)

have several Parameters which need their values predefined before calling the CreateReportControl() Function. The first Parameter is the Report Name, next is the type of control (here the TextBox), where to create the Control (in the Detail Section), next is the Parent Parameter if it is a SubReport (here omitted), the fifth parameter is the Field Name and the next four parameters are the control's dimension values.

The Font and Color attributes of the control are set after its creation.  Similarly, the TextBox control's Child Label Control is created next in the Page-Header Section of the Report. 

In the above Column-Format Report, the Label Control is created in the Detail Section and to the left side of each TextBox Control. The TextBox is created after leaving enough space for the child-label control on the left side.

Public Function Tabular(ByVal DataSource As String)

Dim cdb As Database
Dim FldList() As String
Dim Ctrl As Control
Dim Rpt As Report
Dim PgSection As Section
Dim DetSection As Section

Dim lngTxtLeft As Long
Dim lngTxtTop As Long
Dim lngTxtHeight As Long
Dim lngtxtwidth As Long

Dim lngLblLeft As Long
Dim lngLblTop As Long
Dim lngLblHeight As Long
Dim lngLblWidth As Long

Dim FldCheck As Boolean
Dim strTblQry As String
Dim intflds As Integer
Dim lstcount As Long
Dim RptFields As ListBox
Dim j As Integer

'Create Report with Selected Fields

On Error Resume Next
strFile = DataSource

Set RptFields = fom.SelList
lstcount = RptFields.listcount

If lstcount = 0 Then
   MsgBox "Fields Not Selected for Report!"
   Exit Function
Else
   lstcount = lstcount - 1
End If

ReDim FldList(0 To lstcount) As String

Set cdb = CurrentDb
'Create Report Object
Set Rpt = CreateReport
Set PgSection = Rpt.Section(acPageHeader)
    PgSection.Height = 0.6667 * twips

Set DetSection = Rpt.Section(acDetail)
    DetSection.Height = 0.1667 * twips

For j = 0 To lstcount
  FldList(j) = RptFields.ItemData(j)
Next

With Rpt
    .Caption = strFile
    .RecordSource = strFile
    
    lngtxtwidth = 0.5 * twips
    lngTxtLeft = 0.073 * twips
    lngTxtTop = 0
    lngTxtHeight = 0.1668 * twips

    lngLblWidth = lngtxtwidth
    lngLblLeft = lngTxtLeft
    lngLblTop = 0.5 * twips
    lngLblHeight = lngTxtHeight
End With

For j = 0 To lstcount
   Set Ctrl = CreateReportControl(Rpt.Name, acTextBox, _
   acDetail, , FldList(j), lngTxtLeft, lngTxtTop, lngtxtwidth, lngTxtHeight)
    With Ctrl
       .ControlSource = FldList(j)
       .ForeColor = DarkBlue
       .BorderColor = DarkBlue
       .BorderStyle = 1
       .Name = FldList(j)
       lngTxtLeft = lngTxtLeft + (0.5 * twips)
    End With
   
   Set Ctrl = CreateReportControl(Rpt.Name, acLabel, _
   acPageHeader, , FldList(j), lngLblLeft, lngLblTop, lngLblWidth, lngLblHeight)
   
    With Ctrl
       .Caption = FldList(j)
       .Name = FldList(j) & " Label"
       .Width = (0.5 * twips)
       .ForeColor = DarkBlue
       .BorderColor = DarkBlue
       .BorderColor = 0
       .BorderStyle = 1
       .FontWeight = 700 ' Bold
       lngLblLeft = lngLblLeft + (0.5 * twips)
    End With
Next

lngLblWidth = 4.5 * twips
lngLblLeft = 0.073 * twips
lngLblTop = 0.0521 * twips
lngLblHeight = 0.323 & twips
lngLblWidth = 4.5 & twips
 Set Ctrl = CreateReportControl(Rpt.Name, acLabel, acPageHeader, , "Head1", lngLblLeft, lngLblTop, lngLblWidth, lngLblHeight)
   With Ctrl
        .Caption = strFile
        .TextAlign = 2
        .Width = 4.5 * twips
        .Height = 0.38 * twips
        .ForeColor = DarkBlue
        .BorderStyle = 0
        .BorderColor = DarkBlue
        .FontName = "Times New Roman"
        .FontSize = 16
        .FontWeight = 700 ' Bold
        .FontItalic = True
        .FontUnderline = True
   End With
On Error GoTo Tabular_Err

Page_Footer Rpt

DoCmd.OpenReport Rpt.Name, acViewPreview

Tabular_Exit:
Exit Function

Tabular_Err:
MsgBox Err.Description, , "Tabular"
Resume Tabular_Exit
End Function

The Page_Footer() Function Code.

This Function is called by both the Column and Tabular Wizards to create the Date and Page Numbers in the PageFooter Section of the Report.

Public Function Page_Footer(ByRef obj)
Dim lngWidth As Long, ctrwidth As Long, ctrlCount As Long
Dim j As Long, cdb As Database
Dim lngleft As Long, lngtop As Long, LineCtrl As Control, Ctrl As Control
Dim rptSection As Section, leftmost As Long, lngheight As Long
Dim rightmost As Long, RightIndx As Integer
'
'Note : The Controls appearing in Detail Section from left to Right
'       is not indexed 0 to nn in the order of placing,
'       instead 1st control placed in the Section has index value 0
'       irrespective of its current position.
'
On Error GoTo Page_Footer_Err

Set cdb = CurrentDb
Set rptSection = obj.Section(acDetail)

ctrlCount = rptSection.Controls.Count - 1

lngleft = rptSection.Controls(0).Left
rightmost = rptSection.Controls(0).Left

'indexed 0 control may not be the leftmost control on the Form/Report
'so find the leftmost control's left value
For j = 0 To ctrlCount
 leftmost = rptSection.Controls(j).Left
 
 If leftmost < lngleft Then
   lngleft = leftmost
 End If
 If leftmost > rightmost Then
   rightmost = leftmost
   RightIndx = j
 End If
Next
 
lngtop = 0.0208 * 1440
lngWidth = 0: ctrwidth = 0

   lngWidth = rightmost + rptSection.Controls(RightIndx).Width
   lngWidth = lngWidth - lngleft
   
  Set LineCtrl = CreateReportControl(obj.Name, acLine, acPageFooter, "", "", lngleft, lngtop, lngWidth, 0)
  Set Ctrl = LineCtrl
  LineCtrl.BorderColor = 12632256
  LineCtrl.BorderWidth = 2
  LineCtrl.Name = "ULINE"
  
lngtop = 0.0418 * 1440
lngleft = LineCtrl.Left
lngWidth = 2 * 1440
lngheight = 0.229 * 1440

'draw Page No control at the Report footer
Set LineCtrl = CreateReportControl(obj.Name, acTextBox, acPageFooter, "", "", lngleft, lngtop, lngWidth, lngheight)
With LineCtrl
   .ControlSource = "='Page : ' & [page] & ' / ' & [pages]"
   .Name = "PageNo"
   .FontName = "Arial"
   .FontSize = 10
   .FontWeight = 700
   .TextAlign = 1
End With
'draw Date Control at the right edge of the Line Control
'calculate left position of Date control

lngleft = (LineCtrl.Left + Ctrl.Width) - lngWidth
Set LineCtrl = CreateReportControl(obj.Name, acTextBox, acPageFooter, "", "", lngleft, lngtop, lngWidth, lngheight)
With LineCtrl
   .ControlSource = "='Date : ' & Format(Date(),'dd/mm/yyyy')"
   .Name = "Dated"
   .FontName = "Arial"
   .FontSize = 10
   .FontWeight = 700
   .TextAlign = 3
End With

Page_Footer_Exit:
Exit Function

Page_Footer_Err:
MsgBox Err & ": " & Err.Description, "Page_Footer()"
Resume Page_Footer_Exit
End Function

There are several Command Buttons on both Pages of the TabControl and all their Event Subroutines are run in the RWiz_CmdButton Wrapper Class. There is one Command Button on the second Page with the caption Finish that runs the Report Wizard's Functions. All the Wizard Functions are placed in the WizObject_Init Class Module. For that reason, a separate Command Button Control Instance is defined for the cmdFinish in the WizObject_Init Class Module. The Command Button name on the Form is cmdReport with the caption Finish.  The cmdFinish Instance created in the Class Module is not added to the Collection Object after enabling the OnClick Event. 

The Click Event Subroutine of this Command Button is written in the WizObject_Init Class Module so that the Report Wizard Functions can be called from this Module directly.

At the beginning of the Class_Init() Subroutine, the Create_FilesList() Function is called to create the ComboBox's source list of Tables and Select Queries, followed by the creation of ListBoxes, Command Buttons instances, enabling their Events and adding them to the Collection Object. 

The cmdReport Click Event calls the Report Creation Function. The Column Type Report is not likely to be used, but it is useful for Label Printing.

The RWiz_CmdButton Class Module.

This Wrapper Class Module of CommandButton Object contains the following Command Button Click Event Subroutines.

Option Compare Database
Option Explicit

Private WithEvents cmd As CommandButton
Private frm As Form
Dim DarkBlue As Long, twips As Long, xtyp As Integer, strFile As String

Public Property Get w_Frm() As Form
    Set w_Frm = frm
End Property

Public Property Set w_Frm(ByRef wFrm As Form)
    Set frm = wFrm
End Property

Public Property Get w_cmd() As CommandButton
    Set w_cmd = cmd
End Property

Public Property Set w_cmd(ByRef wcmd As CommandButton)
    Set cmd = wcmd
End Property

Private Sub cmd_Click()
Dim lblInfo As String

  Select Case cmd.Name

    Case "cmdCancel2"
        DoCmd.Close acForm, frm.Name
               
    Case "cmdNext"
    If frm.SelList.listcount = 0 Then
        frm.cmdReport.Enabled = False
    Else
        frm.cmdReport.Enabled = True
    End If
    
  'Display the Wizard selection along with
  'the Table/Query selected in a Label Control
  'In the 2nd Page when the User Clicks
  'the cmdNext Command Button to display
  'the 2nd Page of the Wizard.
    lblInfo = "Table/Query: " & frm!FilesList
    If frm!WizList = 1 Then
        lblInfo = lblInfo & " - Column Report."
    Else
        lblInfo = lblInfo & " - Tabular Report."
    End If
    frm!info.Caption = lblInfo
    
 'Create the field List of the selected Table
 'and display them in the 1st ListBox on the
 '2nd Page of the Report Wizard.
       Call SelectTable
       
    Case "cmdCancel"
        DoCmd.Close acForm, frm.Name
        
    Case "cmdRight"
'Move the selected field to the Right=side ListBox.
'Multiselect option not given
        RightAll 1

    Case "cmdRightAll"
'Option Number Moves all the fields from
'Left side ListBox to the Right-side ListBox
        RightAll 2

    Case "cmdLeft"
        LeftAll 1
        
    Case "cmdLeftAll"
        LeftAll 2
    
    Case "cmdBack"
    'Go back to first Page. cancels the 2nd Page selections.
        frm.SelList.RowSource = "" 'Empty Selected field list
        frm.FilesList.RowSource = "WizQuery"
        frm.Page1.Visible = True
        frm.Page1.SetFocus
        frm.Page2.Visible = False
               
End Select
End Sub

Private Sub SelectTable()
Dim vizlist As ListBox
Dim lcount As Integer
Dim chkflag As Boolean
Dim FildList As ListBox
Dim strName As String
Dim strRSource As String
Dim cdb As DAO.Database
Dim doc As Document
Dim Tbl As DAO.TableDef
Dim Qry As DAO.QueryDef
Dim QryTyp As Integer
Dim FieldCount As Integer
Dim flag As Byte
Dim j As Integer

Set vizlist = frm.WizList
lcount = vizlist.listcount - 1

chkflag = False
For j = 0 To lcount
  If vizlist.Selected(j) = True Then
    xtyp = j + 1
    chkflag = True
  End If
Next

If IsNull(frm![FilesList]) = True Then
   MsgBox "Select a File from Table/Query List.", vbOKOnly + vbExclamation, "cmdNext"
   frm.WizList.Selected(0) = True
Else
   strFile = frm.FilesList
   frm.Page2.Visible = True
   frm.Page2.SetFocus
   frm.Page1.Visible = False
   
Set cdb = CurrentDb
flag = 0
For Each Tbl In cdb.TableDefs
    If Tbl.Name = strFile Then
       flag = 1
    End If
Next
For Each Qry In cdb.QueryDefs
    If Qry.Name = strFile Then
       flag = 2
    End If
Next
If flag = 1 Then
    Set Tbl = cdb.TableDefs(strFile)
    Set FildList = frm.FldList
    strRSource = ""
    FieldCount = Tbl.Fields.Count - 1
    For j = 0 To FieldCount
        If Len(strRSource) = 0 Then
            strRSource = Tbl.Fields(j).Name
        Else
            strRSource = strRSource & ";" & Tbl.Fields(j).Name
        End If
    Next
ElseIf flag = 2 Then
    Set Qry = cdb.QueryDefs(strFile)
    strRSource = ""
    FieldCount = Qry.Fields.Count - 1
    For j = 0 To FieldCount
        If Len(strRSource) = 0 Then
            strRSource = Qry.Fields(j).Name
        Else
            strRSource = strRSource & ";" & Qry.Fields(j).Name
        End If
    Next
End If

frm.FldList.RowSource = strRSource
frm.FldList.Requery
End If

End Sub

Private Function RightAll(ByVal SelectionType As Integer)
Dim FldList As ListBox, SelctList As ListBox, strRSource As String
Dim listcount As Long, j As Long, strRS2 As String

'On Error GoTo RightAll_Err
If SelectionType = 0 Then
   Exit Function
End If
Set FldList = Forms("ReportWizard").FldList
Set SelctList = Forms("ReportWizard").SelList

listcount = FldList.listcount - 1
strRSource = SelctList.RowSource: strRS2 = ""

Select Case SelectionType
    Case 1
        For j = 0 To listcount
            If FldList.Selected(j) = True Then
                If Len(strRSource) = 0 Then
                    strRSource = FldList.ItemData(j)
                Else
                    strRSource = strRSource & ";" & FldList.ItemData(j)
                End If
            Else
                If Len(strRS2) = 0 Then
                    strRS2 = FldList.ItemData(j)
                Else
                    strRS2 = strRS2 & ";" & FldList.ItemData(j)
                End If
            End If
        Next
        SelctList.RowSource = strRSource
        FldList.RowSource = strRS2
        SelctList.Requery
        FldList.Requery
    frm.cmdReport.Enabled = True
    Case 2

        For j = 0 To listcount
            If Len(strRSource) = 0 Then
                strRSource = FldList.ItemData(j)
            Else
                strRSource = strRSource & ";" & FldList.ItemData(j)
            End If
        Next
        SelctList.RowSource = strRSource
        FldList.RowSource = ""
        SelctList.Requery
        FldList.Requery
        frm.cmdCancel2.SetFocus
    If SelctList.listcount = 0 Then
        frm.cmdReport.Enabled = False
    End If
End Select
frm.cmdReport.Enabled = True

RightAll_Exit:
Exit Function

RightAll_Err:
MsgBox Err & ": " & Err.Description, , "RightAll"
Resume RightAll_Exit
End Function

Private Function LeftAll(ByVal SelectionType As Integer)
Dim FldList As ListBox, SelctList As ListBox, strRSource As String
Dim listcount As Long, j As Long, strRS2 As String

On Error GoTo LeftAll_Err

If SelectionType = 0 Then
   Exit Function
   
End If

Set FldList = Forms("ReportWizard").FldList
Set SelctList = Forms("ReportWizard").SelList

listcount = SelctList.listcount - 1
strRSource = FldList.RowSource: strRS2 = ""

Select Case SelectionType
    Case 1
        For j = 0 To listcount
            If SelctList.Selected(j) = True Then
                If Len(strRSource) = 0 Then
                    strRSource = SelctList.ItemData(j)
                Else
                    strRSource = strRSource & ";" & SelctList.ItemData(j)
                End If
            Else
                If Len(strRS2) = 0 Then
                    strRS2 = SelctList.ItemData(j)
                Else
                    strRS2 = strRS2 & ";" & SelctList.ItemData(j)
                End If
            End If
        Next
        SelctList.RowSource = strRS2
        FldList.RowSource = strRSource
        SelctList.Requery
        FldList.Requery
    If SelctList.listcount = 0 Then
        frm.cmdReport.Enabled = False
    End If
    Case 2
        For j = 0 To listcount
            If Len(strRSource) = 0 Then
                strRSource = SelctList.ItemData(j)
            Else
                strRSource = strRSource & ";" & SelctList.ItemData(j)
            End If
        Next
        SelctList.RowSource = ""
        FldList.RowSource = strRSource
        SelctList.Requery
        FldList.Requery
    If SelctList.listcount = 0 Then
        frm.cmdReport.Enabled = False
    End If
End Select
LeftAll_Exit:
Exit Function

LeftAll_Err:
MsgBox Err.Description, , "LeftAll"
Resume LeftAll_Exit

End Function

The Set of four Command Buttons, between the List Boxes on the second Page of the Wizard Form, controls the Field select/unselect operations. The first button moves the selected field from the first list to the second Listbox for the Report only one field at a time. The second Command Button with two greater-than symbols moves all the fields in the first ListBox to the 2nd ListBox.

The next Command Button removes the item selected from the second ListBox and places it back in the first ListBox. The fourth Command Button Click will remove all the List items selected earlier in the second ListBox for Report and move them all together back in the first ListBox.

The Back Command Button Click will empty the second ListBox and go back to the Report Wizard's first Page.

The RWiz_Combo Class Module Code

Option Compare Database
Option Explicit

Private cbofrm As Access.Form
Private WithEvents cbo As Access.ComboBox 'ComboBox object

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

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

Public Property Set cbo_Frm(ByRef cfrm As Form)
    Set cbofrm = cfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get c_cbo() As ComboBox
    Set c_cbo = cbo
End Property

Public Property Set c_cbo(ByRef pcbo As ComboBox)
    Set cbo = pcbo
End Property

Private Sub cbo_Click()
        cbofrm!FileList = Null

        cbofrm.TabCtl0.Pages(0).Visible = True
        cbofrm.TabCtl0.Pages(0).SetFocus
        cbofrm.TabCtl0.Pages(1).Visible = False
        cbofrm.TabCtl0.Pages(1).SetFocus
End Sub

Private Sub cbo_GotFocus()
    GFColor cbofrm, cbo
End Sub

Private Sub cbo_LostFocus()
    LFColor cbofrm, cbo
End Sub

The RWiz_ListBox Class Module Code.

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  : 20/10/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_Click()
Dim i As Integer

Select Case lst.Name
    Case "WizList"
        'Code
    Case "FldList"
        'Code
    Case "SelList"
        'Code
End Select

End Sub

Private Sub lst_GotFocus()
    GFColor lstfrm, lst
End Sub

Private Sub lst_LostFocus()
    LFColor lstfrm, lst
End Sub

The ListBox and ComboBox Class Module Subroutine Code highlights the Control when these controls receive the Focus.

The RWiz_TabCtl Class Module Code.

Option Compare Database
Option Explicit

Private tbFrm As Form
Private WithEvents tb As TabControl

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

Public Property Get Tb_Frm() As Form
    Set Tb_Frm = tbFrm
End Property

Public Property Set Tb_Frm(ByRef mFrm As Form)
    Set tbFrm = mFrm
End Property

Public Property Get Tb_Tab() As TabControl
    Set Tb_Tab = tb
End Property

Public Property Set Tb_Tab(ByRef mTab As TabControl)
    Set tb = mTab
End Property

Private Sub tb_Change()
Select Case tb.Value
    Case 0
        'MsgBox "Change Event: TabCtl.Page(0)"
    Case 1
        'MsgBox "Change Event: TabCtl.Page(1)"
End Select
        
End Sub

This Wrapper Class Module has the TabPage_Change() Event included for completeness, but not used for any purposes.

Download the Demo Database from the Link given below.


Streamlining Form Module Code in Standalone Class Module.

  1. Re-using Form Module VBA Code for New Projects.
  2. Streamlining Form Module Code Part-Two.
  3. Streamlining Form Module Code Part-Three.
  4. Streamlining Form Module Code Part-Four.
  5. Streamlining Form Module Code Part-Five.
  6. Streamlining Form Module Code Part-Six.
  7. Streamlining Form Module Code Part-Seven.
  8. Streamlining Form Module Code Part-Eight.
  9. Streamlining Form Module Code Part-Nine.
  10. Streamlining Form Module Code Part-Ten.
  11. Streamlining Form Module Code Part-Eleven.
  12. Streamlining Report Module Code in Class Module.
  13. Streamlining Report Module Code in Class Module-2.
  14. Streamlining Form Module Code Part-14:All Controls
  15. Streamlining Custom Made Form Wizard-15
  16. Streamlining Custom Report Wizard-16
  17. Streamlining Form VBA External File Browser-17
  18. Streamlining Event Procedures of 3D TextWizard-18


Share:

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