Unable to use WithEvents effectively like we use in Excel

[vikasbhandari2]

Hey Gurus,



I know the topic is a little weird and I need your help badly.

I will explain the scenario first. I have been working on a project for my client in Access 2007. Now we have almost 25 windows with lots of textboxes in it. Now, when the whole project is almost ready, client has come back to us with a very strange requirement which is forcing us to include OnChange event, and a common code snippet in all of the textboxes.

I know I can write a function and go to each textbox and add the function one by one, but I just dont want to do it.

I thought of another way. I wrote the following example, and I am unsure how to implement it correctly so I need your help.

I wrote a class named Class1 with the following code:

Option Compare Database

Public WithEvents tb As TextBox




Private Sub tb_Change()
    MsgBox "Changed"
End Sub

Then I have a form with a single textbox named Text0. I have the following code in the CodeBehind:

Option Compare DatabasePublic cls As Class1






Private Sub Form_Load()
    Set cls = New Class1
    Set cls.tb = Me.Text0 'binding the class's textbox with the form's textbox
End Sub

I am not a super programmer but I think when I have bound the class's textbox with my form's textbox, whenever I change the text in my form's textbox, it should call the on change event of class1 also. But it is not calling. I am really excited to know that on which part my logic stands failed and would really love if someone can explain me this issue in detail.

Thanks,
V

The sample db is attached here:
Database3.accdb

[Rod]

If you want me to help in detail then you need to repost your sample db as v2007 (or earlier).

OK, my first observation is that I cannot understand how pursuing this will help you solve your client's requirement. Faced with your problem, I would do it the long-winded way, that is, add the code snippet manually to each and every text box throughout your project. My second thought would be to write some VBA that added this code snippet automatically to each module, but I would only consider this if I thought the effort of developing such a routine would be less than doing it manually or if the requirement was likely to recur in the future.

My second observation is: why the 'Change' event? This means that the 'WithEvents' routine, if and when working, will execute for each and every character typed into the textbox. Surely this is not what is wanted.

My third observation is to encourage you - nay insist - that you include the 'Option Explicit' statement at the head of each module. As soon as you start coding that affects communication between modules then it is essential (IMHO) to have control over your variables and references. Anyway it make debugging so much easier as VBA finds the typos, etc. for you.

Having said all this, now for the bad news. You are correct in what you say that the 'Change' event for 'Text0' is not being 'heard' by your class instance. In fact the truth is that the event is not being broadcast at all! And it won't be until you put some code - even dummy code - behind the On Change event for Text0 itself. Try it if you doubt me. So if you had in mind to somehow apply this technique to all those textboxes, you still need to code and event for each and every one!

I think you are really back to square one.