?
Solved

Shortcut menu pick up name of current sub form

Posted on 2004-11-22
12
Medium Priority
?
294 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:SQL_Stu
  • 6
  • 4
  • 2
12 Comments
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 240 total points
ID: 12643753
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
 
LVL 7

Author Comment

by:SQL_Stu
ID: 12643880
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12643944
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 7

Author Comment

by:SQL_Stu
ID: 12643989
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12644075
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
 
LVL 58

Expert Comment

by:harfang
ID: 12644099
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
 
LVL 58

Accepted Solution

by:
harfang earned 260 total points
ID: 12644120
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12644166
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12644170
Sorry, that was in response to the earlier comment at 1:32PM GMT, not the one at 1:35.
0
 
LVL 58

Expert Comment

by:harfang
ID: 12644438
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12644472
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
 
LVL 58

Expert Comment

by:harfang
ID: 12644612
No harm done, just please correct me as well as often as needed :)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question