Shortcut menu pick up name of current sub form

OK guys, here's the situation:

I have a main form - FormA
I have a sub form on FormA - SubFormA
I have a shortcut (ie. right click) menu on SubFormA - one of the items is "Edit"
I have written a bit of code that should run when "Edit" is selected.

Here's the question:

How do I pass the name of the current subform (in this case SubFormA) to the function?  I would know how to do it if it were on a button/other control (ie. me.form.name), but how can you pass the form name to a macro that contains a "RunCode" item?

Stu
LVL 7
SQL_StuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shanesuebsahakarnCommented:
Hmm - it's not easy, since there is no way to reliably pick up the name of the subform container object. Can you not pass a reference to the subform as a Form object instead? Do you actually need the container name or do you need to just reference it to access its objects or properties?
0
SQL_StuAuthor Commented:
Shane,

All I'm trying to do is create a generic shortcut menu that will detect which form/subform the user is on and the form/subform name is passed to the shortcut menu macro.

So, simply returning the name of the form/subform and passing it as a parameter to the function that gets called on the menu item macro.

Stu
0
shanesuebsahakarnCommented:
You can return the current form by using:
Screen.ActiveForm.Name

However, you can't retrieve the subform container name this way. One way you might be able to do it is to loop through all of the subforms on the main form and check if one of its controls currently has the focus, but I haven't tried this.

I would highly recommend going down the route of passing the form or subform as an object rather than its name. So if you have:
Dim myForm As String

Forms(mYForm).SomeControl="Some text"

you can instead use:
Dim myForm As Form

myForm.SomeControl="Some text"

You can pass the current form simply by using the object variable Form (or Me in VBA) - this will pass the active subform or form as an object.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SQL_StuAuthor Commented:
Sorry, Shane, I can easily do what you've suggested in VBA, but I'm talking about passing the form object/name to a function that gets called in a MACRO used for a shortcut menu.

You know, when you use "RunCode" in a macro and you name a function like "=myfunctionname()".  I want to be able to do:  "=myfunctionname(screen.activeform.name)" or "=myfunctionname(screen.activeform)".

I've tried this, but the GUI convertes the "screen.activeform" into "[screen].[activeform]" - which it doesn't recognise as an object - just text.  I've also tried "=myfunctionname(=activeform.name)", but it doesn't like that either.

If you can't answer the above, do you know how to build a shortcut menu in VBA code rather than using a macro?

Stu
0
shanesuebsahakarnCommented:
I suppose you could call a parameter-less function which in turn calls the "real" function passing the appropriate parameters that you want to run from the shortcut as a workaround - would that be an acceptable option?

Sorry, I don't really use macros - I avoid them like the plague if I can :(
0
harfangCommented:
A side effect of right-clicking is setting the focus, so use at you will:

Screen.ActiveContol
Screen.ActiveForm

By looking at the .Parent Chain of the current control you can get all the way back to the ActiveForm.

Good Lucik
0
harfangCommented:
The macro calling is a non-issue. Simply examine Screen.ActiveForm and Screen.ActiveControl in the VB code. No need to pass any parameter to it.
Good Luck
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shanesuebsahakarnCommented:
I'm not sure that will work - ActiveControl won't return the subform, but the control on the subform that currently has the focus. Since the control doesn't have a Parent property unless it's on a tab control, you can't traverse the chain. Right clicking doesn't always change the focus - if the subform currently has the focus, right clicking in an empty space on the main form keeps the focus on the subform control (but bizarrely, the reverse is not true).
0
shanesuebsahakarnCommented:
Sorry, that was in response to the earlier comment at 1:32PM GMT, not the one at 1:35.
0
harfangCommented:
I just made the following test:
* AutoKeys macro runs code ParentChain()
* Global function ParentChain() defines ctl and frm (control and form) and sets those to Screen.ActiveControl and .ActiveForm, then stops.
* Exploring from the immediate pane:

? ctl.Name
cboSupplierID
? ctl.Parent.Name
fsubCategoryProducts
? ctl.Parent.Parent.Name
frmCategories
? frm.Name
frmCategories

Going down the chain is more complex, and not reliable if you display the same subform twice on the main form. You need to loop through all controls, find subform controls, test their content, find tab controls, test their controls...

So ActiveForm is always the main form, but ActiveControl can be used to find the missing links ;)
0
shanesuebsahakarnCommented:
Durrrr...you know, I should really be more careful when I'm testing. ?ctl.Parent obviously gives an error since Parent returns a Form object....

I knew that. Ya gotta believe me. Honest!! :-)
0
harfangCommented:
No harm done, just please correct me as well as often as needed :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.