Reduce or eliminate event code from forms....

[amc]

I am used to develop Access forms (generally bound) in the very traditional way of writing code for all the controls that require them.

For instance, textbox containing a telephone number will check if the number is well formed in before_update (and, if not, Cancel=true and control.undo), whereas in after_update it will search for the number already being in other records (just for informational purposes)

If the form contains, 5 telephone number per person, I am mainiting very similar code 5 times, cluttering the form with code.

There are two immediate ideas to reduce or virtually eliminate so much code.

1) Declare a control of a certain "custom type or business object" (telephone number, say only Spainsh, for simplicity), and write validating code only once in that "type". Controls of that type automatically inherit the code, without any need to reference it

2) Write the events from code, using event handlers. It implies opening the form object, write on it and save it. A kind of batch process to run when creation/maintenance is needed.

May I receive some hints of that from people who has already experienced those paths, or others? Goal is to simplify code, typing and errors.


thanks

 

[pbaldy]

Another option is a public function that accepts a phone number as input, performs your check and passes back the desired result (true or false, etc).

 

[CJ_London]

If the form contains, 5 telephone number per person, I am mainiting very similar code 5 times, cluttering the form with code.

this sounds like poorly designed tables. if people can have more than one telephone number, they should be stored in another table with a one to many relationship from your persons table. then your form would have a subform (in continuous or datasheet view) to display and edit telephone numbers - so code only required once.

 

[amc]

thanks,

I try to avoid a too purist relational design (and continuous subforms, as well). Anyway, the essence of the problem still remains, avoiding (if possible) verbose code in forms

 

[CJ_London]

I try to avoid a too purist relational design (and continuous subforms, as well). Anyway, the essence of the problem still remains, avoiding (if possible) verbose code in forms

if you do not have a good relational design and avoid using continuous subforms, you will end up with verbose code, not to mention potentially tortuous queries with potential knock on effect on performance. But the choice is yours.

 

[plog]

I try to avoid a too purist relational design

You just won the Access forum.

At first I cringed and started to support CJ in his argument. But I can't stop reading that statement and smiling.

 

[amc]

thanks for your comments. I understand relational design has advantages and disadvantages, telephones is an example, addresses and emails might be other ones.

Anyway, the topic is trying to identify ways (if feasible) that could use inheritance or other mechanisms to reuse code in event forms without much overhead, in VBA - Access 2013-2016

 

[pbaldy]

Did post 2 get lost in the confusion?

 

[plog]

Ok now I have to jump in.

I came to this car forum for help for a specific issue, not to hear all you "certified mechanics" tell me why it won't work or why I shouldn't do this.

So either tell me what I should do to be able to use my old french fry grease in my car as motor oil, or please be quiet. I've already filtered out the big chunks and emptied my old oil. I just need you guys to tell me if I am missing any steps or what I can do to ensure this works. Do I need a special filter? Should I use more ethanol now? Please any advice is appreciated, except the most logical advice.

 

[sneuberg]

While I do not have an immediate need for the functionality you are looking for I think it would be great if you could do it. I once took a course in C# and one of the homework project required a lot of textboxes on a form of the same data type. I was very happy to find out that I could validate the data with the same event handler. In fact in C# all of the procedures in your form's code show up in the drop downs for the events. You just need to pick one.

I wonder if something like that can't be done in Access. In Access the properties for events are drop downs too, but the only thing in them is [Event Procedure]. You can type the name of a procedure in the field as I have in the attached, but when the event fires you get a message saying the object can't be found. I thought there was a way to make this work, but I can't figure it out.

Probably doesn't make much difference as there no way to get a handle on the event properties (what object fired the event) anyway. If only Access were based on C#.

 

[amc]

pbaldy, thanks for your comment.

I think you are suggesting what I am already doing

this is my code for one telephone textbox in Before Update

Private Sub telf1_BeforeUpdate(Cancel As Integer)
if Not Telf_BU(Me!telf1) Then
Me!telf1.Undo
Cancel = True
Exit Sub
End If
End Sub

and here is my code for the After Update (warn the user that the keyed telephone already exists in another "object" in the database, a supplier, a customer, whatever,...)

Private Sub telf1_AfterUpdate()

Dim txtPlaces As String
txtPlaces = ""
Dim arPlaces() As typObjExists

If ObjAlreadyExists(Me!telf1, "Telf", arPlaces) Then
txtPlaces = BuildTextoDeLugares(arPlaces)
MjeBox Mje("ObjAlreadyExists", Array(Mje("Telefono"), Me!telf1, txtPlaces))
End If

exit_:
Exit Sub
End Sub


Not very much code, but nice to avoid duplications

 

[CJ_London]

In Access the properties for events are drop downs too, but the only thing in them is [Event Procedure]. You can type the name of a procedure in the field as I have in the attached, but when the event fires you get a message saying the object can't be found. I thought there was a way to make this work, but I can't figure it out.

for that to work

a)myeventhandler needs to be declared as a public function in a module
b)it is called by replacing '[Event Procedure]' with '=myeventhandler()'

in fact if you start typing =name..., it will autopopulate.

Benefit is you can call public functions with the form 'has module' set to no so a faster form load

if you want to pass the variable which is the control with the event to be triggered you can either add a parameter to the function

e.g. public function checktelno(telno as string) as Boolean

and call it as

=myeventhandler([firsttelno])

or you can possibly use screen.activecontrol or screen.previouscontrol although depending on the event it may no longer be the active or previous control

However this does not answer the OP's search for an inheritable event

 

[CJ_London]

see we now have an example from AMC, so declare a public function in a module

 Public Function checkTelNo(ctrl as control) as Boolean
 if Not Telf_BU(ctrl) Then ctrl.Undo
End Sub

and for the beforeupdate event for telf1 put

=checkTelNo([telf1])

and telf2

=checkTelNo([telf2])

you can probably adapt your Telf_BU function to include the undo and cancel