Using VBA to Clear Toolbar Property in Access 2010

[Dan Hugos]

I am trying to use VBA to set the Toolbar property of Access 2010 forms to nothing in various databases.

In other words, I don't want the name of a toolbar in the Toolbar property of various forms. I want it to be nothing.



Each database was created in an old version of Access and the reference to a toolbar is no longer needed. It's being handled with table USysRibbons and the QAT. That's why I want there to be nothing in the Toolbar property of each form in a database.

There are a lot of databases, so rather than opening each form and using the Properties pop-up and deleting the Toolbar property, I want to simply automate this process in VBA. Seems like it ought to be easy.

So I'm cycling through the forms in each database and saying Forms(frm.name).toolbar = "". Then I close it with save turned on.

It doesn't work - the name of a toolbar is still there in the Toolbar property. What am I doing wrong?

[amrut]

Are you opening the form in design view ?
Psedo code -
DoCmd.OpenForm frm.Name, acDesign
Forms(frm.name).toolbar = ""
DoCmd.Close acForm, frm.Name, acSaveYes

This is from vba help for toolbar -

When opening a form in Microsoft Access 2010 that is part of a database that was created in an earlier version of Microsoft Access, the specified toolbar will be displayed differently depending on the current settings of the AllowFullMenus and AllowBuiltInToolbars properties. If the AllowFullMenus and AllowBuiltInToolbars properties are set to False, the specified toolbar will replace the ribbon as the default set of commands available to the user. If the AllowFullMenus or AllowBuiltInToolbars property is set to True, then the specified toolbar is displayed on the ribbon Add-Ins tab.

[Dan Hugos]

Hello Amrut,

Many thanks for the quick reply. Here is my code:

DoCmd.OpenForm strFormName, acDesign
Set frm = Forms(strFormName)
If frm.Toolbar = "User FormView" Then
Debug.Print frm.Name
Forms(frm.Name).Toolbar = vbNullString
End If
DoCmd.Close acForm, strFormName, acSaveYes

I step through the code and everything is working correctly, names assigned properly, and no errors are raised. Then I go manually into a form that came up on the debug.print list and bring up Properties, and alas, there is User FormView still in the Toolbar property, instead of nothing.

Regards, Dan

[ssanfu]

Could there be code in one of the form events that resets the Toolbar property to "User FormView"?

In the IDE, press ctl-F, enter "Formview" (no quotes), select "Current Project", click button "Find Next".

[Dan Hugos]

I'll check it out tomorrow for sure. That's a good thought, though all the forms in every db would have to be doing the same thing in that regard.

Personally, I think it's a bug. It looks to me like each form needs to be opened manually, and the property set to nothing.

[Dan Hugos]

Can anyone quickly attempt to change the Toolbar property of a form in Access 2010 or 2013? If you're successful, please let me know.

Meantime, I guess you have to do it manually.

Microsoft - if you're reading this, personally, I think you have a bug. Please work some magic.

[amrut]

Can you attach a sample db in 2010 with toolbars ?

[Dan Hugos]

Hello Amrut,

That's just it - there are no toolbars, because it's an Access 2010 database. I have a QAT for what used to be toolbars in Access97. It's set in the USYSRibbons table and works fine, replacing the functions that the toolbar used to handle.

Hence, I just want to cycle through the forms, which were designed in Access 97. Where I see that the Toobar property is set to something (in my case, the toobar is set to ls_menubar), then I want to remove it and just have it set to nothing at all.

The code above is able to recognize which forms have this property set, and it doesn't error out when it sets it to "", ie nothing. But then when I go into the form manually, in turns out that the Toolbar property is still set.

So it would seem like you have to do manually, what you ought to be able to do programmatically in VBA. I can set other properties programmatically, just not the Toolbar property.

As I said: Microsoft, I think you have a bug! But if not, what am I doing wrong?

[ssanfu]

Seems like you are doing everything right.
At this point, would need to have the dB to troubleshoot

[Dan Hugos]

I've come across about three links that have people asking the same question, and they get no answer. One MVP said he was reporting it to MS so I guess indeed it is a bug. You can just make a db in Access 2010, create a form, set the Toolbar property equal to any string. Then try to change it or set it to nothing programmatically. I'm unable to do it. If you can try it yourself, let me know if you're successful.

[ssanfu]

I've come across about three links that have people asking the same question, and they get no answer. One MVP said he was reporting it to MS so I guess indeed it is a bug.

Thanks for the info. Good to know.