Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: Using a Class Module to provide an 'On Change' event at a form level

    Getting a little stuck on this one, probably because I haven't had much experience of class modules (started looking at them yesterday).

    The situation is this...

    I have a form, that contains a potentially huge number of input textboxes (say over 50).

    Now I also have a results textbox.

    What I need to happen is for anytime any of these input textboxes are changed, the results textbox needs to be updated with the sum of their values.


    I've found some code that should do the job, and the logic behind it makes sense... The answer I found is for Excel (it uses the MSForms library), but it goes like:

    Firstly, create a class module in your VBA project (let call it clsTextBox)

    Code:
    Private WithEvents MyTextBox As MSForms.TextBox
    
    Public Property Set Control(tb As MSForms.TextBox)
        Set MyTextBox = tb
    End Property
    Private Sub MyTextBox_Change()
        AutoCalc() //call your AutoCalc sub / function whenever textbox changes
    End Sub
    Now, in the userform, add the folowing code:

    Code:
    Dim tbCollection As Collection
    
    Private Sub UserForm_Initialize()
        Dim ctrl As MSForms.Control
        Dim obj As clsTextBox
    
        Set tbCollection = New Collection
            For Each ctrl In Me.Controls
                If TypeOf ctrl Is MSForms.TextBox Then
                    Set obj = New clsTextBox
                    Set obj.Control = ctrl
                    tbCollection.Add obj
                End If
            Next ctrl
        Set obj = Nothing
    
    End Sub
    Source: Excel VBA Userform - Execute Sub when something changes - Stack Overflow


    The problem is basically, I can't get the damn thing to work...

    I've adjusted it so my layout looks like:

    Module - 'Function - AutoCalc()'

    Code:
    Option Compare Database
    
    
    Public Function AutoCalc()
    
    Me.tmpTestLabel.Caption = "TEST"
    
    End Function

    Class Module - 'clsTextBox'

    Code:
    Option Compare Database
    Option Explicit
    
    Private WithEvents MyTextBox As Access.TextBox
    
    Public Property Set Control(tb As Access.TextBox)
        Set MyTextBox = tb
    End Property
    
    Private Sub MyTextBox_Change()
        AutoCalc
    End Sub

    Form - 'testForm'

    Code:
    Dim tbCollection As Collection
    
    
    Option Compare Database
    Option Explicit
    
    
    Private Sub Form_Load()
    
    Dim ctrl As Control
    Dim obj As clsTextBox
    
    Set tbCollection = New Collection
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is Access.TextBox Then
                Set obj = New clsTextBox
                Set obj.Control = ctrl
                tbCollection.Add obj
            End If
        Next ctrl
    Set obj = Nothing
    
    End Sub

    Yet, whenever a textbox on the testForm form is changed, nothing happens to the tmpTestLabel. No errors loading the form, no errors whilst changing anything, just no 'expected' results either.


    If someone could explain to me why this isn't working, I'd be incredibly grateful, as it's doing my head in.


    Thanks guys!
    Last edited by kez1304; 12-01-11 at 09:15.
    Looking for the perfect beer...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by kez1304 View Post
    I've found some code that should do the job, and the logic behind it makes sense... The answer I found is for Excel (it uses the MSForms library), but it goes like:

    Source: Excel VBA Userform - Execute Sub when something changes - Stack Overflow

    The problem is basically, I can't get the damn thing to work...
    The problem is that Access and Excel work very differently when you consider their interfaces (Forms, Controls, etc.).

    1. Contrarily to Excel, Textboxes in Access do not derive from MSForms. The full reference to a Textbox in an Access Form is Access.TextBox, not MSForms.TextBox. So, though correctly defined if you have a reference to MSForms in your project, this will never work for a TextBox in an Access Form:
    Code:
    Private WithEvents MyTextBox As MSForms.TextBox
    2. Similarly, the full reference to a Form in Access is Access.Form and does not have an Initialize event. This event exists for a UserForms that derives from MSForms and this will not work for an Access Form:
    Code:
    Private Sub UserForm_Initialize()
    3. The easiest way to achieve your goal consists in writing the needed function in the Form module (which is a Class Module already), like this:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_colTextBox As Collection
    
    Private Sub Form_Open(Cancel As Integer)
    
        Dim ctl As Access.Control
        
        Set m_colTextBox = New Collection
        For Each ctl In Me.Controls
            If ctl.ControlType = acTextBox Then
                If ctl.Name <> "Text_Result" Then ' Do not add the TextBox receiving the result to the collection.
                    m_colTextBox.Add ctl, ctl.Name
                    ctl.AfterUpdate = "=AutoCalc()" ' Create the reference to the AutoCalc function for the AfterUpdate event of the control.
                End If
            End If
        Next ctl
        
    End Sub
    
    Private Function AutoCalc()
    
            Dim ctl As Access.TextBox
            Dim lngSum As Long                  ' Change to another type (Single, Double, Currency...) if needed.
            
            For Each ctl In m_colTextBox
                lngSum = lngSum + Nz(ctl.value, 0)
            Next ctl
            Me.Text_Result.value = lngSum
            
    End Function
    4. If you're further interested in associating Forms and Controls with Class Modules (this concept is often called "Dynamic External Event Processing", you could find valuable documentation on the subject at:
    http://www.vb123.com/kb/index.html?199901_ss_event.htm
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •