Switchboard: Run a Function and pass a variable?

[ryan.gillies]

Hi all, quick question here.

I've created functions in the past and been able to run them from the Switchboard by choosing Run Code from the dropdown and typing the name of the function.

Now I have two functions I want to call from the Switchboard:

Public Function OpenTable(t As String)
DoCmd.OpenTable t, acViewNormal, acEdit
End Function
 
Public Function OpenQuery(q As String)
DoCmd.OpenQuery q, acViewPivotTable, acReadOnly
End Function

I've tried entering OpenQuery("qCases") and OpenQuery(qCases) but neither open the query for me, I just get an error stating "There was an error executing the command."

Any thoughts? Thanks!

 

[boblarson]

Access doesn't like function names using Access Reserved Words. So, since there is already something in Access (commands) named OpenQuery and OpenTable, you might have better luck in using

Public Function [B]m[/B]OpenTable(t As String)
DoCmd.OpenTable t, acViewNormal, acEdit
End Function
 
Public Function [B]m[/B]OpenQuery(q As String)
DoCmd.OpenQuery q, acViewPivotTable, acReadOnly
End Function

And then calling it using the run function and passing the value:

mOpenQuery("qCases")

 

[ryan.gillies]

That was a no-go unfortunately - I read on another discussion board somewhere last night that its impossible to pass a value through the switchboard.

If anyone can prove otherwise, I'm all ears!

 

[ryan.gillies]

Hi all, I resolved it in the end with a bit of a fudge!

I found this article which refers to adding a new const into the Switchboard code in order to interpret a new function.

However this didn't really solve my problem, as although it would enable the code to run, it doesn't help ordinary folks managing the Switchboard, as they have to edit the Switchboard table manually, rather than from the Access front-end. So I came up with an alternative.

I changed the following code in the Switchboard code:

' Run code.
Case conCmdRunCode
Application.Run rs![Argument]

To this:

' Run code.
Case conCmdRunCode
Eval rs![Argument]

This enables anyone to pass a variable, or variables, to a function from the Switchboard Manager using the following in conjunction with Run Code:

RunMyFunction()     ' When no variables are involved
RunMyFunction("var")     ' When variables are involved

Simple! I've struggled to find this answer everywhere, so I hope this helps people. Now I can use it to create functions like the one's in my post above to open tables or queries or whatever in the way I want, without creating hundreds of vaguely similar macros or functions!

 

[nanscombe]

I was intrigued by this and decided to have a little play.

This uses a Switchboard without any changes and only uses a single function called switchboardEnhanced(), the words in the option labels do the rest.

What I attempted was to grab the captions of the switchboard and the option label for the button pressed and built it up from there.

The function, switchboardEnhanced(), grabs these values and turns them into Arrays using a "-".

This creates two Arrays:

arrSwitchboard(), where arrSwitchboard(0) holds the caption and any words after a "-" are ignored.

arrOptionLabel(), where strOptionLabel(0) holds any words before a "-" in the caption, which can become the name of an Access object, and strOptionLabel(1) holds any words after a "-" in the caption and can be passed as a parameter to an Access object.

If you know a bit of VBA you can even add you own commands in a User defined section of the code. Each menu will give a value based on a lowercase version of SwitchboardCaption/OptionLabel, ie "main switchboard/option 1".

Feel free to have a nose around.