Excellent Navigation Method!

Hi,

If you are like me and you wish you had an easy way for Access to "see" what
form or subform currently has the focus so you can use that info in shortcut
routines for instance, I came up with an easy fix.

Lets say your Main form is call "Main", your first subform is called "Data1"
and your subform on Data 1 is called "DataSub1".

Ok now lets say you've written ur database so that the subforms can be
changed to show any number of forms. The are changed by changing the
..sourceobject using code.

Let's also say that you have written a Shortcut Menu that you want to be
able to work anywhere, no matter where your form is located, Data1 or
DataSub1. The problem is that if you want to perform actions on your subform
using your shortcut menu, if Access doesn't know the current "address" of the
form, its doesn't know what to do.

SOLUTION:
Ok, here is my fix. It's easy actually. Start by creating a 2 Global
Variables like so:

Dim vfrmObject as Object
Dim vfrmName as String

Now on the OnEnter event for your subform simply change vfrmObject to the
location address and vfrmName to the "name" of the location.

For example. If My form "frmPhoneNumbers" is located in subform "Data1" on
my Main form, I set vfrmObject to equal Forms![Main]![Data1] and vfrmName =
"Data1" when I enter Data1, like so:

Set vfrmObject = Forms![Main]![Data1]
vfrmName = "Data1"

Now if I want to create a shortcut menu for my form "frmPhoneNumbers" that
will work if it happens to be in Data1, I just use the following:

With vfrmObject

..form![phonetype] (the field I want to change) = "Home Phone"

end with

Now no matter which subform your frmPhoneNumbers phone is in, Access will
see the address correctly.

WHAT ABOUT SUBFORMS WITHIN SUBFORMS:

No problem, set the OnEnter event for your subform as follows:

Select Case vFrmName

"Bill Mitchell" wrote in message
...


Let's also say that you have written a Shortcut Menu that you want to be
able to work anywhere, no matter where your form is located, Data1 or
DataSub1. The problem is that if you want to perform actions on your
subform
using your shortcut menu, if Access doesn't know the current "address" of
the
form, its doesn't know what to do.

SOLUTION:
Ok, here is my fix. It's easy actually. Start by creating a 2 Global
Variables like so:

Dim vfrmObject as Object
Dim vfrmName as String

Actually, as a developer, and general rule you do want to avoid global vars.
If you use global vars, then you can only have one copy of your code
running. (and, further, that means only one copy of the form loaded - you do
realize that ms-access allows multiple copies of the same form to be
loaded...right?) The other issue is portable code. So, if you copy your
code, you will now also have to remember to change the name of the global(s)
you are using (if you are copying code in the same application). And, if
moving the code to another application, you simply have to remember to copy
the global vars. . This is not a huge deal, but I did want you to be aware
that as a general development approach, you do want to avoid global when
possible.(as you then loose the ability to have more then one copy of the
forms running. (and, as mentioned..there is a additional code maintenance
problems that arise).


Set vfrmObject = Forms![Main]![Data1]
vfrmName = "Data1"


You can write your code to pick up the active form. For example:

' resolve the form ref
Dim frmSub As Form
Dim strActive As String
Dim strForm As String

strForm = Screen.ActiveForm.Name
strActive = Screen.ActiveForm.ActiveControl.Name

Set frmSub = Forms(strForm)(strActive).Form

Now, you got a form ref...

msgbox "phone number is " & frmSub!PhoneNum

At this point, you now have a var reference to the frmSub, and can do
anything you want..such as:

frmSub.Requery

msgbox "num recs = " & frmSub.RecordSetClone.RecordCount

Using the above means you don't have to start putting some code in each
sub-form control event to set a reference. In fact, a large portion of menu
code should be written to pick the up active screen and control. If you code
this way, you will again find often that the same menu code can be used for
more then one screen/form.

What about sub-sub forms? Well, even better is to code a general function
that ALWAYS returns the form ref. That way, the forms menu code will work if
it is the main form, a sub-form..or even sub-sub-form (no need to code for
sub forms....everything should work if the form is a main form...or in fact
is a sub-form, or sub-sub form).

You thus can code like:

dim frmMyForm as Form

set frmMyForm = GetActiveForm

msgbox "num recs = " & frmMyForm.RecordSetClone.RecordCount

The function to "walk" and drill down to the current form (or sub form, or
sub sub form...in fact deep as you want) is based on the above example
code....

Public Function GetActiveForm() As Form

Dim aForm As Form

Set aForm = Screen.ActiveForm

Do While aForm.ActiveControl.ControlType = acSubform
Set aForm = aForm.ActiveControl.Form
Loop

Set GetActiveForm = aForm

End Function

Using the above eliminates the need for global, eliminates the need for you
to set a forms ref in the forms controls enter event. And, the above code
also works when NOT using a sub-form! And, the code is portable, and you can
safely have multiple copies of the form loaded....

I not trying to shoot down you example advice. However, the only
thing that I am pointing out here is that when you start to write
and use menu code, then you should (and will) use the screen object
(screen.ActiveForm, Screen.ActiveContorl) as a general approach
to solve menu code problems.

And, another great tip here is if you use a common naming convention for
your menus code (those public functions you place in a form that the menu
calls), then you often can use the SAME menu for more then one form.
Remember, menus try and run the function name (public) in the forms code
BEFORE the modules are searched for the function name. (simply put: whatever
form has the focus is the FIRST place the function names resolve to..and
THEN the modules are searched). Also, do NOT fully qualify the forms ref
when you do this..as there is a bug. You can read about it he

http://www.mvps.org/access/bugs/bugs0048.htm

I do think your tip about using the sub-form controls on-enter event is
useful. Often, to make menu code work with several tabs and sub-forms, then
setting a ref in the on-enter event is a good way to resolve which sub-form
to use. However, don't forget about the screen object either!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Thanks so much Albert, I'll try it out. I'm only wondering if it will work
when my subform control Name (Data1) is different than the form Name it
contains (frmPhone) for instance? I'll give it a shot and see.

A few other questions real quick:

1. Is there a way to make a continuous form select the underlying record as
active when my mouse moves over it? Some sort of mousemove event?

2. Is there a way to code my mouse pointer so that it shows a line of text
I choose rather than an hourglass? This would act like a sort of portable
Tooltip when using drag and drop or when copying and pasting. e.g., Let's
say i click on a filed containing "Bill Smith" and copy it. Then my mouse
cursor changes to "Bill Smith" until I paste it. Would be useful. I've seen
it done in other programs but don't know how. Other than that I can do the
coding for Drag and Drop.

3. I am an Executive Recruiter specializing in IT. You are a real pro,
please shoot me a copy of your resume at if you
are seeking sopme contract work. My website is www.executivedecision.biz.

Thanks in advance

Bill Mitchell
"Albert D. Kallal" wrote:

"Bill Mitchell" wrote in message
...


Let's also say that you have written a Shortcut Menu that you want to be
able to work anywhere, no matter where your form is located, Data1 or
DataSub1. The problem is that if you want to perform actions on your
subform
using your shortcut menu, if Access doesn't know the current "address" of
the
form, its doesn't know what to do.

SOLUTION:
Ok, here is my fix. It's easy actually. Start by creating a 2 Global
Variables like so:

Dim vfrmObject as Object
Dim vfrmName as String

Actually, as a developer, and general rule you do want to avoid global vars.
If you use global vars, then you can only have one copy of your code
running. (and, further, that means only one copy of the form loaded - you do
realize that ms-access allows multiple copies of the same form to be
loaded...right?) The other issue is portable code. So, if you copy your
code, you will now also have to remember to change the name of the global(s)
you are using (if you are copying code in the same application). And, if
moving the code to another application, you simply have to remember to copy
the global vars. . This is not a huge deal, but I did want you to be aware
that as a general development approach, you do want to avoid global when
possible.(as you then loose the ability to have more then one copy of the
forms running. (and, as mentioned..there is a additional code maintenance
problems that arise).


Set vfrmObject = Forms![Main]![Data1]
vfrmName = "Data1"


You can write your code to pick up the active form. For example:

' resolve the form ref
Dim frmSub As Form
Dim strActive As String
Dim strForm As String

strForm = Screen.ActiveForm.Name
strActive = Screen.ActiveForm.ActiveControl.Name

Set frmSub = Forms(strForm)(strActive).Form

Now, you got a form ref...

msgbox "phone number is " & frmSub!PhoneNum

At this point, you now have a var reference to the frmSub, and can do
anything you want..such as:

frmSub.Requery

msgbox "num recs = " & frmSub.RecordSetClone.RecordCount

Using the above means you don't have to start putting some code in each
sub-form control event to set a reference. In fact, a large portion of menu
code should be written to pick the up active screen and control. If you code
this way, you will again find often that the same menu code can be used for
more then one screen/form.

What about sub-sub forms? Well, even better is to code a general function
that ALWAYS returns the form ref. That way, the forms menu code will work if
it is the main form, a sub-form..or even sub-sub-form (no need to code for
sub forms....everything should work if the form is a main form...or in fact
is a sub-form, or sub-sub form).

You thus can code like:

dim frmMyForm as Form

set frmMyForm = GetActiveForm

msgbox "num recs = " & frmMyForm.RecordSetClone.RecordCount

The function to "walk" and drill down to the current form (or sub form, or
sub sub form...in fact deep as you want) is based on the above example
code....

Public Function GetActiveForm() As Form

Dim aForm As Form

Set aForm = Screen.ActiveForm

Do While aForm.ActiveControl.ControlType = acSubform
Set aForm = aForm.ActiveControl.Form
Loop

Set GetActiveForm = aForm

End Function

Using the above eliminates the need for global, eliminates the need for you
to set a forms ref in the forms controls enter event. And, the above code
also works when NOT using a sub-form! And, the code is portable, and you can
safely have multiple copies of the form loaded....

I not trying to shoot down you example advice. However, the only
thing that I am pointing out here is that when you start to write
and use menu code, then you should (and will) use the screen object
(screen.ActiveForm, Screen.ActiveContorl) as a general approach
to solve menu code problems.

And, another great tip here is if you use a common naming convention for
your menus code (those public functions you place in a form that the menu
calls), then you often can use the SAME menu for more then one form.
Remember, menus try and run the function name (public) in the forms code
BEFORE the modules are searched for the function name. (simply put: whatever
form has the focus is the FIRST place the function names resolve to..and
THEN the modules are searched). Also, do NOT fully qualify the forms ref
when you do this..as there is a bug. You can read about it he

http://www.mvps.org/access/bugs/bugs0048.htm

I do think your tip about using the sub-form controls on-enter event is
useful. Often, to make menu code work with several tabs and sub-forms, then
setting a ref in the on-enter event is a good way to resolve which sub-form
to use. However, don't forget about the screen object either!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Seems to work perfectly Albert. You da man!

Thanks much.

Bill

"Albert D. Kallal" wrote:

"Bill Mitchell" wrote in message
...


Let's also say that you have written a Shortcut Menu that you want to be
able to work anywhere, no matter where your form is located, Data1 or
DataSub1. The problem is that if you want to perform actions on your
subform
using your shortcut menu, if Access doesn't know the current "address" of
the
form, its doesn't know what to do.

SOLUTION:
Ok, here is my fix. It's easy actually. Start by creating a 2 Global
Variables like so:

Dim vfrmObject as Object
Dim vfrmName as String

Actually, as a developer, and general rule you do want to avoid global vars.
If you use global vars, then you can only have one copy of your code
running. (and, further, that means only one copy of the form loaded - you do
realize that ms-access allows multiple copies of the same form to be
loaded...right?) The other issue is portable code. So, if you copy your
code, you will now also have to remember to change the name of the global(s)
you are using (if you are copying code in the same application). And, if
moving the code to another application, you simply have to remember to copy
the global vars. . This is not a huge deal, but I did want you to be aware
that as a general development approach, you do want to avoid global when
possible.(as you then loose the ability to have more then one copy of the
forms running. (and, as mentioned..there is a additional code maintenance
problems that arise).


Set vfrmObject = Forms![Main]![Data1]
vfrmName = "Data1"


You can write your code to pick up the active form. For example:

' resolve the form ref
Dim frmSub As Form
Dim strActive As String
Dim strForm As String

strForm = Screen.ActiveForm.Name
strActive = Screen.ActiveForm.ActiveControl.Name

Set frmSub = Forms(strForm)(strActive).Form

Now, you got a form ref...

msgbox "phone number is " & frmSub!PhoneNum

At this point, you now have a var reference to the frmSub, and can do
anything you want..such as:

frmSub.Requery

msgbox "num recs = " & frmSub.RecordSetClone.RecordCount

Using the above means you don't have to start putting some code in each
sub-form control event to set a reference. In fact, a large portion of menu
code should be written to pick the up active screen and control. If you code
this way, you will again find often that the same menu code can be used for
more then one screen/form.

What about sub-sub forms? Well, even better is to code a general function
that ALWAYS returns the form ref. That way, the forms menu code will work if
it is the main form, a sub-form..or even sub-sub-form (no need to code for
sub forms....everything should work if the form is a main form...or in fact
is a sub-form, or sub-sub form).

You thus can code like:

dim frmMyForm as Form

set frmMyForm = GetActiveForm

msgbox "num recs = " & frmMyForm.RecordSetClone.RecordCount

The function to "walk" and drill down to the current form (or sub form, or
sub sub form...in fact deep as you want) is based on the above example
code....

Public Function GetActiveForm() As Form

Dim aForm As Form

Set aForm = Screen.ActiveForm

Do While aForm.ActiveControl.ControlType = acSubform
Set aForm = aForm.ActiveControl.Form
Loop

Set GetActiveForm = aForm

End Function

Using the above eliminates the need for global, eliminates the need for you
to set a forms ref in the forms controls enter event. And, the above code
also works when NOT using a sub-form! And, the code is portable, and you can
safely have multiple copies of the form loaded....

I not trying to shoot down you example advice. However, the only
thing that I am pointing out here is that when you start to write
and use menu code, then you should (and will) use the screen object
(screen.ActiveForm, Screen.ActiveContorl) as a general approach
to solve menu code problems.

And, another great tip here is if you use a common naming convention for
your menus code (those public functions you place in a form that the menu
calls), then you often can use the SAME menu for more then one form.
Remember, menus try and run the function name (public) in the forms code
BEFORE the modules are searched for the function name. (simply put: whatever
form has the focus is the FIRST place the function names resolve to..and
THEN the modules are searched). Also, do NOT fully qualify the forms ref
when you do this..as there is a bug. You can read about it he

http://www.mvps.org/access/bugs/bugs0048.htm

I do think your tip about using the sub-form controls on-enter event is
useful. Often, to make menu code work with several tabs and sub-forms, then
setting a ref in the on-enter event is a good way to resolve which sub-form
to use. However, don't forget about the screen object either!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn