Steve said:
I am working on a very large database that has a main menu, many sub-menus
and a few sub-sub-menus. These menus typically have many command buttons
that open forms and reports in the database.
I assume you're talking about the built in the menu system? (yes/no??).
Forms and reports may also be
opened from command buttons on some of the forms.
1. Is there any way to programatically determine where a sub-menu or
sub-sub-menu is opened from? The code would need to reveal the name of the
menu and the name of the command button.
I think you've got this backwards? Why do you care what button is calling
the code that's going to be run? Approximately 90% of my custom menus call
vba code in the form that has the ****focus****.
All you need to do is make the code (a function) public, and then simply
place the function name in the buttons on-action setting.
Further, likely often you will have specific code to a particular form, and
once again, you simply declare those functions (in that form) as public.
The syntax to call the code then is:
=YourFunctionName()
Often, (if not most of the time), you code you call will need to pick up
some information about the current screen etc. So, my code most of the time
starts out, and grabs the current screen name. I use:
Public Function AskInvoicePrint()
Dim tblgroupid As Long
Dim frmActive As Form
Set frmActive = Screen.ActiveForm
tblgroupid = frmActive.frmMainClientB.Form!ID
If frmActive.InvoiceNumber = 0 Then
frmActive.InvoiceNumber = nextinvoice
frmActive.Refresh
End If
DoCmd.OpenForm "guiInvoicePrint", , , "id = " & tblgroupid
End Function
The above is code that the invoice print button runs. note how I right away
pick up the active form. After that, I can easily ref the forms object as if
the code was running much like the code would if put behind a button on the
form. In the above example, I also check if a invoice xnumber has been
generated before printing. And, the Refresh forces a disk write if in fact I
do change the invoice number. And, in addition the above clip also passes
the currently selected sub-form item that the invoice print form needs.
Also, if the code you write is for the particular form, then as mentioned,
you can simply place the code into the forms module code. There is no need
to pick up the active screen...and you can use "me.LastName, me.Refresh etc
as you always used.
So keep in mind if the function is public in a standard code module, then
the menu will call that function in that standard code module. However, the
form with the ***current*** focus code module is searched first for the name
of that function (this is perfect behavior as now we whichever form has the
focus will have it's code run. And, if we need/want "global" code for all
forms then place the function routine in a standard code "module and it will
be called for ALL forms (we simply pick up the form name using the above
screenactive example).
2. Is there any way to programatically determine where a specified form
is opened from? Again, the code would need to reveal the name of the menu
and the name of the command button.
Once again I'm confused as to why you need to pick up the menu and command
button on the menu? Just have it call your own vba code in the form (I must
be missing a BIG puzzle here, but perhaps you explain why you need to do
this???)
As to how to determine the previous calling form?
You can simply pickup the name of
the pervious form in the on-open event. In fact, you can grab the previous
form name as late as the on-load event. Hence, the following works well
In the forms code module place:
Option Explicit
dim frmPrevious as form
Then, in the on-open event go:
set frmPrevious = screen.ActiveForm
Now, anywhere in code, you can use fields, properties and even code of the
previous from
msgbox "calling form = " & frmPrevious.Name
(above display the name of the calling form)
frmPrevious!LastName = "bla bla bla"
(above would set the last name field of the previous form to bla bla bla)
frmPrevious.Refresh
(the above would force the previous form to write its current record to
disk, but not change its current record position).
Or, even call custom code or methods from that previous form like:
frmPrevous.MyRefresh
Since I create quite a few custom menu bars, then I don't have to worry
about using open args to "pass" the value/name of the previuos form. In fact
if you make it a coding standard in all your forms to always decleare a
"frmPrevious", then you always have a ready made reference to the calling
form. You can then over time even cut and paste code between different forms
becuase you alwasy declare frmPrevious in each form you build...
Anyway you might want a further explain as to why you need to pick up the
name of the command bar or button in the menu system? You can certainly pass
parameters from the custom menu bar when they call your vba code:
eg, you simply set the menu bar on-action to:
=MyPublicFunctionInAForm("parm1","Parm2")
The above would call public function in a form, and would also pass two
string parameters (so you would have to correctly declare the function to
accept two string parameters)
As I mentioned as a general rule you'll not need to pass parameters because
you simply place the code in the form's code module (this means whichever
form has the focus is the code that's going to get called by the custom menu
bar). As I mentioned for the cases where you have a global routine, simply
place the code in a standard code module, and when the menu system does not
find the "function name in the form's module, it will actually search for it
in a standard code module and run the code in that module. (and as mentioned
you simply pick up the forms name that's active by using screen active as
per the above example).
When I place a button on a form and I click on it to run some code in the
form, I never really care what the name of the button was or is. And, I
don't think you should have anymore of a problem when you use a custom menu
bar in place of buttions on a form....the problem is really teh same and
that code should not care/know what button is calling that code....
