Get popup menu button to execute VBA function ON A SUBFORM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

In short:
I can't get a popup menu button to execute a VBA function on a subform. How
can I achieve this?

Full story:

I have a subform which I reuse in several places. It's a datasheetview with
15+ columns. To make this usable I would like to offer the user the
possibility to select from three pre-defined sets of columns.

To allow the user to make this selection I made a popup menu for the subform
with three buttons and set the buttons 'On Action' like so:

=ShowColumns("A")
=ShowColumns("E")
=ShowColumns("F")

and a public function ShowColumns(choice as String) on the subform

This works very well if the subform is not used as a subform but as a normal
form. If used as a subform, Access reports that it cannot find the requested
function when the popup menubutton is clicked.

I suppose this is because the subform is not added to the Applaction.Forms
collection and therefor the function is not globally available or something.

Ofcourse I could use a global function which goes through all open forms
looking for the right subform and then passes the function on... But I'd like
it easer than that.

Any ideas anyone?

Kind regards,
Ries Spruit
 
Hello,

In short:
I can't get a popup menu button to execute a VBA function on a subform. How
can I achieve this?

Full story:

I have a subform which I reuse in several places. It's a datasheetview with
15+ columns. To make this usable I would like to offer the user the
possibility to select from three pre-defined sets of columns.

To allow the user to make this selection I made a popup menu for the subform
with three buttons and set the buttons 'On Action' like so:

=ShowColumns("A")
=ShowColumns("E")
=ShowColumns("F")

and a public function ShowColumns(choice as String) on the subform
Click to expand...

use a public function and give it also the form like
public function ShowColumns(choice as String, frm as Form)

with frm
Select Case choice
case "A"
.controls("MyControl").visible=true
.......
end Select

end with
end sub
 
Not quite there yet, thanks for your reply though.

I Presume this would mean that the popup button's 'On Action' setting would
become:

=ShowColumns("A",Me)

Unfortunately, when executing, it reports that 'The automation object Me is
not part of the object' (translated from Dutch).

I am not sure what kind of variables can be used in that 'On Action' setting
of a popup button. I can't find too much info on that subject.

Thanks again,
Ries
 
The easiest way seems to get a reference to the subfrom via

Screen.ActiveControl.Parent

in the global public function an then pass on the function call to the
subform. This still seems like a work-around though...
 
Not quite there yet, thanks for your reply though.

I Presume this would mean that the popup button's 'On Action' setting would
become:

=ShowColumns("A",Me)

Unfortunately, when executing, it reports that 'The automation object Me is
not part of the object' (translated from Dutch).
Click to expand...
try me.Form
 
Back
Top