Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Access Form Control Arrays and Event-3

This is the continuation of last Week’s Topic on Text Box AfterUpdate and LostFocus Event capturing and performing Validation Checks on values, in Class Module Array.

Last week we have stopped short of discussing how to move all the VBA Code from the Form_Load() Event Procedure into another Class Module and leave the Form Module almost empty of all Event Procedures.  The VBA Code that defines the Text Box Control Class Module Array and invoking the required built-in Events in their respective Array elements.  This method will need only three or four lines of Code on the Form Module and will take care of all the actions in a Derived Class Module Object.

Earlier we have created Derived Class Objects using Class Module as Base Class to extend the functionality of the Base Class.  We will do that here too.

We have taken the Text Box Control first, instead of any other Control on the Form, for the Array based examples because Text Boxes are the main controls used on Form.  Text Box have several Events, like BeforeUpdate, AfterUpdate, LostFocus, OnEnter, OnExit, KeyDown, KeyUp, OnKey and several others, and depending on the requirements we can invoke one or more of the above Events in the Derived Class Object. 

We can write some standard Event Procedures in the Class Module for all of the above sample Events  in the Text Box’s Class Module.  But, invoke only those required one with the statement  obj.txt.EventName = “[Event Procedure]” during the Array element declaration time. 

The Class Module Event Sub-Routines may require customization of Code for different Form’s Text Box control values.  The best approach is to create a Text Box Class Module Template with most frequently used Event Procedures.  Create a copy and customize it for specific requirement.

Other Controls on the Form, like Command Buttons, Combo Boxes, List Boxes and others, mostly  use the Click or Double-Click Events only. We will take up this topic of managing different type of control Arrays on the same Form.

We will explore the possibility of better ways than Arrays to manage instances of different type of  Controls on the Form.

Coming back to today’s topic, moving the Form Module Code to a separate Class Module, we will create a Derived Class Module Object using the ClsTxtArray1_2 Class Module as Base Class.  Move the Form Module Form_Load() Event Procedure Code into the new Class Object.

If you have not downloaded last week’s Demo Database then download it from the following link, before continuing.  We will make copies of the Modules and Form to modify the Code so that you will have both version of the Code and Form on the same database.  After making changes to the Code and Form Module you can instantly run and see how it works.

After downloading the database Open it in Ms-Access.  You may open the Form Module and check the Code.

We have to copy the Class Module (ClsTxtArray1_2) Code into a new Class Module with a different name ClsTxtArray1_3, but without any change in the code.  Form also must  be copied with a different name frmTxtArray1_3Header.  The changes, if any, will be done in the new Copy leaving the original safe and un-altered.  This way the earlier Form and Class module will remain safe.

We will use last week’s sample Form (the image is given below) and the Form Module VBA Code also reproduced below for reference.

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_2

Private Sub Form_Load()
Dim cnt As Integer
Dim ctl As Control

For Each ctl In Me.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     If ctl.Name = "Text8" Then
       Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Else
       Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End If
     
  End If
Next
End Sub

Make a Copy of the above Form and name it as frmTxtArray1_3Header.

Create a new Class Module with the name ClsTxtArray1_3.  Copy the VBA Code from ClsTxtArray1_2 Class Module and Paste it into the new Module.

Last week’s Class Module ClsTxtArray1_2  Code reproduced below for reference.

Option Compare Database
Option Explicit

Private WithEvents Txt As Access.TextBox

Public Property Get mTxt() As Access.TextBox
  Set mTxt = Txt
End Property

Public Property Set mTxt(ByRef txtNewValue As Access.TextBox)
  Set Txt = txtNewValue
End Property

Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = Txt.Name
msg = ""
Select Case txtName
    Case "Text0"
        'Valid value range 1 to 5 only
        varVal = Nz(Txt.Value, 0)
        If varVal < 1 Or varVal > 5 Then
           msg = "Valid Value Range 1-5 only: " & varVal
        End If
    Case "Text8"
        'validates in LostFocus Event
    Case "Text10"
        'valid value 10 characters or less
        'Removes extra characters, if entered
        varVal = Nz(Txt.Value, "")
        If Len(varVal) > 10 Then
           msg = "Max 10 Characters Only. " & varVal
           Txt.Value = Left(varVal, 10)
        End If
    Case "Text12"
        'Date must be <= today
        'Future date will be replaced with Today's date
        varVal = DateValue(Txt.Value)
        If varVal > Date Then
          msg = "Future Date Invalid. " & varVal & vbCr & "Corrected to Today's Date."
          Txt.Value = Date
        End If
    Case "Text14"
        'A 10 digit number only valid
        varVal = Trim(Str(Nz(Txt.Value, 0)))
        If Len(varVal) <> 10 Then
          msg = "Invalid Mobile Number: " & varVal
        End If
End Select

If Len(msg) > 0 Then
    MsgBox msg, vbInformation, Txt.Name
End If

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant, msg As String

tbx = Nz(Txt.Value, "")

msg = ""
If Len(tbx) = 0 Then
  msg = Txt.Name & " cannot be left Empty."
  Txt.Value = "XXXXXXXXXX"
End If

If Len(msg) > 0 Then
   MsgBox msg, vbInformation, Txt.Name
End If

End Sub

The ClsTxtArray1_3 Class Module will be used as the Base Class for our new Derived Class Module, we will name it as ClsTxtArray1_3Header, with extended functionality.

Create a new Class Module with the name ClsTxtArray1_3Header. The Derived Class Module, with it's Properties and Property Procedures are given below:

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_3
Private frm As Access.Form

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

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
 'Form Module Code goes here
End Sub

Copy and paste the above Code into the new Header Class Module you have created.

Check the first two Property declarations.  First Property ClsTxtArray1_3 Class Object is instantiated as an undefined Array: Ta() – Ta stands for TextBox-Array.

Next Property frm is to take control of the Form, from where we plan to transfer the VBA Code here and whatever we did there will be done here. 

Next Get/Set Property Procedure will take care of the Form’s references.  It is  Set Property Procedure not Let, because we are passing a Form Object to it.

Immediately after the Form’s reference is received in the Set Property Procedure we call the Class_Init() (this is not the same as Class_Initialize() that runs automatically when a Class Object is instantiated) sub-routine to run the same code moved here from the Form’s Module.

Now, we will transfer the following Code from the Form_Load() Event Procedure into the Class_Init() sub-routine and make changes in the Form Module.

Copy and Paste the following lines of Code from the Form Module into the Class_init() sub-routine, replacing the Comment line:

Dim cnt As Integer
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End Select
     
  End If
Next

Open the Form frmTxtArray1_3Header in design view. Display the Code Module. Copy and Paste the following Code into the Form's Module, overwriting the existing Code:

Option Compare Database
Option Explicit

Private T As New ClsTxtArray1_3Header

Private Sub Form_Load()
  Set T.mFrm = Me
End Sub

We have instantiated the Derived Class ClsTxtArray1_3Header in Object Variable T,. With the statement Set T.mFrm = Me the active form's reference is passed to the T.mFrm() Set Property Procedure.

Immediately after this action, on the form_Load() Event procedure, the Class_Init() sub-routine runs in the ClsTxtArray1_3Header Class and the txtArray1_3 Class Object array elements are created with invoking Events for each Text Box on the Form.  Hope you are clear with the Code above.

If you are ready with modifying the Form Module, Compile the database to ensure that everything is in order.

Save and close the Form, Open it in Normal View and try out each Text Boxes and ensure that their Event sub-routines are performing as expected.

The TextBox Class Object Array method works fine for several Text Boxes.  But, to create an Array, it should have a counter variable, Re-dimension the array for new element preserving the data in earlier array elements.  Increment the counter variable for the next Text Box on the Form and so on. 

When there are several controls of the other types also on the Form ( like Command Buttons, Commbo Boxes etc.)  we need to create Arrays of that Type’s Class Objects separately, with separate counter and re-dimension those controls Array too in the Class Module.  We will try these sometime later to learn how to do it.

But, a better way to manage these complex situation is to use the Collection Object, in place of Array.  We will run a demo here itself with the Text Boxes to get a feel of this method.

  1. Create a new Derived Class Module with the name ClsTxtArray1_3Coll.
  2. Copy and Paste the following Code into the Class Module:
Option Compare Database
Option Explicit

Private C As New Collection
Private Ta As ClsTxtArray1_3
Private frm As Access.Form

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

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
'-----------------------------
'Usage of Collection Object, replacing Arrays
'-----------------------------
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     
     Set Ta = New ClsTxtArray1_3  'instantiate TextBox Class
     Set Ta.Txt = ctl 'pass control to Public Class Property
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta.Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta.Txt.AfterUpdate = "[Event Procedure]"
     End Select
     C.Add Ta 'add to Collection Object
  End If
Next

End Sub

A Collection Object Property is declared and instantiated at the beginning. 

The TextBox Class Module is defined, not instantiated, in Object Variable Ta.

The TextBox Class Ta Object is instantiated within the Control Type Test condition.  A new Ta Object instance is created for each TextBox on the Form.

After enabling the Events the Ta Class Object is added to the Collection Object as it’s Item.

This method is repeated by adding new instance of the TextBox Class Object for  each Text Box on the Form, with it’s required Events enabled, as a new Item to the Collection Object.  The Code is cleaner than the Array method.

Make a Copy of the Form frmTxtArray1_3Header with the name frmTxtArray1_3Coll

  1. Open it in Design View and display the Form's Code Module.
  2. Copy and Paste the Following Code into the Form Module, replacing the existing Code.
Option Compare Database
Option Explicit

Private Ta As New ClsTxtArray1_3Coll

Private Sub Form_Load()
  Set Ta.mFrm = Me
End Sub

The only change here is the Derived Object's name change to ClstxtArray1_3Coll. Compile the database again.

Save the Form, Open it in normal View. Test the Text Boxes as before.

It should work as before.

You may download the database with all the Modules and Forms with all the suggested changes.


  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
Share:

Access Form Control Arrays and Event-2

Last week we have learned how to create the Class Object Array (the Class Object that defines only a single Access.TextBox Control as it's Property) of TextBox Controls from MS-Access Form. The built-in AfterUpdate or LostFocus Events Raised from Text Boxes on the Form are captured in their respective Class Object Array element and executes the AfterUpdate() or LostFocus() sub-routine from there.

The AfterUpdate Event of the first TextBox is captured in the first element of the Class Object Array and the AfterUpdate() sub-routine is executed in there, instead of running the Code on the Form's Module, as we do normally. Similarly other Text Boxes Events are also handled in their respective Class Object Array element.

If you are a first timer on this topic you may visit the earlier pages, from the following links, for continuity and to know the transition of code from one stage to the next.

  1. withevents Button Combo List TextBox Tab
  2. Access Form Control Arrays and Event Capturing

The  AfterUpdate and LostFocus Event handler Code we have written earlier in the class module were of common nature and suits for all text boxes on the Form.  They were test sub-routines to monitor the events that triggered from all text boxes on the form are getting captured in their respective class module array element or not.

Now, it is time to define some basic data entry rules, for each text box on the form, and ensure that the User is made aware of the rule, when it is violated.

I have added some labels above all Text Boxes on the form showing how the text box value is validated in the class module array on AfterUpdate and LostFocus Events.

The image of the form, with text boxes and their validation rules on labels, is given below.

Note: Since, this is only for demonstration purposes the validation rules are not strictly enforced.  The second text box need only some value text or number etc.  The mobile number field checks  the length of the text box contents .  The Mobile Number Text Box’s Input Mask is set to accept only digits.

  1. The first text box accepts value range 1 to 5 only.  Any value outside this range triggers an Error Message.
  2. The second text box is validated OnLostFocus Event for the presence of some value in it, if it is left empty an error message will pop up and a sample string is inserted.
  3. The third text box accepts text or number up to 10 characters long, not more.  It removes the extra characters at the end and updates the field.  If it is left blank no error is displayed.
  4. Next text box is a date field and date greater than today is invalid.
  5. The last text box accepts only a 10 digit Number. 

We will write VBA Code for the above simple validation checks in the class module.

The earlier Version of vba code in the class module  ClstxtArray1 is given below for reference.

Option Compare Database
Option Explicit

Public WithEvents Txt As Access.TextBox

Private Sub Txt_AfterUpdate()
Dim txtName As String, sngval As Single
Dim msg As String

txtName = Txt.Name
sngval = Nz(Txt.Value, 0)

msg = txtName & " _AfterUpdate. :" & sngval
MsgBox msg, vbInformation, Txt.Name

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant
tbx = Nz(Txt.Value, "")

If Len(tbx) = 0 Then
  MsgBox Txt.Name & " is Empty.", vbInformation, Txt.Name
End If
End Sub

Check the above Txt_AfterUpdate() Event Procedure, this Event from all text boxes on the Form comes in to the same sub-routine in the class module.  The txt.Name property will have the text box name, from where the event is coming from,  and the txt.Value property will give us the value entered into the text box.  With these two values we can write code for validating each text box contents.

The text box validation sample vba code of txt_AfterUpdate() Event Sub-routine is given below.

Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = Txt.Name
msg = ""
Select Case txtName
    Case "Text0"
        varVal = Nz(Txt.Value, 0)
        If varVal < 1 Or varVal > 5 Then
           msg = "Valid Value Range 1-5 only: " & varVal
        End If
    Case "Text8"
        ' validation in OnLostFocus Event
    Case "Text10"
        varVal = Nz(Txt.Value, "")
        If Len(varVal) > 10 Then
           msg = "Max 10 Characters Only. " & varVal
           Txt.Value = Left(varVal, 10)
        End If
    Case "Text12"
        varVal = DateValue(Txt.Value)
        If varVal > Date Then
          msg = "Future Date Invalid. " & varVal
        End If
    Case "Text14"
        varVal = Trim(Str(Nz(Txt.Value, 0)))
        If Len(varVal) <> 10 Then
          msg = "Invalid Mobile Number: " & varVal
        End If
End Select

If Len(msg) > 0 Then
    MsgBox msg, vbInformation, Txt.Name
End If

End Sub

The text box name (Txt.Name) received from the AfterUpdate Event is checked in the Select Case. . . End Select structure.  Depending on the text box name and text box value (Txt.Value) the validation check is performed, if not valid an appropriate message is displayed.

On the Form_Load() Event Procedure we have added OnLostFocus() Event only for TextBox8 on the form.  When the insertion point leaves out of this text box the LostFocus Event fires and captures it in the Private Sub txt_LostFocus()  sub-routine of class module.  If the Text Box is empty the sample text string “XXXXXXXXXX” is inserted into TextBox8, followed by an error message.

The LostFocus sub-routine is given below:

Private Sub Txt_LostFocus()
Dim tbx As Variant, msg As String

tbx = Nz(Txt.Value, "")

msg = ""
If Len(tbx) = 0 Then
  msg = Txt.Name & " cannot leave it Empty."
  Txt.Value = "XXXXXXXXXX"
End If

If Len(msg) > 0 Then
   MsgBox msg, vbInformation, Txt.Name
End If

End Sub

Here we are not testing for TextBox8, as we did in the AfterUpdate() event procedure, because we have not added the LostFocus Event for any other TextBox. 

Did you notice the statement Txt.Value = "XXXXXXXXXX" that writes back the string into the same text box, from where the event is captured.  What do we do if we have to refer to some other control on the Form to read/write something there.  For that we have to introduce the Form Object Property in the Class Module.

We will do that along with the Code change, as we plan to move all actions from the Form Module to Class Module.

The full VBA Code of the Class Module: ClsTxtArray1_2 is given below:

Option Compare Database
Option Explicit

Public WithEvents Txt As Access.TextBox

Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = Txt.Name
msg = ""
Select Case txtName
    Case "Text0"
        varVal = Nz(Txt.Value, 0)
        If varVal < 1 Or varVal > 5 Then
           msg = "Valid Value Range 1-5 only: " & varVal
        End If
    Case "Text8"
        ' validation in OnLostFocus Event
    Case "Text10"
        varVal = Nz(Txt.Value, "")
        If Len(varVal) > 10 Then
           msg = "Max 10 Characters Only. " & varVal
           Txt.Value = Left(varVal, 10)
        End If
    Case "Text12"
        varVal = DateValue(Txt.Value)
        If varVal > Date Then
          msg = "Future Date Invalid. " & varVal
        End If
    Case "Text14"
        varVal = Trim(Str(Nz(Txt.Value, 0)))
        If Len(varVal) <> 10 Then
          msg = "Invalid Mobile Number: " & varVal
        End If
End Select

If Len(msg) > 0 Then
    MsgBox msg, vbInformation, Txt.Name
End If

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant, msg As String

tbx = Nz(Txt.Value, "")

msg = ""
If Len(tbx) = 0 Then
  msg = Txt.Name & " cannot leave it Empty."
  Txt.Value = "XXXXXXXXXX"
End If

If Len(msg) > 0 Then
   MsgBox msg, vbInformation, Txt.Name
End If

End Sub

The Form Module Code doesn't have any change from last week’s example, except that the Class Module Name is changed to ClstxtArray1_2. 

I preserve the Class Module Code used in earlier Articles in a separate Version of Class Module, that is why the name change here.

I have made a small change in the Form module Code for TextBox8 Control to Raise only the LostFocus Event. Earlier Code invokes both AfterUpdate and LostFocus Events.

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_2

Private Sub Form_Load()
Dim cnt As Integer
Dim ctl As Control

For Each ctl In Me.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     If ctl.Name = "Text8" Then
       Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Else
       Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End If    
  End If
Next
End Sub

We have moved all the event handling code, normally written on the Form’s Class Module, to the Class Module and all the actions in there is totally hidden from the User.  But, still looking at the above Form Module there is plenty of actions taking place on the Form_Load() Event Procedure.

We will pull some trick to move the entire action from the Form Module to a Class Module, leaving three or four lines of code on the form.  These and more in the coming weeks.

In the meantime you may download the Demo Database from the Links given below, try out and study the Code.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
Share:

Access Form Control Arrays and Event Capturing

Last week we have learned how to capture Events,  built-in and User-Defined, Raised from several type of Form-Controls and to run Event driven Programs in Class Module.  We have used Text Box, Command Button, Combo Box, List Box and Tab Control, one control of each type, on the Form. You may visit the earlier pages by clicking on the following Links:

Forms will have several objects of the same type.  For example Text Boxes, there will be several of them on the Form.  It is not possible to declare separate Text Box Property in Class Module, for each Text Box on the Form, to capture Events from each one of them.

The easiest technique is something like the following steps:

  1. Create a Class Module, with a single Text Box Object Property.
  2. Create separate Sub-Routines for  AfterUpdate, LostFocus etc. in the Class Module to handle these Events from Text Box(s) on the Form. 
  3. Create an Array of this Class Module Object in Form Module, or in a separate Class Module, with one Class Object element for each Text Box Control on the Form.
  4. The built-in Event Raised from a particular Text Box on the Form captures that Event by it's corresponding Class Object Array-Element and runs the sub-routine Code from there. 

Let us start with a simple example.

  1. Create a new Class Module and change its Name Property Value to clsTxtArray1.
  2. Copy and Paste the following VBA Code into the Class Module and Save it:
    Option Compare Database
    Option Explicit
    
    Public WithEvents Txt As Access.TextBox
    
    Private Sub Txt_AfterUpdate()
    Dim txtName As String, sngval As Single
    Dim msg As String
    
    txtName = Txt.Name
    sngval = Nz(Txt.Value, 0)
    
    msg = txtName & " _AfterUpdate. :" & sngval
    MsgBox msg, vbInformation, Txt.Name
    
    End Sub
    

    Declared the Text Box Object Txt with Public scope to avoid the Property Procedure Get/Set  for the time being to keep the Code in Class Module simple.

    The AfterUpdate() Event Procedure will execute when that Event of the Text Box fires on the Form.

  3. Create a new Form, insert a single Text Box on the Form and save the Form with the name frmTxtArray1.

    Image of the sample form is given below.

  4. Open the Form in design View and display the form's Code Module.
  5. Copy and Paste the following VBA Code into the Form Module:
    Option Compare Database
    Option Explicit
    
    Private ta() As New ClsTxtArray1
    
    Private Sub Form_Load()
    Dim cnt As Integer
    Dim ctl As Control
    
    For Each ctl In Me.Controls
      If TypeName(ctl) = "TextBox" Then
         cnt = cnt + 1
         ReDim Preserve ta(1 To cnt)
         Set ta(cnt).Txt = ctl
         ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
      End If
    Next
    End Sub
    

    In the global declaration area ClsTxtArray1 Class is instantiated as an empty Array Object.

    The real action is in the Form_Load() Event Procedure.

    Two Variables, Count (cnt) and Control (ctl), are declared.

    The For Each . . . Next Loop is set to scan the Form for all Control Types on the Form and identify the required control type: TextBox.

    If the Control TypeName is TextBox then cnt Variable is incremented by one. The ta Array is re-dimensioned for 1 to cnt of items, preserving the earlier elements of data, if any.

    The statement Set ta(cnt).Txt = ctl  assigns the current Class Object Array element’s txt Property with the TextBox Control.

    The next line ta(cnt).Txt.AfterUpdate = "[Event Procedure]" enables the TextBox’s AfterUpdate Event, so that it can be captured in the Private Sub txt_AfterUpdate() sub-routine of ta(cnt) instance of ClsTxtArray1 Array element.

  6. If you are through with the above lines then save and close the Form.

  7. Open the Form in normal view.

  8. Enter some numeric Value into the Text Box and press Tab Key. 

    A message box pops up with useful information in it.  Sample test run image is given below.

    Check the MsgBox image with event related info in there.

    The TexBox Name is shown in the Title area, from where the AfterUpdate Event is captured.  The message line indicates that it is run from AfterUpdate sub-routine and the value entered into the TextBox is displayed at the end of the line..

  9. Close the Form and Open it in Design View.
  10. Add four or five more Text Boxes on the Form, anywhere you like on the Form.
  11. Save and Close the Form.

    Sample Image of the changed Form is given below.

  12. Open the Form in normal View.
  13. Enter some numeric value in any newly added text box and press Tab Key.

    When you do this the MsgBox will pop up, displaying message like the earlier one. It will contain the TextBox Name and the number entered into the Text Box.

  14. Try out other Text Boxes also in this way.

    You may add more Text Boxes and try out whether newly added Text Boxes also respond to the AfterUpdate Event.

The After Update Event fires only when you enter some value or edit existing value and leave out of the Text Box.

But, what if a particular Text Box cannot be left blank when the insertion point leaves out of the Text Box (LostFocus Event) without keying in some value.  When the insertion point leaves out of that Text Box, the Form should fire the LostFocus Event.  The Event must be captured in Class Module and display a message, if it is empty.  To do that we must make changes in Form Module and in the Class Module.

In the Form_Load() Event Procedure, checks whether TextBox8 (on my Form the second TextBox at the left) is the current control then adds ta(cnt).Txt.OnLostFocus = "[Event Procedure]"  to trigger the LostFocus() Event on TextBox8. 

Add the following lines into the Form_Load() Event Procedure, replacing Text8 with the Text Box name from your Form.

If ctl.Name = "Text8" Then
   ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
End If

The current control's name is Text8 then ta(cnt).Txt.OnLostFocus Event is also set to invoke this Event.  So Text8 will fire both AfterUpdate and LostFocus Events.   

The changed Form_Load() Event Procedure Code is given below:

Private Sub Form_Load()
Dim cnt As Integer
Dim ctl As Control

For Each ctl In Me.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve ta(1 To cnt)
     Set ta(cnt).Txt = ctl
     ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     
     If ctl.Name = "Text8" Then
       ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     End If
     
  End If
Next
End Sub

A Sub-routine for LostFocus Event is required in the ClstxtArray1 Class Module, to capture the Event from the Text8 Text Box.

The Sub-routine code to handle the LostFocus Event is given below:

Private Sub Txt_LostFocus()
Dim tbx As Variant
tbx = Nz(Txt.Value, "")

If Len(tbx) = 0 Then
  MsgBox Txt.Name & " is Empty.", vbInformation, Txt.Name
End If
End Sub

If some Value is entered into the Text8 TextBox then the MsgBox will not appear for the LostFocus Event.  If the TextBox8 is empty then the LostFocus Event will fire.  The full Class Module Code is given below:

Option Compare Database
Option Explicit

Public WithEvents Txt As Access.TextBox

Private Sub Txt_AfterUpdate()
Dim txtName As String, sngval As Single
Dim msg As String

txtName = Txt.Name
sngval = Nz(Txt.Value, 0)

msg = txtName & " _AfterUpdate. :" & sngval
MsgBox msg, vbInformation, Txt.Name

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant
tbx = Nz(Txt.Value, "")

If Len(tbx) = 0 Then
  MsgBox Txt.Name & " is Empty.", vbInformation, Txt.Name
End If
End Sub

Each TextBox on a Form may accept different information and their validity Criterion may be different.  The validity of data, of each Text Box, must be checked individually to ascertain it’s acceptability and need to take action upon it, if it doesn’t conform to the requirements. 

The above code displays only a generalized message, for all Text Boxes, from the sub-routine.  That may not be sufficient for real applications.  We need to write specific Code for each TextBox , when the above Event fires from each Text Box.

We will continue this discussion next week for more details on this topic.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
Share:

WithEvents Button Combo List Textbox Tab

If you know how to program one control on the Form to capture the built-in Event in  Class Module then you know how to do it for all of them.  Last week we have used one Text box and two Command Buttons for our introductory  trial runs. 

Links of Last two Articles on this subject is given below, in case if you would like to refresh your memory:

Now, we know how to create User-Defined Events on Form and what it takes to invoke the Event from Form Module and capture it in Class Module based Program. 

We had sample trial runs on capturing built-in Events of Command Button Clicks and Text Box’s AfterUpdate Events in Class Module.  Even though we don’t write any Code within the Event Procedure on the Form Module we had to keep the empty Procedure lines intact on the Form Module in order to capture Event in the Class Module, when it happens on the Form. 

Now, we will include few other commonly used Form Controls on our sample form to try out to see how it works.  We will include the user-defined Events, which we tried out one week back, also in this trial run.

The image of the Form in normal view is given below.


Following are the list of Controls on the Form: ClsTestForm1

  1. Two Command Buttons
    • cmdRaise:  Click Event - Runs a Mind-Reading Game from Standard Module
    • cmdClose: Click Event – Closes the Form
  2. Combo Box – cboWeek: Click Event – Displays selected Day of Week
  3. List Box – lstMonth:  Click Event – Displays selected Month’s current date’s Day
  4. Text Box – Text1:
    •   AfterUpdate Event – Raises User Defined Events: QtyLess() & QtyMore()
    •   LostFocus Event – If TextBox is empty it displays a message.
  5. Tab Control – TabCtl9Change Event – TabControl Page Change Event.

ClsTestForm1 Class Module Code is given below:

Option Compare Database
Option Explicit

Private myFrm As New ClsEvent1
Public Event QtyLess(X As Long)
Public Event QtyMore(X As Long)
Public Event TbPage0(ByVal pageName As String)
Public Event TbPage1(ByVal pageName As String)

'Keep the comment line within the Event Procedures
'Otherwise compiler will clear the Empty Event Procedures

Private Sub cboWeek_Click()
  'comment
End Sub

Private Sub cmdClose_Click()
'comment
End Sub

Private Sub cmdRaise_Click()
    'comment
End Sub

Private Sub Form_Load()
   Set myFrm.frmMain = Me
End Sub

Private Sub lstMonth_Click()
  'comment
End Sub

Private Sub TabCtl9_Change()
Dim strName As String

If TabCtl9.Value = 0 Then
   strName = TabCtl9.Pages(0).Name
   RaiseEvent TbPage0(strName)
Else
   strName = TabCtl9.Pages(1).Name
   RaiseEvent TbPage1(strName)
End If

End Sub

Private Sub Text1_AfterUpdate()
'Userdefined Events
Dim q As Long
  q = Nz(Me!Text1, 0)
  If q < 1 Then
     RaiseEvent QtyLess(q)
  End If
  If q > 5 Then
     RaiseEvent QtyMore(q)
  End If
End Sub

Private Sub Text1_LostFocus()
'cmnt
End Sub

The Class Module Object clsEvent1 is instantiated in myFrm Object Variable.

Next two lines in the Global declaration area defines four User-Defined Events: QtyLess() and QtyMore(), TbPage0() and TbPage1().  The first two Events will be Raised based on the Value entered into the Text Box and the Other two Raised on TabCtl9_Change() Event.

On the Form_Load() Event Procedure the current Form Object is passed to the Property Procedure by the statement Set myFrm.frmMain = Me.

The Text1_AfterUpdate() Event Procedure tests the entered Value in the Text1 Text Box,  validated and if the value doesn't fall within the valid range then one of the User-Defined Event is raised.

For the Tab Control we have defined two User-Defined Events to capture the change of Tab Control Pages.  When you make a page active one of the Event related to that Page is Raised.  For example: when you make the first TabPage active the user-defined Event TbPage0() is Raised and captured in the Class Module.

Other blank Event Procedures are place holders for invoking the respective built-in Events and capturing them in the Class Module Object to take appropriate action.

The Class Module ClsEvent1 VBA Code is given below:

Option Compare Database Option Explicit Private WithEvents frm As Form_clsTestForm1 Private WithEvents btn1 As commandbutton Private WithEvents btn2 As commandbutton Private WithEvents txt As TextBox Private WithEvents cbo As ComboBox Private WithEvents lst As ListBox Public Property Get frmMain() As Form_clsTestForm1 Set frmMain = frm End Property Public Property Set frmMain(ByRef mfrm As Form_clsTestForm1) Set frm = mfrm Call class_init End Property Private Sub class_init() 'Set control Form Control references 'to trap Events from Form Set btn1 = frm.Controls("cmdRaise") Set btn2 = frm.Controls("cmdClose") Set txt = frm.Controls("Text1") Set cbo = frm.Controls("cboWeek") Set lst = frm.Controls("lstMonth") End Sub Private Sub btn1_Click() Call MindGame 'this program is on Standard Module End Sub Private Sub btn2_Click() MsgBox "Form will be Closed now!", , "btn2_Click()" DoCmd.Close acForm, frm.Name End Sub Private Sub txt_LostFocus() Dim txtval As Variant txtval = Nz(txt.Value, 0) If txtval = 0 Then MsgBox "Enter some number in this field: " & txtval End If End Sub Private Sub frm_QtyLess(V As Long) MsgBox "Order Quantity cannot be less than 1" End Sub Private Sub frm_QtyMore(V As Long) MsgBox "Order Qty [ " & V & " ] exceeds Maximum Allowed Qty: 5" End Sub Private Sub cbo_Click() MsgBox "You have selected: " & UCase(cbo.Value) End Sub Private Sub frm_TbPage0(ByVal tbPage As String) MsgBox "TabCtl9 " & tbPage & " is Active." End Sub Private Sub frm_TbPage1(ByVal tbPage As String) MsgBox "TabCtl9 " & tbPage & " is Active." End Sub Private Sub lst_Click() Dim m As String, t As String Dim S As String m = lst.Value t = Day(Date) & "-" & m & "-" & Year(Date) S = Choose(Weekday(DateValue(t)), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") MsgBox "Date: " & t & vbCr & " Day: " & UCase(S) End Sub Private Sub Class_Terminate() Set frm = Nothing Set btn1 = Nothing

Set btn2 = Nothing Set txt = Nothing Set cbo = Nothing Set lst = Nothing End Sub

In the Class Module Global Declaration area the Form Object frm and other Controls on the Form are declared with the WithEvents Key word to capture the built-in Event when invoked from the Form clsTestForm1 control.  The Events can be either built-in or User-Defined.

In the Form_Load() Event Procedure the Form Object is passed to the class Module clsEvent1 Property Procedure frmMain(), as Parameter.

From within this Property Procedure the Class_Init() sub-routine is called to set the Form Controls references, with their Name Property Values, to the Control Objects declared with the key word WithEvents in the Global declaration area.

Note: For the User-Defined Events on the Form needs only the Form object frm in the Class Module clsEvent1 to capture the Events Raised on the Form. Hence, we have not declared the Tab Control Object in the declaration area with the Key word WithEvents.

Check the sub-routine names how they are declared.

In the Private Sub btn1_Click() sub-routine name btn1 object holds the reference to the Command Button Name cmdRaise and the Event Click when invoked on the clsTestForm1 Form it will be captured in the sub-routine and runs the Code within the procedure. The Event Procedure name have two parts, the object name (declared object name btn1 in Class Module) and Event Name(Click invoked on Form) both separated with an underscore character(_).

For all User-Defined Events, like QtyLess(), Raised on the Form needs only the frm Object with the WithEvents declaration.  The specific Form Module Name -  Form_clsTestForm1  required rather than the general type declaration Access.Form, to capture them in the Class Module clsEvent1, with the sub-routine name like frm_QtyLess().  If Property Procedure exists for the frm (if it is declared as Private) then the Property Procedure Parameter type also must be the specific Form’s Class Module Name Form_clsTestForm1.

Command Button cmdRaise have several built-in Events; Click, MouseMove, MouseDown, MouseUp etc. but here we will be capturing only the Click Event.

Same way btn2 (cmdClose) – Command Button2, cbo (cboWeek) - Combo Box, and lst (lstMonth) - List Box Click Event Procedures are captured in Class Module.

The TextBox Text1 have several built-in Events, like BeforeUpdate, AfterUpdate, LostFocus, GotFocus, but here we are capturing the LostFocus and two User-defined Events invoked from the AfterUpdate Event.

The Tab Control Page doesn’t fire the Click Event. That doesn’t mean that we cannot click on the Tab Page and use that event to run the required Code to do what we intend to do.

Remember the following two points if you are trying to write Code For Tab Page Click Event:

1.  Clicking on the Active Page (the page that is visible) doesn’t fire any Event.

2. Clicking on the inactive Page Changes that page active and the Change Event fires.

It simply means that instead of insisting on Click Event to fire use the TabCtl_Change() Event to write your Code.  You can read the TabCtl.Value to know the active Tab Page’s index number.  The TabCtl.Pages(Index).Name will give you the active page’s visible Name.

We have two User-Defined Events: TbPage0() and TbPage1() on the Form.  These will be invoked from within the TabControl Change Event on the Form.

Last Week I have promised to show you as how to eliminate the empty (Event Procedures without any executable code in it) procedures from the Form Module.  In the above Form also we kept those empty procedure to fire the built-in Event Procedures and capture them in Class Module.

We can do this by adding few lines of Code in the Class_Init() Sub-Routine.  That Sub-routine with the required changes is given below.  These lines will invoke the built-in Event Procedure from the Form.

Private Sub class_init()
  'Set control Form Control references
  'Set up Event Procedures to invoke

  Set btn1 = frm.Controls("cmdRaise")
      btn1.OnClick = "[Event Procedure]"
  Set btn2 = frm.Controls("cmdClose")
      btn2.OnClick = "[Event Procedure]"
  Set txt = frm.Controls("Text1")
      txt.OnLostFocus = "[Event Procedure]"
  Set cbo = frm.Controls("cboWeek")
      cbo.OnClick = "[Event Procedure]"
  Set lst = frm.Controls("lstMonth")
      lst.OnClick = "[Event Procedure]"
End Sub

You may download the Demo Database with all the objects from the link given at the end of this page.

A New Form, clsTestForm1_New and Class Module clsEvent1_New with changed Code is given in the Demo Database.

The changed VBA Code in Form’s Class Module is given below:

Option Compare Database
Option Explicit

Private myFrm As New ClsEvent1_New
Public Event QtyLess(X As Long)
Public Event QtyMore(X As Long)
Public Event TbPage0(ByVal pageName As String)
Public Event TbPage1(ByVal pageName As String)

'Keep the comment line within the Event Procedures
'Otherwise compiler will clear the Empty Event Procedures
Private Sub Form_Load()
   Set myFrm.frmMain = Me
End Sub

Private Sub TabCtl9_Change()
Dim strName As String

If TabCtl9.Value = 0 Then
   strName = TabCtl9.Pages(0).Name
   RaiseEvent TbPage0(strName)
Else
   strName = TabCtl9.Pages(1).Name
   RaiseEvent TbPage1(strName)
End If

End Sub

Private Sub Text1_AfterUpdate()
'Userdefined Events
Dim q As Long
  q = Nz(Me!Text1, 0)
  If q < 1 Then
     RaiseEvent QtyLess(q)
  End If
  If q > 5 Then
     RaiseEvent QtyMore(q)
  End If
End Sub

The changed Class Module (ClsEvent1_New) Code is given below:

Option Compare Database
Option Explicit

Private WithEvents frm As Form_clsTestForm1_New
Private WithEvents btn1 As commandbutton
Private WithEvents btn2 As commandbutton
Private WithEvents txt As TextBox
Private WithEvents cbo As ComboBox
Private WithEvents lst As ListBox

Public Property Get frmMain() As Form_clsTestForm1_New
    Set frmMain = frm
End Property

Public Property Set frmMain(ByRef mfrm As Form_clsTestForm1_New)
  Set frm = mfrm
  Call class_init
End Property

Private Sub class_init()

'Set control Form Control references
'to trap Events from Form
  Set btn1 = frm.Controls("cmdRaise")
      btn1.OnClick = "[Event Procedure]"
  Set btn2 = frm.Controls("cmdClose")
      btn2.OnClick = "[Event Procedure]"
  Set txt = frm.Controls("Text1")
      txt.OnLostFocus = "[Event Procedure]"
  Set cbo = frm.Controls("cboWeek")
      cbo.OnClick = "[Event Procedure]"
  Set lst = frm.Controls("lstMonth")
      lst.OnClick = "[Event Procedure]"
End Sub

Private Sub btn1_Click()
  Call MindGame 'this program is on Standard Module
End Sub

Private Sub btn2_Click()
    MsgBox "Form will be Closed now!", , "btn2_Click()"
    DoCmd.Close acForm, frm.Name
End Sub

Private Sub txt_LostFocus()
Dim txtval As Variant
txtval = Nz(txt.Value, 0)
If txtval = 0 Then
  MsgBox "Enter some number in this field: " & txtval
End If
End Sub

Private Sub frm_QtyLess(V As Long)
   MsgBox "Order Quantity cannot be less than 1"
End Sub

Private Sub frm_QtyMore(V As Long)
   MsgBox "Order Qty [ " & V & " ] exceeds Maximum Allowed Qty: 5"
End Sub

Private Sub cbo_Click()
   MsgBox "You have selected: " & UCase(cbo.Value)
End Sub

Private Sub frm_TbPage0(ByVal tbPage As String)
   MsgBox "TabCtl9 " & tbPage & " is Active."
End Sub

Private Sub frm_TbPage1(ByVal tbPage As String)
   MsgBox "TabCtl9 " & tbPage & " is Active."
End Sub

Private Sub lst_Click()
Dim m As String, t As String
Dim S As String

m = lst.Value
t = Day(Date) & "-" & m & "-" & Year(Date)
S = Choose(Weekday(DateValue(t)), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
MsgBox "Date: " & t & vbCr & "  Day: " & UCase(S)

End Sub

Private Sub Class_Terminate()
  Set frm = Nothing
  Set btn = Nothing
  Set txt = Nothing
  Set cbo = Nothing
  Set lst = Nothing
End Sub

Check the Code closely and look for statements that reads Form Control Values into the Class Module.

Download the sample demo database given below and try out the Forms and study the Code.

More exciting Events will take place next week.



  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
Share:

WithEvents and Defining your own Events

Hope that you have gone through last week’s WithEvents, Event, RaiseEvent introduction, tried out the sample Forms and understood as how both Form based VBA Code relates each other.  We will try out the same example differently here, with one Form and a Class Module and see what change required in Class Modules to capture Events from Form Module.

From here on we will be using Form and Class Module combination for capturing built-in Events from Form and execute required code in Class Module.

Our ultimate aim is to capture all the required built-in Events (frequently used) raised by all type of Objects (Command Button, Text Box, Combo Box, List Box or Option Group Buttons and several Objects of the same type) on the Form and write the required VBA Code in Class Module or Class Module Arrays or in Collection Object. It takes only few lines of Code on the Form Module to transfer the Form Object Reference to the Class Module and rest of the Code goes into the Class Module itself.

Between now and then it is a long way and I hope you will follow each week’s Posts and try out sample trial runs presented, to keep track of each weeks progressive changes on Code, and understand their relevance at each stage.

If you are not familiar with Class Modules then please go through the earlier articles starting from: Ms-Access Class Module and VBA.

So, last week’s example was for the demonstration of User-Defined Events and capturing it in the target module.  The RaiseEvent action was executed from within a built-in Event Procedure: Qty_AfterUpdate().

We will run last week’s example here one more time, with some change in the set up of related Objects.

Example2:  In this Demo run we will use one Form (Form_Form3Custom) and a Class Module: ClsCustomEvent. The Event will be invoked from the Form and will capture it in a Class Module and run the required Code from there.

The sample Form: Form3Custom image is given below:


Create a Form with the name Form3Custom with the following Controls, Copy and Paste the VBA Code given below into the Code Module of the Form.

The following Controls are there on the Form.

  • TextBox Name : Qty
  • Command Button Name : cmdClose
  • Label above the Text Box. – for information purpose only.

VBA Code behind Form3Custom is given below:

Option Compare Database
Option Explicit

Public ofrm As ClsCustomEvent

Public Event QtyLess(mQty As Single)
Public Event QtyMore(mQty As Single)
Public Event Closing()

Private Sub Form_Open(Cancel As Integer)
  Set ofrm = New ClsCustomEvent
  Set ofrm.mfrm = Me
End Sub

Private Sub Qty_AfterUpdate()
  If Qty < 1 Then RaiseEvent QtyLess(Qty)
  If Qty > 5 Then RaiseEvent QtyMore(Qty)
End Sub

Private Sub cmdClose_Click()
  RaiseEvent Closing
  DoCmd.Close
End Sub

Create a Class Module with the Name: ClsCustomEvent

Copy and Paste the VBA Code given below into the Class Module, Save and Compile the database to make sure that no Errors are encountered during compilation.

Option Compare Database
Option Explicit

Private WithEvents frm As Form_Form3Custom

Public Property Get mfrm() As Form_Form3Custom
  Set mfrm = frm
End Property

Public Property Set mfrm(ByRef obj As Form_Form3Custom)
  Set frm = obj
End Property

Private Sub frm_QtyLess(Q As Single)
Dim msg As String
    msg = "Order Quantity < 1 is Invalid. " & Q
    MsgBox msg, vbInformation, "ClsCustomEvent"
End Sub

Private Sub frm_QtyMore(ByRef Q As Single)
Dim msg As String
    msg = "Quantity: [ " & Q & " ] is above Order Limit 5."
    MsgBox msg, vbInformation, "ClsCustomEvent"
End Sub

Private Sub frm_Closing()
  MsgBox "Form will be Closed Now!"
End Sub
  1. Open Form3Custom in Normal View.
  2. Enter a value greater than 5 into the Text Box and press Tab Key.  If everything went on well you will see an error message.
  3. Try entering a negative value (say –2) into the Text Box and press Tab Key.  An error message will be displayed from Class Module.
  4. If you enter any value in the range 1 to 5 then no error message will appear.
  •  NoteTake a closer Look at the ClsCustomEvent VBA Code. 
  • In the Global declaration area the Form Object is declared as Private WithEvents frm as Form_Form3Custom (the Form’s specific Class Module Name) rather than normal declaration Private WithEvents frm as Access.Form
  • In the Property Get and Set Property Procedures are also uses the same Object Type declarations  as Form_Form3CustomThe specific Form Module name is used from where the Custom-made Events are invoked.
  • In the Custom Event Procdures QtyLess() and QtyMore() the parameter type declaration is ByRef and not ByVal.

When you try to do something of this kind in your own Project keep these points in mind, otherwise it will not work.

Now, we will try how to capture the built-in Events, like AfterUpdate, Clicks  from Form Controls into Class Module and run appropriate Code for Validation Checks or Calculations etc., instead of running them behind the Form’s Class Module itself.

For built-in Events on Form we don't have to define Event and RaiseEvent statements on Form.

But, the WithEvents definition is required in Class Module, where we capture the Events from Form Controls like Text Box, Command Button, Combobox, List Box etc.

With this background knowledge we will use a Form, like Form2 used earlier, with few changes and a Class Module to capture the built-in Events.

Image of the sample Form is given below:


A Text Box  and two Command Buttons are there on the Form.  The Command Button at the bottom of the Form is for closing the Form. The Label at the top is for information purpose only.

The Control names are given below:

  1. Text Box Name: Text1
  2. Command Button: cmdRaise
  3. Command Button 2: cmdClose
  4. Top Label for information only

The Value entered into the Text Box is checked for validity, on the AfterUpdate built-in Event,  and a message is displayed.  The acceptable valid value range is 1 to 5 in the Text Box.  Any value outside this range displays an error message.

The Command Button, immediately below text box, when clicked displays the current value in the Text Box.

The bottom Command Button click informs that the Form is getting closed.

These actions are not happening in the Code Module of the Form, but in the Class Module where it captures the actions and executes the Code in the class Module.  

The VBA Code behind the Form’s  (ClassTestForm) Module is given below.

Option Compare Database Option Explicit Dim m_obj As New ClsEventTest Private Sub Form_Load()

Set m_obj.mFrm = Me End Sub Private Sub Text1_AfterUpdate() 'comment End Sub Private Sub cmdRaise_Click() 'comment End Sub Private Sub cmdClose_Click() 'comment End Sub

The Dim statement, on the top, instantiates the Class Module Object ClsEventTest, with Object name m_obj.

Within the  Form_Load() Event Procedure the current Form Object is passed to the Class Module Object’s m_obj.mFrm Property.  In short the current Form Object is passed to the Class Module Object instance m_obj.

Next, Text1_AfterUpdate(), cmdRaise_Click() and cmdClose_Click() Event Procedures are  place holders only and there are no VBA executable Code in them.  A Comment line is inserted within the procedure to prevent the Compiler from eliminating the empty Event Procedures. 

These empty Event Procedures must be present (at least now) on the Form’s Module to trigger the Event and to capture the Event  in Class Module Object to run the Code there.

This is as good as our RaiseEvent action we have used on our User-Defined Event Procedure.

Likewise the built-in AfterUpdate Event of the Text Box (enter a value in the text box and press Tab Key to trigger this event) is captured in Class Module and VBA Code executed there.

The Text Box have other Events too, like BeforeUpdate, LostFocus, GotFocus and so on.  If these Events are also to be captured in the Class Module then those Empty Procedures are also must be enabled on the Form and write Code in the Class Module sub-routines to take care of these Events when triggered on the Form.

A question naturally pops up in one’s mind that if the empty Event Procedure lines are mandatory (at this stage:Yes) on the Form Module then why cann’t write the rest of the Code also there.  If such questions pops-up in your mind you are on the right track in learning this trick.  We will find a way to eliminate those empty procedures from the Form Module.

The Class Module: ClsEventTest  Code is given below:

Option Compare Database Option Explicit Private WithEvents frm As Access.Form Private WithEvents txt As TextBox Private WithEvents btn As CommandButton Private WithEvents btnClose As CommandButton Public Property Get mFrm() As Access.Form Set mFrm = frm End Property Public Property Set mFrm(ByRef vNewValue As Access.Form) Set frm = vNewValue Call class_init End Property Private Sub class_init() 'btn object in global declaration area 'is initialized with form Command Button cmdRaise Set btn = frm.Controls("cmdRaise") 'txt Object is initialized with Form Text1 TextBox Set txt = frm.Controls("Text1") 'like btn, btnClose Object is initialized Set btnClose = frm.Controls("cmdClose") End Sub ’Event Handling section Private Sub btn_Click() MsgBox "Current Value: " & Nz(txt.Value, 0), , "btn_Click()" End Sub Private Sub txt_AfterUpdate() Dim lngVal As Long, msg As String lngVal = Nz(txt.Value, 0) 'Text1 TextBox value msg = "Order Qty [ " & lngVal & " ] Valid." ‘default message 'perform validation check Select Case lngVal Case Is < 1 msg = "Quantity <1 is Invalid: " & lngVal Case Is > 5 msg = "Quantity [ “ & lngval & “ ] > Order Limit 5." End Select MsgBox msg, vbInformation, "txt_AfterUpdate()" End Sub Private Sub btnclose_Click() MsgBox "Form: " & frm.Name & " will be closed." DoCmd.Close acForm, frm.Name End Sub Private Sub class_terminate() Set txt = Nothing Set btnClose = Nothing Set btn = Nothing Set frm = Nothing End Sub

Let us see what we have in the above Class Module.

In the Global declaration Area of the Module four Object variables are declared with the WithEvents statement. 

First line, a Form Object with the name frm.  This Object will be assigned with the Form Object ClassTestForm (or any other Form that uses this Class Module), on the Form_Load() Event of the Form.

One TextBox and two Command Button Controls are declared, with the WithEvents statement, in the Global Area of the Class Module .

These controls will be set with the references of the Text Box and Command Button names on the Form.

The  Public Property Set mFrm() Procedure accepts the Form Object, assigned from the  Form Module.

The Class_Init() (this is not Class_Initialize()) sub-routine is called from the Set Procedure and initializes the Text Box and Command Button Controls with these objects on the Form.

For example: the statement Set btn = frm.Controls("cmdRaise") sets a reference to the Command Button Control name ‘cmdRaise’ on the Form object frm.

Rest of the statements in this init() procedure also sets a reference to the other controls (WithEvents Text1, btnClose) on the Form too.

Try out the above Form and Class Module.

Next week we will see how to remove the Empty Event Procedures from the Form’s Code Module.


  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing

Share:

WithEvents Ms-Access Class Module Tutorial

There are Class Modules on Forms and Reports.  Almost all Controls on Form, like Command Buttons, Text Boxes, Combo Boxes, List Boxes and others  have Event s Coding features that makes Ms-Access a powerful Database Management System.

Text Box have GotFocus, LostFocus, BeforeUpdate, AfterUpdate  and other Events and we write VBA Code in them to do various tasks, like data entry control, validation checks or calculations on entered data and so on. 

The OnClick Event Procedure of Command Button launches Forms or Reports or Run programs or Macros to do various tasks.  In all these predefined Events we write code directly on the Form’s or Report’s Class Module.

But, we are going to do it differently this time by redirecting the Event handling somewhere else other than the Event invoking Class Module.

As far as Ms-Access is concerned we can divide this procedures into two sections:

  1. Built-in Events invoked from Controls on Form/Report Class Module can be captured in other Class Module  and execute Code there, to do whatever you would like to do on that Event, instead of writing Code directly on the source Form/Report Module.
  2. Besides that, we can define our own Custom Events on Form/Report Modules and capture the event on other Form’s Class Module or in a Class Module Object.  Write required code on the target module to handle whatever action needed on the Form. This will need only a  line of code in the built-in Event Procedure, to transmit the Event to the target location, where we can write code for action.

First we will start, with the second option, defining Custom Events and will learn as how to invoke a Custom Event from one Form and capture it on another Form or in a Class Module Object, as it happens and run the required task there. 

Why we take the Custom Events first, because it uses all the fundamental elements of this  powerful programming feature.  It uses few basic statements (given below) and their placement on different Modules and the correct naming of Events on the Source and Target Modules are very important.

We will be trying out a simple example, but it is very important that you understand the key elements placement of Custom Event and how all of them are synchronized to work together.

--- form1 ---

Private WithEvents obj as Form_Form2

Private Sub obj_eventName(parameter) ‘Capture the Event coming from obj

‘write Code here

End Sub

--- Form2 ---

Public Event eventName(parameter) ‘Declare Event

RaiseEvent eventName(parameter) ‘Invoke the Event

Capturing the built-in Events, like Button Clicks, Combo Box Clicks etc in Class Module object is much more simpler than to define Custom Event on one Class Module and capture it from another Class Module.

Events and Event-trapping is strictly a business involving Class Modules only, cannot be done on Standard Modules.  But, you can call Sub-Routines/Functions from Standard Module from Class Module, if needed.

Let us try an example with two simple Forms, with a Text Box and a Command Button.  With this trial run I am sure that you will know the basics of this procedure. The sample design of Form1 is given below:

  1. Create a new Form with the name Form1 and open it in Design View.
  2. Insert a Command Button on the Detail Section of the Form.
  3. Display the Property Sheet (F4) and change the Name Property Value to cmdOpen.
  4. Change the Caption Value to Open Form2.
  5. Insert a Label Control above the Command Button and change the Name Property Value to Label1 and the Caption Value also Label1.
  6. Change the Form1’s Has Module Property value to Yes.
  7. Display the Module of Form1.
  8. Copy and paste the following Code into the Class Module of the Form and save the Form.
    Option Compare Database
    Option Explicit
    
    Private WithEvents frm As Form_Form2
    
    Private Sub cmdOpen_Click()
    Set frm = Form_Form2
    frm.Visible = True
    
    End Sub
    
    Private Sub frm_QtyUpdate(sQty As Single)
    Dim Msg As String
    
      If sQty < 1 Then
         Msg = "Negative Quantity " & sQty & " Invalid."
      ElseIf sQty > 5 Then
         Msg = "Invalid Order Quantity: " & sQty
      Else
         Msg = "Order Quantity: " & sQty & " Approved."
      End If
      MsgBox Msg, vbInformation, "frm_QtyUpdate()"
      
    End Sub
    
    Private Sub frm_formClose(txt As String)
       MsgBox "Form2 Closed", vbInformation, "farewell()"
       Me.Label1.Caption = txt
    End Sub
    
  9. Create a second Form with the name Form2 and open it in Design View.
  10. Sample image of Form2 is given below:

  11. Create a Text Box on the Form and change it’s name property value to Qty.
  12. Change the child-label Caption to Order Qty (1 – 5):
  13. Create a Command Button below the Text Box and change the Name Property value to cmdClose and change the Caption Property value to Close.
  14. Display the Form’s VBA Module, Copy and Paste the following Code into the Module and save the Form.
Option Compare Database
Option Explicit

Public Event QtyUpdate(mQty As Single)
Public Event formClose(txt As String)

Private Sub Qty_AfterUpdate()
  RaiseEvent QtyUpdate(Me!Qty)
End Sub

Private Sub cmdClose_Click()
  DoCmd.Close
End Sub

Private Sub Form_Unload(Cancel As Integer)
  RaiseEvent formClose("Form2 Closed")
End Sub

 

Let us take a closer look at the Form1’s Class Module Code. 

  • The first line: Private WithEvents frm Form_Form2 declares a Form Object Variable frm of  Form2, enabled with WithEvents trapping feature.  Events originating from Form2 Class Module is captured here and the corresponding Subroutine is executed, depending on the Event Raised.
  • On the cmdOpen_Click event procedure the Form2 object is instantiated in frm Object Variable and made Form2 visible on the Database Window.
  • The Private Sub frm_QtyUpdate() Subroutine is executed when the Qty Text Box on Form2 is updated with a value, and from within the the AfterUpdate Event of the Text Box the RaiseEvent QtyUpdate() is executed.
  • The Private Sub frm_formClose() is executed when the Command Button on Form2 is Clicked to close Form2.

Now let us go through the Form2 Module’s Code.

  • In the global declaration area of the Module two Public Event Procedure names are declared with parameters.
  • When some value is entered into the Text Box and the Tab Key is pressed, the AfterUpdate Event Procedure is run and the QtyUpdate()  Event is Raised with the Statement RaiseEvent QtyUpdate(Me!Qty).  The Qty Text Box value is passed as parameter.
  • The frm_QtyUpdate() Sub-routine runs, from Form1 Class Module and performs validation checks on the passed value and displays an appropriate message.
  • When the Command Button, with the Caption Close, is clicked the formclose() Event is Raised and a message displayed, and the Label control on Form1 is updated with the same info.

What happens on form2 and Form1, the chain of action path is depicted in a diagram given below. You may use it as a guide when you try out something on your own ideas.


It works like a Radio Transmitter and Receiver tuning like setup,.

The global declaration Public WithEvents frm as Form_Form2 on Form1’s VBA Module is like a Radio Receiver, states that whatever action transmitted from Form2 (from instance frm Variable) will be received in Form1 and executed. 

In Form2’s Module at the global declaration section you will find the statement Public Event QtyUpdate(mQty As Single) , you may compare this statement with the transmission Frequency (or Event Name: QtyUpdate()) with data, from Text Box as parameter. 

The transmission takes place only when you call the RaiseEvent Statement and it fires the Event declared at the Module level of Class or Form or Report, with parameter value (if defined), like RaiseEvent QtyUpdate(Me!Qty)

The same name QtyUpdate() is a Subroutine Name – not to declare as Function  - (on Form1 Class Module, like we tune in to the same transmission frequency to receive the radio broadcast) where we write code to run validation check on the passed data as Parameter and displays a message based on the validity of value passed from Qty textbox on Form2.

The Sub-routine name is always prefixed with Form2’s Class Module instance variable name frm_  and the sub-routine header line is written as Private Sub frm_QtyUpdate(sQty As Single), to tune into the correct frequency of transmission.

It is very important that the Sub-Routine Name on Event declaration on Form2 match with Sub-Routine Name on Form1.  The Sub-routine name on Form1 will be prefixed with the Form2’s instance Variable and an underscore frm_.

Note: It means that the actual Code, for the Event Procedure declaration done on Form2 Class Module, is written on Form1 Module, by addressing the subroutine directly with object name (frm_) prefix.

Armed with the above background information let us try out the Forms to see how it works.

  1. Open Form1.
  2. Click on the Command Button.  Form2 is instantiated in memory and made it visible.
  3. If Form2 is overlapping Form1 then drag it to the right side so that both Forms remain side by side.

    On Form2 there is a text box with the name Qty (Quantity), the valid value range acceptable in the Text Box is 1 to 5. 

    Any value outside this range is invalid and an appropriate error message is displayed.

  4. Enter a value in the text box and press Tab Key. 

    The Text Box’s  AfterUpdate Event is run and within this Event the RaiseEvent QtyUpdate(Me!Qty) statement fires the Custom Event  and passes the Text Box Value as parameter.

    Public Sub frm_QtyUpdate(sQty as single) Subroutine on Form1 Class Module runs and validates the parameter value and displays an appropriate message.

    You may try out this by entering different values into the Text Box and by pressing Tab Key.

  5. When you are ready to close Form2 click on the Command Button.
The formClose() Event is fired, a message is displayed. The Label control Caption on Form1 is updated with the same message stating that Form2 is closed.

I am sure you understood as how the whole thing works and try something similar on your own way.  When on doubt use this as a reference point.

More on this next week.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing

Share:

Update Class Object Dictionary Item through Form

Last week we have done a trial run for adding/retrieving Class Objects into/from Dictionary Object Items.  One important point we have highlighted there was that each Class Object Item added to the Dictionary Object must be a new instance of the Class Object, every time.  

This is not peculiar to Class Object or Dictionary Object alone.  This  method must be followed to avoid inadvertent loss of important data, while adding Objects to Collection, Dictionary or to the Array of an Object itself.  As a review of the procedure in brief is given below:

  1. Create a temporary Object instance (Set tmpObj = New Object)
  2. Assign Values to the temporary Object instance’s properties.
  3. Add the temporary Object instance as Dictionary Object Item or Collection Object Item or as the Object’s declared Array element.
  4. Remove the temporary Object instance (Set tmpObj = Nothing) from memory.
  5. To add more items repeat steps 1 to 4.

You would like to know why we can’t use only one instance of the Class Object to add several Items to the Dictionary Object, visit the page Add Class Object as Dictionary Object Items?

Now, let us get ourselves prepared for a trial run to Add, Edit, Update, Delete Class Object Items in Dictionary Object, through Ms-Access Form.  We have worked with Form and Dictionary Object about two weeks back , for adding and retrieving simple items in Dictionary Object. 

This time we are dealing with Objects and Properties in Dictionary Objects, not simple Items, with added data management functions.  Besides that when the Form is closed the Dictionary Item Values (ClsArea Class Object Property Values) are saved to a Table.

We will use our simple ClsArea Class Object, with only three Property Values to fill in (Description, Length and Width Values) , for our trial run.

Even though the Table is used only at the end of a session we will design the simple table first with the fields: Description (strDesc), Length (dblLength) and Width (dblWidth) matching the Class Module ClsArea Object Property Names in brackets.

  1. Create a new Table with the name ClsArea with the Field Names and Field Types shown in the Table Design Image shown below. For Numeric Data Fields select Double Precision Number Type.
  2. Next we need a Form with the following design to manage the data of Dictionary Object.
  3. Design a Form with the following Controls (not bound to the Table.):
    • Combobox on the Header:

      Name Property Value: cboKey

      Row Source Type = Value List

    • Four Unbound Text Boxes in the Detail Section of the Form with the following Names:

      strDesc

      dblLength

      dblWidth

      Area

    • Create another text Box to the right of the last Text Box with the Name:

      ItemCount

    • Create three Command Buttons below the Text Boxes, on the Detail Section, with the Names:

      cmdAdd and Caption: Add

      cmdEdit and Caption: Edit

      cmdDelete and Caption: Delete

    • Create two Command Buttons in the Footer Section of the Form with the Names:

      cmdSave and Caption: Save to Table

      cmdExit and Caption: Exit

  4. The Form’s Normal View Image, with some sample data, is given below:

As the Form control names suggest we can key in Property Values of ClsArea Class Object and add the Class Object to Dictionary Object, as it’s Item.  the strDesc Property Value will be used as Dictionary Key.  Ensure that while Keying in data the Description Field Value must be unique among all items, otherwise it will not accept in Dictionary Object and will end up with Error.  I have avoided extensive validation checks in the Code to maintain the code as simple as possible.

A particular Class Object’s property values can be retrieved from Dictionary Object, using the Key Value selected from the cboKey Combo Box, edit the values on the Form and Update the changes back into the Dictionary. If it is an unwanted item then it can be deleted from Dictionary Object.

There are several Event Procedures behind the Form’s Class Module and a brief explanation is given under each Procedure given below. 

Option Compare Database
Option Explicit

Dim C As ClsArea, xC As ClsArea
Dim D As Object, Desc As String
Dim editFlag As Boolean, saveFlag As Boolean
Dim strV As String

All important Objects and Variables are declared at the Global declaration area.

Private Sub Form_Load()

Private Sub Form_Load()
    Set D = CreateObject(“Scripting.Dictionary)
    Me.cmdEdit.Enabled = False
    Me.cmdDelete.Enabled = False
    saveFlag = False
End Sub

At the Form Load Event the Dictionary Object is instantiated.

Edit and Delete Command Buttons are disabled because the Form will be in data entry mode by default.  Data save detection flag saveFlag is set to false. The flag will be set to True when the Dictionary Data is saved to Table, from one of two possible selection of Command Buttons at the Footer of the Form.

Private Sub Form_Current()

Private Sub Form_Current()
Dim o As String
    o = cboKeys.RowSource
    If Len(o) = 0 Then
       Me.cboKeys.Enabled = False
    Else
       Me.cboKeys.Enabled = True
    End If
End Sub

Checks the Combo box's RowSource have any Values (when the Form is open it will be empty) in it, if not disable Combo box Control.

Private Sub strDesc_GotFocus()
Private Sub strDesc_GotFocus()
 If editFlag Then
  Me.cmdAdd.Enabled = False
  Me.cmdEdit.Enabled = True
  Me.cmdDelete.Enabled = True
 Else
  Call initFields
  Me.cmdAdd.Enabled = True
  Me.cmdEdit.Enabled = False
  Me.cmdDelete.Enabled = False
  editFlag = False
 End If

End Sub

Checks whether the form is in Edit mode or not.  Edit Mode Flag is set when the User clicks the Edit Command Button.  This happens after the user selects an item from the Combobox.

If in edit mode then the data entry fields are not cleared, Edit and Delete Command Buttons are enabled, Add Command Button is disabled.

If in Add mode then data fields are emptied in preparation for new Item, Add Command Button is enabled, Edit and Delete Command Buttons are disabled..

Private Sub dblWidth_LostFocus()

Private Sub dblWidth_LostFocus()
'Area is displayed for info purpose only
  Me!Area = Nz(Me!dblLength, 0) * Nz(Me!dblWidth, 0)
End Sub

On the dblWidth Text Box's LostFocus Event the product of dblLength * dblwidth is displayed in the Area Text Box.

Private Sub cmdAdd_Click()

Private Sub cmdAdd_Click()
'--------------------------------------------------
'1. Add Class Object as Item to Dictionary Object
'2. Update Combobox RowSource Property
'--------------------------------------------------
Dim tmpstrC As String, tmpLength As Double, tmpWidth As Double
Dim flag As Integer, msg As String
Dim cboVal As String, cbo As ComboBox

editFlag = False
Set cbo = Me.cboKeys

tmpstrC = Nz(Me!strDesc, "")
tmpLength = Nz(Me!dblLength, 0)
tmpWidth = Nz(Me!dblWidth, 0)

flag = 0
If Len(tmpstrC) = 0 Then flag = flag + 1
If tmpLength = 0 Then flag = flag + 1
If tmpWidth = 0 Then flag = flag + 1
If flag > 0 Then
   msg = "Invalid Data in one or more fields, correct them and retry."
   MsgBox msg, , "cmdAdd()"
   Exit Sub
End If

Desc = ""
Set C = New ClsArea 'create a new instance
'add Property Values
    C.strDesc = tmpstrC
    C.dblLength = tmpLength
    C.dblWidth = tmpWidth
    
'add Class Object instance to Dictionary
    
    D.Add tmpstrC, C 'Description is the Key
    
    Call comboUpdate(tmpstrC) 'update description as combobox item
       
    Me.ItemCount = D.Count 'display dictionary Items count
    
    'Call initFields 'set all fields to blanks
    Me.strDesc.SetFocus 'make description field current
    
'Clear Class Object
    Set C = Nothing

End Sub


Text Box values are transferred to temporary variables and checks whether any text box is empty.

ClsArea Class Object Properties are assigned with strDesc, dblLength & dblwidth text box values, from temporary variables.

Adds Class Object to Dictionary Object.

Updates Combo Box with the Dictionary Object Key, from Class Object's Description (strDesc) Value.

The Dictionary Items Count is displayed on ItemCount Text Box.

The focus is set in the strDesc field, by which the text boxes are cleared to enter new values to add.

Private Sub cboKeys_AfterUpdate()

Private Sub cboKeys_AfterUpdate()
On Error Resume Next

strV = Me!cboKeys
Set xC = D(strV)
If Err > 0 Then
   MsgBox "Item for Key: " & strV & " Not Found!"
   Exit Sub
End If

Me!strDesc = xC.strDesc
Me!dblLength = xC.dblLength
Me!dblWidth = xC.dblWidth
Me!Area = xC.Area

Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True
Me.strDesc.Enabled = False

On Error GoTo 0

End Sub

After adding several items to the Dictionary Object you may retrieve an Item from Dictionary Object and display the details on Form, by selecting the Dictionary Key Item from the Combo Box. The Add Command Button will be disabled. The strDesc  Field will be disabled. Will unlock it only if you click on Edit Button.

If you select an invalid Key (Key changed during earlier edit operations) from Combobox then an error message: 'Item for Key: XXXX.. Not Found' will be displayed, indicating that the Item doesn't exist in Dictionary Object.

Private Sub cmdEdit_Click()

Private Sub cmdEdit_Click()
'Edit the displayed item properties
Dim cap As String
Dim mDesc As String

editFlag = True

strV = Me!cboKeys

cap = Me.cmdEdit.Caption
Select Case cap
   Case "Edit"
      Me.strDesc.Enabled = True
      Me.cmdAdd.Enabled = False 'when editing Add button is disabled
      Me.cmdEdit.Caption = "Update" 'Edit Button Caption is changed
      
   Case "Update" 'Button clicked when Caption is Update
   'directly replace the property value in the Item
      xC.strDesc = Me!strDesc
      xC.dblLength = Me!dblLength
      xC.dblWidth = Me!dblWidth
      mDesc = Me!strDesc 'changed Description is copied to mDesc
      
   If mDesc <> strV Then 'checks with key in combobox value if not same then
      D.Key(strV) = mDesc 'Change Dictionary Key of Item
      
      'update new desc to Combobox
      'old desc also remains in combobox
      Call comboUpdate(mDesc)
   End If
      Call initFields
      Me.strDesc.SetFocus
      Me.cmdAdd.Enabled = True 'Enable Add button to add new item
      Me.cmdEdit.Caption = "Edit" 'change caption from Update to Edit
      Me.cmdEdit.Enabled = False 'disable Edit Button
      Me.cmdDelete.Enabled = False 'disable Edit Button
End Select

End Sub


The item brought from Dictionary to Form can be edited by clicking on the Edit Command Button. The Caption of the Edit Command Button will change to Update, when you click on it.

Make any changes, including the Description (if necessary) then Click Edit Button again (with Caption Update) the second time to update the change back into Dictionary Object.

If strDesc (Key value of Dictionary) is changed the Dictionary Object Item's corresponding old Key is updated with the new Description Value. The Combobox will be updated with the new Key,  but, old Key is not deleted from combo box.

Data fields are cleared, Add Command Button is enabled, Edit and Delete Buttons are disabled.

Private Sub cmdDelete_Click()

Private Sub cmdDelete_Click()
Dim txtKey As String, msg As String

txtKey = Me!cboKeys
msg = "Delete Object with Key: " & txtKey & " ..?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbCritical) = vbYes Then
   D.Remove (txtKey) 'Remove the Item matching the Key
   
   MsgBox "Item Deleted."
   
   Call initFields
   
   Me.strDesc.Enabled = True
   Me.strDesc.SetFocus 'select description field current
   Me.ItemCount = D.Count 'display items count
   Me.cmdAdd.Enabled = True
   Me.cmdEdit.Enabled = False
   Me.cmdDelete.Enabled = False
   
End If

End Sub

When Delete Command Button is clicked, the current Item on the Form is deleted from Dictionary, after the user reconfirms it.

The data entry fields are cleared, the item count control is updated with the reduced number of items.

Private Sub cmdSave_Click()

Private Sub cmdSave_Click()
Dim msg As String

msg = "Form will be closed After Saving Data,"
msg = msg & vbCr & "Proceed...?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbInformation, "cmdSave_Click()") = vbNo Then
  Exit Sub
Else
  Call Save2Table
  DoCmd.Close
End If

End Sub
 

When you have finished working with the Form and want to Save the Dictionary Data ClsArea Class’s property values from Dictionary to the temporary Table (clsArea) and to Close the Form, click on the 'Save to Table' Command Button.

Private Sub cmdExit_Click()

Private Sub cmdExit_Click()
Dim msg As String
If saveFlag Then
    DoCmd.Close
Else
    msg = "Dictionary Data Not saved in Table!"
    msg = msg & vbCr & vbCr & "Click Cancel to Go back"
    msg = msg & vbCr & vbCr & "Click OK to discard Data and close the Form!"
    If MsgBox(msg, vbOKCancel + vbDefaultButton2 + vbQuestion, "cmdExit()") = vbOK Then
        DoCmd.Close
    Else
        Call Save2Table
        DoCmd.Close
    End If
End If
End Sub

If you choose to click on the Exit Command Button, rather than the ‘Save to Table’ Command Button, here also you are reminded to Save the data. If you don't want to save then you may select the appropriate option to close the Form, without saving the data in Table..

There are few common Sub-Routines,  called from more than one event Procedures and their Code is added to the Full listing of the Code given below.

Private Sub initFields() all text boxes on the Form is cleared when called from Event Procedures.

Private Sub comboUpdate(ByVal stDesc As String) Called from cmdAdd_Click() and cmdEdit_Click() Event Procedures.

Private Sub Save2Table() Called from cmdSave_Click() and cmdExit_Click() Event Procedures.

Highlight, Copy and Paste the Entire Code, given below, into the Form's Class Module of your Form. Save the Form with the name frmDictionary.

Option Compare Database
Option Explicit

Dim C As ClsArea, xC As ClsArea
Dim D As Object, Desc As String
Dim editFlag As Boolean, saveFlag As Boolean
Dim strV As String

Private Sub Form_Load()
    Set D = CreateObject(“Scripting.Dictionary”)
    Me.cmdEdit.Enabled = False
    Me.cmdDelete.Enabled = False
    saveFlag = False
End Sub

Private Sub Form_Current()
Dim o As String
o = cboKeys.RowSource
If Len(o) = 0 Then
  Me.cboKeys.Enabled = False
Else
  Me.cboKeys.Enabled = True
End If
End Sub


Private Sub cboKeys_AfterUpdate()
On Error Resume Next

strV = Me!cboKeys
Set xC = D(strV)
If Err > 0 Then
   MsgBox "Item for Key: " & strV & " Not Found!"
   Exit Sub
End If

Me!strDesc = xC.strDesc
Me!dblLength = xC.dblLength
Me!dblWidth = xC.dblWidth
Me!Area = xC.Area

Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True
Me.strDesc.Enabled = False

On Error GoTo 0

End Sub

Private Sub cmdAdd_Click()
'--------------------------------------------------
'1. Add Class Object as Item to Dictionary Object
'2. Update Combobox RowSource Property
'--------------------------------------------------
Dim tmpstrC As String, tmpLength As Double, tmpWidth As Double
Dim flag As Integer, msg As String
Dim cboVal As String, cbo As ComboBox

editFlag = False
Set cbo = Me.cboKeys

tmpstrC = Nz(Me!strDesc, "")
tmpLength = Nz(Me!dblLength, 0)
tmpWidth = Nz(Me!dblWidth, 0)

flag = 0
If Len(tmpstrC) = 0 Then flag = flag + 1
If tmpLength = 0 Then flag = flag + 1
If tmpWidth = 0 Then flag = flag + 1
If flag > 0 Then
   msg = "Invalid Data in one or more fields, correct them and retry."
   MsgBox msg, , "cmdAdd()"
   Exit Sub
End If

Desc = ""
Set C = New ClsArea 'create a new instance
'add Property Values
    C.strDesc = tmpstrC
    C.dblLength = tmpLength
    C.dblWidth = tmpWidth
    
'add Class Object instance to Dictionary
    
    D.Add tmpstrC, C 'Description is the Key
    
    Call comboUpdate(tmpstrC) 'update description as combobox item
       
    Me.ItemCount = D.Count 'display dictionary Items count
    
    'Call initFields 'set all fields to blanks
    Me.strDesc.SetFocus 'make description field current
    
'Clear Class Object
    Set C = Nothing

End Sub

Private Sub cmdDelete_Click()
Dim txtKey As String, msg As String

txtKey = Me!cboKeys
msg = "Delete Object with Key: " & txtKey & " ..?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbCritical) = vbYes Then
   D.Remove (txtKey) 'Remove the Item matching the Key
   
   MsgBox "Item Deleted."
   
   Call initFields
   
   Me.strDesc.Enabled = True
   Me.strDesc.SetFocus 'select description field current
   Me.ItemCount = D.Count 'display items count
   Me.cmdAdd.Enabled = True
   Me.cmdEdit.Enabled = False
   Me.cmdDelete.Enabled = False
   
End If

End Sub

Private Sub cmdEdit_Click()
'Edit the displayed item properties
Dim cap As String
Dim mDesc As String

editFlag = True

strV = Me!cboKeys

cap = Me.cmdEdit.Caption
Select Case cap
   Case "Edit"
      Me.strDesc.Enabled = True
      Me.cmdAdd.Enabled = False 'when editing Add button is disabled
      Me.cmdEdit.Caption = "Update" 'Edit Button Caption is changed
      
   Case "Update" 'Button clicked when Caption is Update
   'directly replace the property value in the Item
      xC.strDesc = Me!strDesc
      xC.dblLength = Me!dblLength
      xC.dblWidth = Me!dblWidth
      mDesc = Me!strDesc 'changed Description is copied to mDesc
      
   If mDesc <> strV Then 'checks with key in combobox value if not same then
      D.Key(strV) = mDesc 'Change Dictionary Key of Item
      
      'update new desc to Combobox
      'old desc also remains in combobox
      Call comboUpdate(mDesc)
   End If
      Call initFields
      Me.strDesc.SetFocus
      Me.cmdAdd.Enabled = True 'Enable Add button to add new item
      Me.cmdEdit.Caption = "Edit" 'change caption from Update to Edit
      Me.cmdEdit.Enabled = False 'disable Edit Button
      Me.cmdDelete.Enabled = False 'disable Edit Button
End Select

End Sub

Private Sub cmdExit_Click()
Dim msg As String
If saveFlag Then
    DoCmd.Close
Else
    msg = "Dictionary Data Not saved in Table!"
    msg = msg & vbCr & vbCr & "Click Cancel to Go back"
    msg = msg & vbCr & vbCr & "Click OK to discard Data and close the Form!"
    If MsgBox(msg, vbOKCancel + vbDefaultButton2 + vbQuestion, "cmdExit()") = vbOK Then
        DoCmd.Close
    Else
        Call Save2Table
        DoCmd.Close
    End If
End If
End Sub

Private Sub cmdSave_Click()
Dim msg As String

msg = "Form will be closed After Saving Data,"
msg = msg & vbCr & "Proceed...?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbInformation, "cmdSave_Click()") = vbNo Then
  Exit Sub
Else
  Call Save2Table
  DoCmd.Close
End If

End Sub

Private Sub Save2Table()
Dim db As Database, rst As Recordset
Dim recCount As Long, j As Long, item

On Error GoTo Save2Table_Error

recCount = D.Count

Set db = CurrentDb
Set rst = db.OpenRecordset("ClsArea", dbOpenTable)
For Each item In D.Items
   With rst
        .AddNew
         !strDesc = item.strDesc
         !dblLength = item.dblLength
         !dblWidth = item.dblWidth
        .Update
    End With
Next

rst.Close
Set rst = Nothing
Set db = Nothing

saveFlag = True
MsgBox "Data Saved  to Table: ClsArea"

Save2Table_Exit:
Exit Sub

Save2Table_Error:
MsgBox Err & ":" & Err.Description, , "Save2Table_Click()"
Resume Save2Table_Exit

End Sub

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

Private Sub strDesc_GotFocus()
    If editFlag Then
        Me.cmdAdd.Enabled = False
        Me.cmdEdit.Enabled = True
        Me.cmdDelete.Enabled = True
    Else
        Call initFields
        Me.cmdAdd.Enabled = True
        Me.cmdEdit.Enabled = False
        Me.cmdDelete.Enabled = False
        editFlag = False
    End If

End Sub


Private Sub dblWidth_LostFocus()
'Area is displayed for info purpose only
  Me!Area = Nz(Me!dblLength, 0) * Nz(Me!dblWidth, 0)
End Sub

Private Sub initFields()
'Empty all fields
    Me!strDesc = Null
    Me!dblLength = Null
    Me!dblWidth = Null
    Me!Area = Null
    Me.cmdAdd.Enabled = True
End Sub

Private Sub comboUpdate(ByVal stDesc As String)
Dim cbo As ComboBox, cboVal As String
Set cbo = Me.cboKeys
    cboVal = cbo.RowSource
    cboVal = cboVal & ";" & stDesc
    cbo.RowSource = cboVal
    cbo.Requery
    If Len(cboVal) > 0 Then
      Me.cboKeys.Enabled = True
    End If
End Sub


  1. Open the Form frmDictionary and try out adding few entries into the Dictionary Object. 
  2. Select an item from the Combo Box to display the Property Values of the corresponding Class Object.
  3. Click on Edit Command Button, make some change to the Description, Length and Width Values.
  4. Click on Edit Command Button again (with caption Update) to write the changes back into the Dictionary Object.  If you click on the Combobox you can see the item with the changed description and the old description of the item.
  5. Select the old description from the Combo Box. You will get an error message saying that the Item doesn’t exist, because the Dictionary Key of the Item is updated with changed value.
  6. Select an item from the Combo Box and Click on the Delete Command Button to delete the item displayed on the Screen.

Tried to keep the Event Procedure Code simple, avoiding Validation checks on input values,  error trap routines or to safeguard other  side effects of user activities on the Form.

You may improve the Code, if need arises and use it for your own Projects.

You may download this demo database from the link given below.

Download Dictionary_2003.zip
Download Dictionary2007.zip

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form
  11. Dictionary Object Basics
  12. Dictionary Object Basics-2
  13. Sorting Dictionary Object Keys and Items
  14. Display Records from Dictionary to Form
  15. Add Class Objects as Dictionary Items
  16. Update Class Object Dictionary Item on Form
Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

Access Form Control Arrays and Event-3

This is the continuation of last Week’s Topic on Text Box AfterUpdate and LostFocus Event capturing and performing Validation Checks on val...

Labels

Blog Archive

Recent Posts