Menu Coding

[Neal B. via AccessMonster.com]

I have created a custom menu with the code from the button on my form in a
module. I'm using the exact same coding for my menu item as the button on my
fom, only replaced with public, function instead of sub and focus on active
form. I'm getting ..."external name not defined".. error on [tblJOProfile.
Comanyid] below. Here's my code:

Public Function ListJOs_Click()
Dim frmActive As Form
Set frmActive = Screen.ActiveForm

DoCmd.OpenForm "frmListJOs", WhereCondition:="tblJOProfile.Companyid=" &
[tblJOProfile.Companyid]

End Function

Where I use the button on my form it works perfectly. Same code, just from
the menu. Can anyone see where I'm missing something??
Thanks,
Neal

 

[Albert D.Kallal]

Two things:

Since the code belongs to a particular form, then why not just put the code
in the forms module?

You can use:

Public Function ListJos

DoCmd.OpenForm "frmListJOs", WhereCondition:="Companyid = " & me!id

end function

Then, in your menus on-action setting, you put


=ListJos()

The above should work.

Furhter, does you code compile as is?

If for some reason, you wan to use a public function in a standard module,
then

Public Function ListJOs_Click()
Dim frmActive As Form
Set frmActive = Screen.ActiveForm

DoCmd.OpenForm "frmListJOs", WhereCondition:="Companyid = " &
frmActive.Companyid

End Function

The whole idea of the above picking up the active form is so that you can
use the active form in your code. You go to all this trouble to pick up the
active form, and then in your code complete ignore all the efforts you just
made. Also, that openform line is one line..but is wrapped as posted.

Anyway...either of the above approaches should work.

 

[Neal B. via AccessMonster.com]

Thank You Albert ! It works great now.

Two things:

Since the code belongs to a particular form, then why not just put the code
in the forms module?

You can use:

Public Function ListJos

DoCmd.OpenForm "frmListJOs", WhereCondition:="Companyid = " & me!id

end function

Then, in your menus on-action setting, you put

=ListJos()

The above should work.

Furhter, does you code compile as is?

If for some reason, you wan to use a public function in a standard module,
then

Public Function ListJOs_Click()
Dim frmActive As Form
Set frmActive = Screen.ActiveForm

DoCmd.OpenForm "frmListJOs", WhereCondition:="Companyid = " &
frmActive.Companyid

End Function

The whole idea of the above picking up the active form is so that you can
use the active form in your code. You go to all this trouble to pick up the
active form, and then in your code complete ignore all the efforts you just
made. Also, that openform line is one line..but is wrapped as posted.

Anyway...either of the above approaches should work.