Me Keyword in Public Sub

[JamesMcS]

Hi! Have searched here and on t'interweb but I can't seem to find teh right wording...

So, I've got this public sub in a standard module, that's kicked off in the on click of a check box. There's a couple of references to the form in the code, which, when explicity referred to (forms!...) work fine - but I want to use the sub in another form. Me/me.parent doesn't work, throwing up "Invalid Use of Me Keyword" - so what I'm after is the way to refer to the form, but without actually naming it.

 

[JamesMcS]

OK, fix found but I'm sure it's not 100% right. I used screen.activeform. instead of me.

Any more correct ideas?

 

[JamesMcS]

Right then - the screen.activeform only works if I'm calling the sub from a main form. It's actually controls on a subform, so what's the right way to refer to it? Thought I'd sorted this, only to have sweet victory snatched from me....

 

[Banana]

If you want the sub to work for any forms in your application, it's best to pass the form as an argument and replace Me with the parameter.

Example of calling syntax from inside the form's module:

MyCoolSub Me

Example of sub in the standard module:

Public Sub MyCoolSub(frm As Access.Form)

Msgbox frm.Name

End Sub

HTH.

 

[ChrisO]

An alternative is to pass the Control which caused the event.

So, from a Sub Form Check Box: -

Private Sub chkSubFormCheckBox_Click()

    MyPublicProcedure Me.chkSubFormCheckBox

End Sub

In a Public Module display the Control name, the Sub Form name and the Main Form name: -

Public Sub MyPublicProcedure(ByRef ctlThisControl As Access.Control)

    With ctlThisControl
        MsgBox .Name & " on Sub Form " & .Parent.Name & " which is on Main Form " & .Parent.Parent.Name
    End With
    
End Sub

Note that .Parent is a pointer to the Sub Form and .Parent.Parent is a pointer the Form in which the Sub Form resides, the Main Form.

 

[Banana]

Nice technique, ChrisO.

One minor issue with Parent property is that if there is no Parent, a runtime error is raised instead of (IMHO) returning a null pointer, so one has to be certain there is in fact a parent to reference to or properly handle the error. This is not really an issue for controls since by definition, they must be a child of the form but is an issue for determining whether a form is exposed as a subform or as a form by its own.

For this reason, I usually write a separate function IsChild() to safely ascertain whether an object has a parent and handle the error separately.

 

[ChrisO]

Well, like a lot of threads we may be going off at a tangent, nothing new there…

The disadvantage of passing Me, as opposed to passing the Control which caused the event, is that we can’t ‘drill down’ to the Control. Form to (>) Controls is a one to many, if we like to view it that way. So when we get to the Public procedure with Me then how do we determine which Control, if any, caused the event which called the procedure?

On the other hand, if we pass the Control (singular) we know which Control caused the event and can reference its Form…Control to (>) Form is a one to one, if we like to view it that way. So, when we pass the Control we have more information available to us in the public procedure, we have both the Control and its Parent.

Another situation can arise: the public procedure may not be called by a Control…it could be called by a Timer event. In this case there is probably a reason for the Timer event to fire and it could involve a Control. But even if it did not involve a Control, and simply involved the Form, then passing a Control reference still works, one to one.

So, by passing a Control it gives us more information (more bang for our buck) than passing a Form.

Error handling is another story altogether.

 

[Banana]

Well, like a lot of threads we may be going off at a tangent, nothing new there…

Well, hopefully, it'll be beneficial for all.

The disadvantage of passing Me, as opposed to passing the Control which caused the event, is that we can’t ‘drill down’ to the Control.

Astute observation, ChrisO. OP did ask for form and not the control but yes if the requirement is that we need to know which control should interest us, it may be preferable to use Control and as you point out, resolve the control's parent.

But even if it did not involve a Control, and simply involved the Form, then passing a Control reference still works, one to one.

Well, we can't pass a Form object to a Control parameter and while we certainly can infer the form by examining the control's Parent property, one must give consideration as to whether the requirement will be satisfied with just a Form reference or need more detail. To borrow Rabbie's signature - The best solution is the simplest one that meets all requirements. Using Control.Parent when a Form reference will do means the code is complicated for no gain. OTOH, using Form reference does not allow us to reuse the same functionality for where controls is involved so in that case, there is advantage even with increased complexity.

Hopefully, this will give other people food for thoughts in how one would go about structuring the code.

 

[ChrisO]

So, does not the question become; if we had to choose one thing to pass to a (singular) public procedure, would we pass a Form or a Control on that Form?

 

[Banana]

But surely the question in turn depends on the question of what the public procedure's functionality should be.

To provide a concrete example: If we wanted to have a consistent formatting of messaging to the users that the data entered was invalid, then certainly the procedure should expect control reference since we can then tell the user which control has the invalid data, do some highlight or set focus. On the other hand if the procedure is to hide the form which is used as a criteria for a report during its preview, then form reference is likely to be sufficient.

As they say, right tool for the job. Sometime one needs a tiny hammer. Then there's a need for sledgehammer.

 

[ChrisO]

My question was…
If we had to choose one thing to pass to a (singular) public procedure, would we pass a Form or a Control on that Form?


Your answer was…
As they say, right tool for the job. Sometime one needs a tiny hammer. Then there's a need for sledgehammer.

Your answer requires two hammers.

 

[The_Doc_Man]

ChrisO, no it DOESN'T require two hammers. A public subroutine that always does the same thing always requires the same input-class (be it the big hammer or the little one.) The subroutine never asks for the other hammer. But if you don't know what the subroutine does, you might GIVE it the wrong hammer. The difference is always knowing what is required, not knowing the range of possible answers for different requirements.

If you have a form-related function in your code, pass a form. If you have a control-related function in your code, pass a control. If you have a string-related function in your code, pass a string. If you have a gas-related function, pass gas. How difficult is that? Don't take this as impugning you personally, but your comments in this specific case seem obfuscatory or else you flat-out missed the point.

 

[The_Doc_Man]

James - the Me keyword is a shortcut for "the parent of the class module executing this code." Obviously, if you are in a General (non-class) module, the parent of the module isn't a form or report and Me doesn't mean what you'd like it to mean.

I wish that the public general modules COULD track back subroutine calls to class modules and then derive the value of "ME" from the call stack. But that ain't the way it works. Which is why you must pass more specific variables when touching general module code.

For what it is worth, if you are dinking about in Excel, "ActiveSheet" to Excel has the same meaning as "Me" to Access. In Excel, you must activate a sheet. When you do, it becomes "ActiveSheet." In Access, you must open a form. When you do, it becomes "Me." Same difference. Word VBA has a similar concept - though I've forgotten the concept name at the moment...

In all of those cases, the public code from a general module is not intimately associated with the context of its caller. In fact, it would be possible (though admittedly rare) to use the same general code module for a callable function in any of those contexts. So "Me" and "ActiveSheet" won't mean anything to code not tightly bound to either application.

 

[Banana]

For what it is worth, if you are dinking about in Excel, "ActiveSheet" to Excel has the same meaning as "Me" to Access. In Excel, you must activate a sheet. When you do, it becomes "ActiveSheet." In Access, you must open a form.

Well, FWIW, Access has a concept of ActiveForm. Furthermore when I'm automating Excel, I don't like to use ActiveSheet for same reason as not want to use ActiveForm or Active<whatever> that Screen object provides. I try to grab a reference to the given worksheet and use that variable throughout the automation so I know there'll be no ambiguity next time ActiveSheet is called. It did happen that in one piece of inherited code where ActiveSheet was used, some operation caused the Activesheet to change and we'd end up with half formatting on one sheet and other half formatting on another sheet. Messy.

 

[The_Doc_Man]

I agree with you, I use a Set wstX = ExcelObj.workbook(x).worksheet, too. My point was more that the other utilities have shortcuts, but they have very similar limitations when you step outside of the class module that holds them.

 

[ChrisO]

Nuts! Do you remember the General?

 

[The_Doc_Man]

Would that be General McAuliffe in Bastogne? While the comment may be apocryphal, I believe General Patton considered General McAuliffe to be eloquent, saying so much in such a short response. I also know the translation offered to the bewildered German officers who didn't understand the terse reply.

Chris, thanks for the kind vacation suggestion. But wifey's condition requires her to avoid excessive heat and I've smelled enough sulfur in my life that I don't need to be around it any more.