How to set AllowFullMenus with VBA (1 Viewer)

[sharpnova]

I found some code online to do this but nothing seems to work.

When I execute the line of code: CurrentDb.Properties("AllowFullMenus") = True, I get no errors. So I know the property exists.

Yet it doesn't do anything when I set it. I'm trying to allow/disallow full menus based on user's security level.

I found code here that doesn't work:
http://www.utteraccess.com/forum/lofiversion/index.php/t1878087.html

But this code doesn't work at all. Mainly because there is no 'HasDAOProperty' function in vba.

 

[Ari]

Hi

Dim prpNew As Property
On Error Resume Next
CurrentDb.Properties("AllowFullMenus").Value = true
If Err.Number = 3270 Then
   Set prpNew = CurrentDb.CreateProperty("AllowFullMenus",dbBoolean,true) 
   CurrentDb.Properties.Append prpNew 
   Err.Clear
End If

Sucess!

 

[GregRun]

The code you posted works.

Try doing this in the immediate window:
1) Type this and hit enter:
Currentdb.Properties("AllowFullMenus") = True
2) Type this and hit enter (don't forget the question mark):
?Currentdb.Properties("AllowFullMenus")
3) It should print "True".
4) Check the database properties and confirm that the Allow Full Menus box is checked
5) Repeat steps 1-4 using False instead of true

I just did this procedure on my Access 2007 installation and it worked perfectly. If this doesn't work for you for some reason, let me know.

 

[sharpnova]

I see that if I manually change the properties in the immediates window as you said, then it indeed toggles them. But the effects aren't seen until the next time the user launches the database.

I was actually hoping to toggle the property (not just a startup property) while the database was open.

Is this possible?

 

[GregRun]

You'll notice that when you change that property manually in the options menu, it tells you that the change will be made on restart... To my knowledge, there is no way to see the change once the form is open.

What is your purpose for hiding the menus once the database is already open?

One way to use this property effectively is to hide the menus from everyone but you. The best way to do this:

Step 1: Write a function to turn menus off and on.

Public Function InitMenus () As Boolean
    If CurrentUser = "YourUserName" Then
        Currentdb.Properties("AllowFullMenus") = True
    Else
        Currentdb.Properties("AllowFullMenus") = False
    End If
End Function

Step 2: Create a macro to call that function
Action: RunCode
FunctionName: InitMenus()

Step 3: Save the macro and name it "AutoExec"

 

[sharpnova]

Unfortunately this method isn't any different since even after the Autoexec runs and it authenticates the user and sets the menu property accordingly, no effect will be seen until restarting the database.

What I wanted to do was allow manager-level users to go "analyze this report with excel" on generated reports.

But I didn't want non manager-level users to be able to access the Tools menu at all.

For now I'll be allowing everyone access. It seems the only solution will be to write VBA to emulate the "analyze this report with excel" functionality so that the Tools menu can be disabled for everyone.

 

[GregRun]

I tested this before I posted it. That is why is specifically said to use a Macro instead of just putting it in the onOpen of a form. I tried that and it didn't work. When you call the function from an "AutoExec" macro it will apply the change when you open the database. That (from my limited testing) seems to be the only way to accomplish what you're looking for.

 

[sharpnova]

I did test your method. Exactly as you said.

I created a function that did a CurrentDb.Properties("AllowFullMenus") = True

I made an autoexec macro that runs this function.

I even put a msgbox after the property set to make sure the function was being called and it was.

This was no different than before. It toggles the property and you can't see the effects until you restart again.

 

[GregRun]

What version of Access are you running?

 

[GregRun]

Huh... just tested it again and you're right, it did not work... sorry.

 

[sharpnova]

Access 2003 (11.8321.8333) SP3

Oh well. I'll just allow Tools access for everyone for now. (Most of the users are computer literate enough that I need not worry)

Later on I'll come back and create "analyze with excel" functionality of my own so that Tools doesn't need to be enabled for anyone.

Thanks for your help

 

[GregRun]

I'm not familiar with "Analyze with Excel." What is it? Maybe we could help you with that. As far as I can tell it is not a feature in Access 2007 so writing your own procedure would probably be best for forward compatibility anyway.

 

[sharpnova]

When a report loads, I go to Tools -> Office Links -> Analyze It with Microsoft Office Excel

Choosing this option launches excel with an excel version of the report.

And I think:

DoCmd.RunCommand acCmdOutputToExcel could work.. but the problem is it has no argument for which object to export to excel so I don't understand how to use it.

 

[Trigger9008]

[...]DoCmd.RunCommand acCmdOutputToExcel could work.. but the problem is it has no argument for which object to export to excel [...]

I was just poking around and don't mean to revive a dead thread but the following code may help with exporting to Excel:

    DoCmd.OutputTo _
            ObjectType:=acOutputQuery, _
            ObjectName:="Query or Report Name", _
            OutputFormat:=acFormatXLSX, _
            OutputFile:="Desired Output File Name.xlsx", _
            AutoStart:=True, _
            OutputQuality:=acExportQualityPrint

I hope this helps someone out there.

 

[gemma-the-husky]

Because the original change needs a close/reopen, it should work if the user does that, ie, an "authorised" manager gets the setting changed, closes and reopens, and then has the correct setting.

The same thing happens in reverse. The first time a normal user uses the version, he will see the full version. The second time, it is back to normal

Slightly irritating, but probably not the end of the world

I suppose if your startup code forces the "locked" version back again, the "unlock" never works!