What happens to an event handler's parameter when I specify a custom handler?

[soaphope]

If I type in the following in the properties editor for a form's event handler, say onClick:

=myfunc(10)

then when the onClick event fires, my custom function is called with parameter 10.

But what about when I type the same thing in for a function like onUnload that passes a Cancel parameter to an event procedure? Can I access that parameter from a custom event handler function? I tried writing something like:

Public Function myfunc(i as integer, ByRef param as variant)

to see if the Cancel parameter would "show up." Can someone shed some light on what happens to the Cancel parameter or how I can access it?

Thank you!

 

[MarkK]

It's sort of like this: you tell the system what you want it do in response to the event. If you use "[Event Procedure]" in the OnUnload property of a form, the system creates the Cancel variable and attempts to call a Private Sub Form_Unload(Cancel As Integer). But if you tell the system to call your custom "=YourFunction(10)" function then the code containing the Cancel variable is never run and the variable is never allocated memory.

 

[soaphope]

Thanks lagbolt - is there a way to cancel the form's closing from my custom function?

 

[Banana]

As Lagbolt indicates, you can't use the event handlers when you use your custom functions. It's as if you've completely replaced the event handler with your own custom function but obviously you can't replace the Cancel variant.

However, I do believe there should be a DoCmd.CancelEvent, but I've never used that so you should consult the documentation to verify this is the right method to invoke in your custom function.

Alternatively, (and I'm assuming here that you can't just hardcode in your form's event handler as usual - maybe you want a group of forms to behave in same way or something so you need to run it through a common set of events) you can write a class module with an WithEvents Form variable and use the class's events instead of form's event directly - that should enable you to have more control directly but without needing to hardcode.

Hope that helps.

 

[MarkK]

No, do it the other way around. Handle the unload event, do whatever--maybe cancel the unload, and then call your custom function from there.

 

[soaphope]

I have a lot of forms that have similar functionality. They are unbound forms that provide some advanced editing capability on records in my SQL Server database. So to automate the form production I created a FormManager object. Right now, all I have to do is put controls on the form and make one Init call to the FormManager. There's no other code that has to go in the form. The FormManager sets the various onXXXX events on the form and its controls that it cares about to automate the behavior of the form. The great thing about this approach is that if I want to add functionality or fix a bug, i can do it in the FormManager and all the forms instantly get the new behavior.

Unfortunately if you are correct, I'll have to begin adding code form by form to handle the Close event. I sure wish I could get at that Cancel!!

 

[Banana]

But if you already have FormManager class, then all you need is a private WithEvent Form variable then declare the close IN the FormManager. No need to add code to the forms directly.


That said, I have to wonder why unbound forms? Bound forms can be used to good effect, even with MySQL backend and is typically much less work and likely to be more reliable.

 

[soaphope]

Thank you Banana, I will try out the DoCmd.CancelEvent and post back if it works. I haven't ever seen WithEvents so I will do some research on that as well. Thanks for pointing me at another option.

 

[soaphope]

The SQL database only allows access to data through functions and stored procedures, so I don't have updatable recordsets that I can use with bound forms. The database does things like archive records when they are deleted and keep an audit trail when certain data is updated, and it presents some other server-side functionality that the forms use. We created a simple code generator that creates all the VBA functions that provide passthrough query calls, so all I have to do on a form is provide the name of a few functions that are called by the FormManager using Application.Run.

 

[Banana]

So you know - If you can only use stored procedures, and it's updatable (basically a one-table representation with a mean to map changes back to the source row individually), you can also use ADO recordset and bind it to the form. That also may be less work than an unbound form albeit more work than usual method because you can only bind ADO recordset in VBA but I've used ADO for cases where I needed a stored procedure/function. Whether that's an alternative you can use, I'll leave up to you to decide.

Best of luck.

 

[MarkK]

Another option if you are programmatically creating ad hoc forms is to create them from a template, and put code in that template. Then each form you create has that code in it, and you could do something like ...

Private Sub Form_Unload(Cancel As Integer)
[COLOR="Green"]  'pass the current form instance to a custom function
  'that determines whether to cancel the unload[/COLOR]
  Cancel = YourCancelUnloadFunction(Me)
[COLOR="Green"]  'call a public function [/COLOR]
  If Not Cancel Then YourCustomFunction 10
End Sub