running code from a menu

[jatfill]

I'm completely stumped on this, any help would be greatly appreciated. I don't currently use switchboards, I prefer menu-driven commands, as I can keep them consistent throughout my database, etc.

I would like to be able to run VBA from a menu item instead of creating a macro for each custom menu item, etc. but I'm not even sure if that's possible.

For example, I would like a menu command that simply runs the following:

DoCmd.OpenForm "frmName", acNormal, "", "", acAdd, acNormal

but Access menu won't take this as a custom command... it returns an error. I have one form I use for data entry and editing, and it would be much better if I could control how the form is opened (data entry mode, filtered, etc)


Any thoughts would be greatly appreciated... thanks!

 

[Robert Dunstan]

Hi,

You need to place your code in a function i.e

Function yourFunctionName()
DoCmd.OpenForm "frmName", acNormal, "", "", acAdd, acNormal
End Function

You then need to assign that function to your menu command.

HTH
Rob

 

[axa]

jatfill,

one workaround for this is as follows. a custom Menu Button's OnAction property can contain a call to a macro (as you know), and it can also call a Public Function that you may have defined in a vba module like so:

=MyMenuFunction()

and you can design your function(s) to accept arguments which can help to make them more useful...
__________________________________________

Public Function MenuOpenForm(strFormName As String, blnAsNew As Boolean)
    If blnAsNew Then
        DoCmd.OpenForm strFormName, acNormal, "", "", acAdd, acNormal
    Else
        DoCmd.OpenForm strFormName, acNormal, "", "", acEdit, acNormal
    End If
End Function


...
your custom menu command could then be configured like so

=MenuOpenForm("frmName",True)
__________________________________________

you could create yourself a library of useful menu helper functions like the one above, which should make your life a bit easier!

Hope that helps

axa


ps- robert beat me to it, while i was writing the above

[This message has been edited by axa (edited 08-15-2001).]

 

[Robert Dunstan]

May be my typing is not as slow after all!!

 

[SharonC]

I realize this message is over 3 years old, but I'm searching for something similar to this answer. If I wanted to send the acFormView property to the function would it be a integer? And if so, what is the number (and where would I find it) for acFormDS?

jatfill,

one workaround for this is as follows. a custom Menu Button's OnAction property can contain a call to a macro (as you know), and it can also call a Public Function that you may have defined in a vba module like so:

=MyMenuFunction()

and you can design your function(s) to accept arguments which can help to make them more useful...
__________________________________________

Public Function MenuOpenForm(strFormName As String, blnAsNew As Boolean)
****If blnAsNew Then
********DoCmd.OpenForm strFormName, acNormal, "", "", acAdd, acNormal
****Else
********DoCmd.OpenForm strFormName, acNormal, "", "", acEdit, acNormal
****End If
End Function


...
your custom menu command could then be configured like so

=MenuOpenForm("frmName",True)
__________________________________________

you could create yourself a library of useful menu helper functions like the one above, which should make your life a bit easier!

Hope that helps

axa


ps- robert beat me to it, while i was writing the above

[This message has been edited by axa (edited 08-15-2001).]