Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

WithEvents in Class Module for Sub-Form Text Box Events

Introduction.

So far we have used only a single Form and Text Box Controls on it to demonstrate the usage of WithEvents and built-in Event capturing, running subroutines in Class Object Arrays or in Collection Object items.  But, how do we write code for Sub-Form(s) based Text Box controls, of the Main Form, to invoke built-in Events and to capture them in Class Module Objects.

The sample form image with two Sub-Forms and few Text Boxes on all the Forms are given below for reference.

We will be using the familiar AfterUpdate or  OnGotFocus or OnLostFocus Events on Text Boxes on Main Form and Sub-Forms.  But let us concentrate on one or two points we have not yet dealt with so far.

  1. Introduction of the Access.Form Property in the Class Module of Text Box Control.
  2. How to address the Text Box Controls on the Sub-Form, to invoke the built-in Events and capture them in the Class Module?
  3. How to move the insertion point from the last Text Box on the first Sub-Form to the first Text Box on the second sub-form?
  4. How to move the insertion point from the last Text Box on the second sub-form to the only text box on the Main Form?
  5. How to use the reference of other controls to read/write on the Form control, from within the current Event Procedure in the Class Module?

Why Form Property Required in Class Module.

So far we have worked with a single Text Box Property in the Class Module .  The Event Procedures are also mostly deals with the Text Box Name and it’s Value alone. While working with the value of the current text box, that triggered the Event, there are situations where we need the value from other Text Box controls on the Form.

For example: We update the employee’s date of birth on one text box, we need to find  the employee’s retirement date, at the age of 56 years and update it in another Text Box.

Option 1: At the after-update event procedure of the date-of-birth text box, calculate the retirement date and update it into the retirement date text box directly.

Option 2: After updating the date-of-birth Text Box the cursor jumps to the Retirement Date control.  On the OnGotFocus Event on this text box read the date-of-birth value from the Birth-Date text box, calculate the retirement date and insert it into the current text box.

In either case you have to work with two text boxes to read or write value into it,  To read/write value into the other text box we can reach it only through the Form Object. To do that we need the Access.Form Object as Property in the Class Module, besides the Access.TextBox control Object.

The new Class Module (ClsSubForm) Code is given below:

Option Compare Database
Option Explicit

Public WithEvents txt1 As Access.TextBox
Private frm2 As Access.Form

Public Property Get sFrm() As Access.Form
  Set sFrm = frm2
End Property

Public Property Set sFrm(ByRef frmValue As Access.Form)
  Set frm2 = frmValue
End Property

Private Sub txt1_AfterUpdate()
Dim ctl As Control, ctlName As String
Dim bd As Date

ctlName = txt1.Name
Select Case ctlName
    Case "EmpName"
      If Len(Nz(txt1.Value, "")) > 10 Then
         txt1.Value = Left(txt1.Value, 10)
         MsgBox "Maximum 10 characters only allowed.", vbInformation, ctlName & "_AfterUpdate()"
      End If
    Case "BirthDate"
      bd = CDate(Nz(txt1.Value, 0))
      If bd > 0 And bd < Date Then
         MsgBox "BirthDate Valid:" & txt1.Value, vbInformation, ctlName & "_AfterUpdate()"
      End If
    Case "Email"
       MsgBox "Email Address: " & txt1.Value, vbInformation, ctlName & "_AfterUpdate()"
    Case "Mobile"
       MsgBox "Mobile No. " & txt1.Value, vbInformation, ctlName & "_AfterUpdate()"
End Select

End Sub

Private Sub txt1_GotFocus()
'Got Focus Event is set for only
'Retirement Date field
'Testing for the field name is not required
Dim vDate As Variant, rdate As Date
Dim tmpRef As Access.Form

   Set tmpRef = frm2.frmSubForm1.Form
   'vDate = frm2.frmSubForm1.Form!BirthDate
   'vDate = frm2.frmSubForm1.Form.Controls("BirthDate").Value
   vDate = tmpRef!BirthDate
   
If vDate > 0 Then
    vDate = CDate(vDate)
    'calculate retirement Age/Date
    rdate = vDate + Int(56 * 365.25)
   txt1.Value = rdate
End If
MsgBox "Retire Date: " & txt1.Value, vbInformation, txt1.Name & txt1.Name & "_GotFocus()"
End Sub

Private Sub txt1_LostFocus()
Dim txtname As String
txtname = txt1.Name
Select Case txtname
    Case "RetireDate"
         'frm2.Controls("frmSubForm2").SetFocus
          frm2.frmSubForm2.SetFocus
    
    Case "Email"
          MsgBox "Email Address: " & txt1.Value, vbInformation, txtname & "_LostFocus()"
    Case "Mobile"
          MsgBox "Mobile Number: " & txt1.Value, vbInformation, txtname & "_LostFocus()"
          'frm2.Controls("EmpName").SetFocus
            frm2.EmpName.SetFocus
    Case Else
        If CDate(Nz(txt1.Value, 0)) = 0 Then
            MsgBox "Birth Date is Empty!", vbInformation, txtname & "_LostFocus()"
        End If
End Select
End Sub

Class Module Properties and Sub-Routines.

The TextBox Property is declared, with the WithEvents keyword, as txt1 Object, with Public Scope . Even though we are breaking the rule of encapsulation, by declaring this property as Public, it is justified because we are in the learning curve.  Later on you can declare it as a Private Property and add the Get/Set Property Procedures and change the Code to address the txt1 Object Property through the Property Procedures.

The Form Object frm2 will be assigned with the Main Form (Form_frmMain with it’s sub-forms) Object so that we can retrieve values from sub-form based text box controls, or write values into them too.

The txt1_AfterUpdate() Event Procedure in the ClsSubForm Class Module doesn't have anything new in it, because we saw it’s performance before.  The BirthDate, Email and Mobile  Text Box controls are from both sub-forms on the Main Form.  The AfterUpdate Events triggered on the Sub-Form controls are also comes into this Sub-Routine.

The txt1.GotFocus() Event Procedure of RetireDate Text Box runs in this Sub-Routine.  To insert the employee’s retirement age/date we need to read the Date of Birth of the employee from the BirthDate Text Box.  To read the value from there we need the direct reference of this Text Box on the first sub-form, using the frm2 Main Form Object Property.

Referencing Sub-Form Text Box from Class Module.

Different versions of the BirthDate Text Box references are given below:

vdate = frm2.frmSubForm1.Form!BirthDate
vDate = frm2.frmSubForm1.Form.Controls("BirthDate").Value

Or take the long route:

Dim tmpRef As Access.Form
Set tmpRef = frm2.frmSubForm1.Form
vDate = tmpRef!BirthDate

Now, stepping into the txt1.LostFocus() Event Procedure the notable actions we have included here is transferring control (or setting the insertion point) from frmSubForm1 last Text Box to frmSubform2 first Text Box and frmSubform2 last Text Box to the only Text Box on the frmMain form.

  1. Moving the insertion point from the last Text Box on frmSubForm1 to the first Text Box on  frmSubForm2.
    frm2.frmSubForm2.SetFocus
    OR
    frm2.Controls("frmSubForm2").SetFocus
    
  2. Moving the insertion point from the last Text Box on Sub-form2 to the EmpName Text Box on the Main Form.
    frm2.EmpName.SetFocus
    OR
    frm2.Controls("EmpName").SetFocus
    

Common Pitfalls in Setting Focus on Sub-Form Control.

The pitfall that we often encounter in attempting to set the focus on a Text Box inside the sub-form directly is something like the following example:

frm2.frmSubForm2.Form.Email.SetFocus

The above statement is logically correct and will not give any error messages when run, but it will not work.  The point to note here is that a sub-form sits within a sub-Form Container  control (with the same name of the Sub-Form) and setting focus on this control is enough.   When we do that the Text Box or any other Control on the sub-form with Tab Index 0 will automatically become active or will receive focus.

This is what we did in the first example, the reference of the sub-form container control on the Main Form (frm2.frmSubForm2.SetFocus) is used to set the focus on.  When you add the .Form  to the sub-form container-control then it become the reference of  a Form and the system simply ignores it.

The second option frm2.Control(“frmSubForm2”).Setfocus explicitly addressing it as a Control of the Main Form: frm2.

Setting Focus on a control on Main Form from the sub-form is straight forward frm2.EmpName.SetFocus or frm2.Controls(“EmpName”).Setfocus.

Building the Derived Class Module: ClsSubFormHeader.

Armed with enough ground work information on changes in the ClsSubForm Class Module we will proceed to build the Header Class (or Derived Class ClsSubFormHeader) to add the Form/Sub-Form Text Box Controls to the Collection Object.  We will add appropriate built-in Event statements required to announce the Event Procedure execution on the Text Boxes.  Without that we cannot be sure that the Class Module actually does the job we assigned to it, since the actions are happening behind the curtain, not on the Class Module of the Form.  Once you make sure that everything works fine you can remove the unwanted messages, showing the values from the Text Box.

The point to note here is that how to address (setting the reference to the Text Box controls on the sub-form(s) while adding it to the Collection Item.

Separate Class Object Instance for each Form Control

Remember this, separate instance of the Class Object ClsSubForm is added, for each Text Box on all the Forms, as separate Item in the Collection Object.  The Text Box’s required built-in Event triggering mechanism is also enabled in the Class Object, before adding it to the Collection Object.  Even though the Form’s Code Module is empty our Class Module Object is continuously monitoring, for the enabled built-in Event to happen, from the Collection Object storage.  When it happens on a particular Text Box the related ClsSubForm Class Object instance based sub-routine, like txt1_AfterUpdate(), executes and displays it’s result in the form of a message in the Application Window, or updates the value in a Text Box on the Form. 

The ClsSubFormHeader Class Object Code is given below:

Option Compare Database
Option Explicit

Private frm As Access.Form
Private T As ClsSubForm
Private C As New Collection

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

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

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

'Scan for TextBoxes on the Main Form
For Each ctl In frm.Controls
   If TypeName(ctl) = "TextBox" Then
   
       Set T = New ClsSubForm ‘instantiate Class
       Set T.txt1 = ctl
       
       Select Case ctl.Name
           Case "EmpName"
               T.txt1.AfterUpdate = "[Event Procedure]"
       End Select
       C.Add T ‘add to collection object
   End If
Next

'Scan for TextBoxes on the Sub-Form
For Each ctl In frm.frmSubForm1.Form.Controls
   If TypeName(ctl) = "TextBox" Then
   
      Set T = New ClsSubForm ‘instantiate Class
      Set T.txt1 = ctl
      Set T.sFrm = frm
      
       Select Case ctl.Name
           Case "BirthDate"
                  T.txt1.AfterUpdate = "[Event Procedure]"
                  T.txt1.OnLostFocus = "[Event Procedure]"
           Case "RetireDate"
                  T.txt1.OnGotFocus = "[Event Procedure]"
                  T.txt1.OnLostFocus = "[Event Procedure]"
       End Select
       C.Add T 'add to collection Object
   End If
Next

'Scan for TextBoxes on the Sub-Form
For Each ctl In frm.frmSubForm2.Form.Controls
   If TypeName(ctl) = "TextBox" Then
   
      Set T = New ClsSubForm
      Set T.txt1 = ctl
      Set T.sFrm = frm
      
       Select Case ctl.Name
           Case "EMail"
               T.txt1.AfterUpdate = "[Event Procedure]"
           Case "Mobile"
               T.txt1.OnLostFocus = "[Event Procedure]"
       End Select
       C.Add T 'add to collection Object
   End If
Next

End Sub

The change is in the For Each . . . Next statement where we sets the reference to the Sub-Form Controls to pick the Text Box Control from there and add it to the Collection Object, after enabling the required built-in Events.

On the first sub-form the For. . . Next Loop uses the following reference:

For Each ctl In frm.frmSubForm1.Form.Controls
.
.
.
Next

The ctl control carries the reference (address) of the sub-form Text Box while it is added to the Class Module ClsSubForm Txt1 Property and goes to the Collection Object as it's Item.

Same way the second Sub-form Text Box references are used in the For . . .Next Loop.

For Each ctl In frm.frmSubForm2.Form.Controls
.
.
.
Next

Both the sub-Forms are set with their Has Module Property Value to Yes. Both sub-Form Class Module does not have any VBA Code.

The Main Form (frmMain) Module Code is given below:

Option Compare Database
Option Explicit

Private T As ClsSubFormHeader

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

The ClsSubFormHeader derived Class is declared as Object T.  On the Form_Load() Event the  ClsSubFormHeader Class Object is instantiated.  The current Form Object is passed to the Property Procedure T.mFrm. of ClsSubFormHeader

If you check the ClsSubFormHeader Class Object Code you can see that this Form Object is passed to the frm2 Property of ClsSubForm, through the Property Set Procedure sFrm, in the statement Set T.sFrm = frm along with each instance of the Class Module ClsSubForm, for each Text Box in Collection Object Item.

Summary

The Sub-Form Control references are added to the Class Module Instance Property like any other control on the Main Form.  This is required when the enabled built-in Event triggers it sync with the correct Class Object instance and executes the Event Procedure.

You may download a Demo Database with all the Forms and VBA Code to try out and study the Code.

You may leave your observations, suggestions, comments on the Comment Section of this Page.

Downloads



Links to WithEvents ...Tutorials.

  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
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink
  10. WithEvents and Report Lines Hiding
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types
Share:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports msaccess tips Accesstips Downloads Objects Property Collection Object Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Dictionary Object Form Report Calculation Command Button Data Emails and Alerts Query RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code 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 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 hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts