how to loop the .changeevent

[icemonster]

how do you loop the onchange event?

sample

for each control
onchange = subroutine

next

 

[VilaRestal]

The code would be:

Dim ctl As Control
For Each ctl In Me.Controls 'Or some other controls collection
    ctl.OnChange = "Macro1" 'for example
Next ctl

In Access VBA, highlight the word OnChange and press F1 to be sure you understand what setting the value of this property does. It's a rare and quite sophisticated thing to be doing. I wonder if you really need to.

 

[VilaRestal]

Oh and not every control type has an OnChange property. I think only TextBoxes do so the code should be:

Dim ctl As Control
For Each ctl In Me.Controls 'Or some other controls collection
    If ctl.ControlType = acTextBox Then ctl.OnChange = "Macro1" 'for example
Next ctl

 

[icemonster]

how bout combo boxes?

 

[icemonster]

and where would this code be placed?

 

[VilaRestal]

how bout combo boxes?

Yeah comboboxes do too.

and where would this code be placed?

I have no idea because I have no idea what you're trying to achieve by doing this.

 

[icemonster]

tried it but not working with the function within the module.

 

[VilaRestal]

Sorry icemonster, without a lot more info about what you've got and what you're trying to do I can't help and I doubt anyone can.

I'm 99% certain that whatever you're trying to do then looping through the OnChange properties of all controls in a collection is not the way to do it.

Whatever you're trying to do must be quite sophisticated and you obviously don't know some basic concepts of VBA so I would advise you not to try until you've learnt the basics.

 

[icemonster]

no i do get the basics just that am not used to unbound forms. the purpose of this was so that i can track the changes made within my unbound form to trigger the enabling and disabling of certain commands. but it's ok, thanks for your hepl. i found a different way.

 

[VilaRestal]

OK sorry. It's just the code you posted at the start:

onchange = subroutine

OnChange is the property of an object not a variable or an object itself

subroutines aren't objects and you can't assign them as the value of any property or set any other object to them (the subroutine itself, not the return value of a function, which of course you can do those things with)

Each event for each control can only ever have one subroutine for it and that is fixed at design time. There's no way to change that at runtime just as you can't write code at runtime.

You can however use a controls OnChange (or OnClick or OnKeyDown etc) to change what happens at that event to something other than executing the VBA code (e.g. run a macro instead).

I hope that explains it a bit.

 

[icemonster]

it's alright. just that i'm used to subroutines before just kept the term i guess.

 

[ChrisO]

>>Each event for each control can only ever have one subroutine for it and that is fixed at design time. There's no way to change that at runtime just as you can't write code at runtime.<<

That’s actually incorrect.

The event properties on the property sheet are strings and they certainly can be set at runtime. They can also be changed at runtime depending on the conditions required. They can call private function within the Form’s module or public functions in standard modules

They are persistent only to the extent that the Form is open, they will be lost on Form closure.

In Access 2003, and earlier, they need to be functions and not subroutines else they will be called more than once. This seems to have been corrected in Access 2007.

Some arguments can be passed to the functions but the return value of the function can not be used, as far as I know.

Chris.

 

[VilaRestal]

So, when MS Help says they can be changed to the name of a Macro they also mean the name of a function (Macro in the Excel sense of the word)?

Sub Test()
Command1.OnClick = "TestFunc"
End Sub

Function TestFunc()
MsgBox "Test"
End Sub

Or would it be as part of an expression?

Sub Test()
Command1.OnClick = "=TestFunc()"
End Sub

Function TestFunc()
MsgBox "Test"
End Sub

 

[ChrisO]

More as part of an expression as in it will be evaluated and to evaluate a Function it will be called.

It can also be turned off on the fly by setting the property to a zero length string.

So, =TestFunc() is evaluated by calling Function TestFunc().

Private Sub Form_Open(Cancel As Integer)
    Command1.OnClick = "=TestFunc()"
End Sub


Function TestFunc()
    MsgBox "Test"
End Function

Chris.

 

[Pat Hartman]

I don't know what you're trying to do but I'm going to guess that the on Change event is not the correct event. It has very limited usefulness and is only used when you have a need to trap each charater as it is typed into a control because it runs for EVERY character typed into the control, not just once as the other events. You may want the on Dirty event if you want to do something the FIRST time a character is typed into each control or you may want the BeforeUpdate event if you want to run some code before a value is saved.

In any event, the way I read your question is that you want to write one piece of code that runs whenever a specific event runs on any control. This cannot be done with a loop since event processing is preemptive in nature which is different from a property which in and of itself does nothing. It is used to control code that has been initiated by something else.

It is quite possible to create macros or functions that can be placed in the appropriate event property of a contol/form. I would use a function since I have an aversion to macros. As long as you place the function in the form's class module, you can refer to form fields from within it.

You seem to have programmed in other environments. Well - the big difference between Access and everything else, is that Access has BOUND forms and when in Access, you should go with the flow and take advantage of everything Access does for you. It is extremely, extremely, extremely rare to use unbound forms to handle data. What are you doing that is preventing you from using a bound form?

 

[icemonster]

well i guess my mistake was not having explained my situation clearly.

basically i just got lazy and wanted to find a way to detect changes made in my unbound form. normally i would set a variable to false when something is typed on the field. but i found myself having too much fields and got lazy putting then on each fields' on change event. so i saw somewhere before about looping through the unbound fields to detect changes.

 

[ChrisO]

You are probably better off using a bound Form.

However…

Option Explicit
Option Compare Text


Private intChangeEventHasFired As Integer


Private Sub Form_Open(ByRef intCancel As Integer)
    Dim ctl As Control
    
    [color=green]' Not all controls have a change event.[/color]
    On Error Resume Next
    
    For Each ctl In Me
        ctl.OnChange = "=HandleChangeEvent()"
    Next ctl
    
End Sub


Private Function HandleChangeEvent()

    intChangeEventHasFired = True
    
End Function


Private Sub Form_Unload(ByRef intCancel As Integer)

    [color=green]' Note that if an Application.Quit or DoCmd.Quit
    ' command has been executed anywhere in the project
    ' then the intChangeEventHasFired Flag will be
    ' set to False before this event is fired.[/color]
    If (intChangeEventHasFired) Then
        [color=green]' Do whatever needs doing.[/color]
    End If
    
End Sub

Chris.

 

[Pat Hartman]

You're not really putting this code in the change event are you?

 

[ChrisO]

Who, me?

 

[Pat Hartman]

No, icemonster. I think you would know better although you didn't question the request.