The "calling" three times is a known bug.
The solution is to remove the "forms" qualifier from the menu "on - action"
So, in your formA, you have some code like:
(note that it MUST be declared as public!)
Public Function CustomerReview()
DoCmd.OpenForm "customer_review", , , , , , Me.CompanyName
end if
Now, when you press the button on formA, have it go:
Public Sub customer_review_Click()
CustReview
End Sub
And, for the code behind the menu , in the on action, you also want the
"CustRevew" function to run. So, in the on action, just run the above code,
put the following in the On-action:
=CustReview()
So, the "trick/fix" to the problem is to NOT use a full forms qualifier in
the above function name (if you do, then it runs 3 times!).
Note that if the current form with the focus does NOT have a PUBLIC function
(called CustReview in our example), then a public function with that name
from a standard module is searched for. And, of course, if no public
function is found, then you get a error message.
The above fact of the function name being looked for in the current form
that has the focus means you exploit this fact to create custom menus that
operate on more then one form, and all you have to do is create a "common"
naming scheme for each form "function". For example, you might have a custom
print button. On form it might print a invoice, and on a anther form, just
print the current record. So, each form can have a Pubic Function called
Public Function MyPrint()
Then, in your custom menu, in the on-action, you would thus want to run the
above code, so in the on-action, you put:
=MyPrint()
While we are speaking about custom menus, anytime you start using menus,
then you tend to start writing more "generic" code.
So, in stead of your formA "passing" company name to the formB, why not in
forms "b" code make a reference to the previous form. The code would involve
having a module level variable in form b that holds the previous form ref,
like:
Option Compare Database
Option Explicit
dim frmPrev as form ' prevoius form
' now, in the forms on-open (and in fact, you can EVEN still do this as LATE
as the forms on-load event)...hum, in fact, lets use the forms on-load
event, as on-open can be used here, but is inappropriate.
So, here is what we do:
set frmPrev = screen.ActiveForm
Now, any where in form B, we can use ANY value of formA.
To requery formA in form b, we go:
frmPrev.Requery
To force formA to write data to disk, we can go:
frmPrev.Refresh
And, to get the company name, we can go:
frmPrev!CompanyName
To get the persons last name
frmPrev!LastName
So, really, don't pass a single value..but just get the 2nd form to make a
ref to the previous form. It eliminates having to use open args, gives you
FULL control of the previous form, and the code is completely generic, and
calling forms do NOT even have to "set", or pass a parameter..the whole
thing just works.
For a LOT of general menu code, then again I pick up the active screen.
Often, (if not most of the time), you code you call will need to pick up
some information about he 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 number has been
generated before printing. And, the Refresh forces a disk write if in fact I
do need the invoice number. And, in addition the above clip also passes
the currently selected sub-form item that the invoice print form needs.
INtersting, but that above sample clip of code *should* have been palced
in the forms module, and then we could have just used "me.Whatever"
So, there is no need to pick up the active screen...and you can use me. as
you
always used. if the code is in the forms module.
If you want to see some sample menu bars, and why I use them, you can read
the following:
http://www.members.shaw.ca/AlbertKallal/Articles/UseAbility/UserFriendly.htm