Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Access Form Control Arrays and Event Capturing

Introduction

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. 

Sample Demo Project

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
    

    The VBA Code Line by Line

    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. Sample Data Entry

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


    Adding More Controls on Form

  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.


Adding the LostFocus Event

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

Moving to the Next Stage

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.


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