Unusual behavior?

[Ben]

Hi group -
I came across something that seems unusual today, and I'm
hoping somone can clarify what is going on 'behind the
scenes'.
In Access97 I have a custom menubar for a form, the menu
options call a macro that calls a function on the form.
(Normally I use VB6 or .NET so this whole process was
rather frustrating) In the macro I had a RunCode action
and the function Forms!myForm.myFunction. This worked
just fine, bit noticed that the function was being called
3 times, and yet is was only on the stack 1 time !?! This
didn't hurt anything, but it annoyed me. I started
looking around and Access help says that if the macro is
called from a menubar for a form Access searches for the
function in the form's code module 1st. So, I tried de-
qualifying the reference to just myFunction - start over
and the code only runs once.
What's going on here? Does Access work backwards along
the function name arguement so with Forms!
myForm.myFunction RunCode found it 3x? Even more spooky,
I'd coded the functions with no return value or type
since Access ignores it in this context, when at first I
just added myFunction() as Integer the code only ran
twice.
Am I crazy or does this all seem a little strange.
TIA for any input.
Thanks
Ben

 

[Albert D. Kallal]

That is a known bug. And the solution is to NOT quality the form.

This bug exists in a97, a2000 and a2002.

You are correct in that leaving out the forms ref means the code is only
called once.

in fact, you don't even have to use a Runcode..

You can place

=(YourRoutine())

In the On action. So, you don't even need a macro.

In fact, I often call all kinds of code from a module. Just make sure the
code in the module is a public function.

So, I can call a public function in a module like:

=AskSwtichBus()

The code in the standard module can pick up the form name as follows:

Public Function AskSwitchBus(SingleName As Boolean)

' ask's user what bus to switch to
' called from active frmBook

Dim i As Integer

Dim lngTourId As Long
Dim strName As String
Dim MyAForm As Form
Dim MySubForm As Form
Dim lngSwitchNames As Long
Dim lngBookingId As Long
Dim lngBusId As Long

Dim rstFriendsList As Recordset
Dim strMySql As String

Set MyAForm = Screen.ActiveForm
Set MySubForm = MyAForm.tblBgroup_subform.Form

lngTourId = MyAForm!tour_id

lngBookingId = MyAForm!ID
lngSwitchNames = MySubForm!ID
lngBusId = MySubForm!bus_id

Note how I picked up the active form name....

 

[Ben]

Albert-
Thank you once again for the information. I didn't
realise that I could put the function name in the
OnAction of the menu item. I like that much better (hate
macros, hate, hate, hate - love code, code good)
In your example, where does the SingleName parameter come
from if you call it from a menu item's On Action? The
obvious answer is the parameter box, are you OOL if you
want to send 2+ arguments?
I gues I'm just a bit frustrated with menus in Access, VB
is better, but menus are still a bit of a red headed
stepchild it seems, no matter where you go.
Once again, thank you Albert, and all the MVPs who
volunteer thier time & brainpower to save us mortals from
suicide by 'banging head against wall'.
Ben

 

[Albert D. Kallal]

That was my fault....

I should have put:

=AskSwitchbus(True)

You can easily add parameters to your functions that you call. Often, this
allows a good deal of flexibility.

Note that in that switchbus example I picked up the active form name since
that code was OUTSIDE of the form. Often, for some general type code, you
can pick up the form name, and thus make the code function for more then one
form.

However, in some cases, the code does belong in the form, and thus you don't
need that code to pickup the form name. Just remember to NOT include the
form qualification in your menu bar when you do this (and make sure the
forms code is defined as public).

This means again that you can actually use the same menu bar for more then
one form, and the code functions just have to be the SAME for each form!