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.

 

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

 

[ChrisO]

Who, me?