LEARN MS-ACCESS TIPS AND TRICKS: Menus

Showing posts with label Menus. Show all posts

Restoring disabled Full Menus Access2007

a.p.r. pillaiSeptember 01, 2011Access2007, How Tos, Menus, restore No comments

Introduction - Access 2003.

When you develop an application, even if it is for your own use, always consider that each object you design is for someone else use. Then only we will be serious about giving a closer look at each control’s function, their strength, and drawbacks. Others may not treat each control on a Table, Form, Report, etc., as we visualize their usage, the Users may try to enter garbage into the data entry fields or may try to implement their own ideas, if they can, in the design of a Control or Form and so on.

Let us take an example of a Date-of-Birth (or an Invoice Date) data entry field on a Form. If we leave this field open to accept any date value, then it is likely that the user may make mistakes like entering a future date. To prevent such eventualities we must enable the built-in Validation checks on the value entered into the date field and warn the user about the mistake and force him to enter a valid value.

Data Field Validation Checks.

We can do this by setting the Validation Rule and Validation Text property values of the Date Field. The Validation Rule property of the date field can be set with the expression: <Date() to accept date values earlier than Today in the date-of-birth field. But, this expression is good only if we are prepared to accept date-of-birth values earlier than 100 years or more, otherwise we must set a lower bound value too, like >=(DateAdd(“yyyy”,-100, Date()) and <Date(). To warn the user about the data entry error, the Validation Text property can be set with a message text like Future date is invalid and Age Limit 100 years. This is better if you implement it on the Table itself rather than on the Form.

Preventing Changes to Important Objects.

It is equally important that Users are kept away from modifying objects like Forms, Queries, Reports, Macros, and VBA Programs. This is where we consider implementing User-level Security in Microsoft Access. The user-level security feature is available in Microsoft Access2003 or earlier versions only.

Keep Users' Freedom within Limits.

Another way of preventing users from straying out is to create Custom Menus and Custom Toolbars for the Application and disable all built-in Menus and Toolbars so that users are not able to get into object designs through the built-in Menus. If you have designed customized Menus and Toolbars, then you can disable the built-in Menus by removing the Check-marks in the Access Options.

Caution: Take a backup of the database before making the following changes otherwise you may not be able to get back into the database for making design changes yourself later.

Select Office Button - -> Access Options - -> Current Database - -> Ribbon and Toolbar Options

Remove check-marks from the following options:

Allow Full Menus
Allow Default Shortcut Menus
Allow Built-in Toolbars

After removing the check-marks you must close and reopen the database. Now, the customized Menus and Toolbars you have created are only available to the User. But, unfortunately even now your database objects Forms, Reports, etc., are not safe from unauthorized changes. It is true that they cannot right-click on an object in the Navigation Pane and go into the design view, but they can if they select the VBA Module's view from the Navigation Pane, browse for a Form/Report, select it and then select View Object option.

In the VBA Navigation Pane, Forms and Reports with VBA Class Module behind them are visible to the Users. You can hide those objects by setting their Hide Option by right-clicking on the database Navigation Pane. If you don't want Users to go into the Module View, through the Navigation Pane then you may remove the check-mark from the Display Navigation Pane option along with the Menu options explained above.

Unless the User is too smart you can consider your Database Objects safe, but not safe enough from a smarter one. If someone familiar with the Keyboard Shortcuts can open the VBA Module wide open by pressing ALT+F11. You can write the rest of the story yourself from there. Even If you keep the Navigation Pane of the VBA window hidden it can be opened by clicking on the Object Browser button on the toolbar.

Since the User-level Security is not available in Access2007 and later versions, these are some of the methods available to you to implement security within an Access Database.

Once you remove the Full Menus Option it will be difficult for you to get back into the database for making changes to Forms, Reports, etc. That's why I suggest taking a backup, to be on the safe side.

Even during design time make it as a regular practice to take back-ups of the database, to save the work done so far, before starting with latest changes. If the database somehow got corrupted (or deleted by mistake) at any stage of design time you are safe from totally losing your database.

Restoring Full Menus Back.

If you want the Full menus back again we can play a small trick to get back into the database and put the check-mark back into the Allow Full Menus Option, without going through directly from the Office Button Menu.

Open the database.
Press ALT+F11 to display the VBA Window
Press CTRL+G to display the Debugging Window (Immediate Window).
Type the following command in the Debug Window and press Enter Key:
```
CurrentDb.Properties("AllowFullMenus").Value = True
```
Close and re-open the database again.

Now, you can approach Access Options through Office Button (top left corner) and make changes to Options there.

Technorati Tags: Microsoft

Earlier Post Link References:

Create Menus with Macros-2

a.p.r. pillaiDecember 11, 2010Forms, Menus, Menus and Toolbars, toolbar No comments

Continued from Create Menus with Macros. - Access 2003

Continued from last week's Article: Create Menus with Macros, please go through it before continuing.

Last week we learned how to create a simple Shortcut Menu for Forms with Macros. This week we will create a Toolbar Button and a Menu Bar item through Macro-based Menus.

New Macro: macProcess.

But, before all that, let us make a little modification to our earlier shortcut menu to add a menu group item added with a few options.

Open a new macro in the design view.
Select MsgBox from the drop-down list in the Action Column.
Type Proceed with Report Data Processing? In the Message control under the Action Argument list.
Select Critical from the drop-down list in the Type control.
Type Report Processing in the Title control.
Note: This macro is supposed to contain a series of actions; like running action queries (Make-Table, Append, Update, Delete, or running of other macros or VBA Routines) to prepare data ready for Reports. Here, we are simply adding a message box to display a message instead. You must create Menus with actions as per the requirements of your Application.
Save the Macro with the name: macProcess and close it.
The macSubMenu Macro.
Click on New to open another new macro in Design View.
If the Macro Name Column is not visible then select Macro Name from View Menu.
Type &Form in the Macro Name column of the first row and select OpenForm from the drop-down list under the Action Column.
Select an existing Form in the From Name control using the drop-down control.
Type Process &Data in the Macro Name column in the second row and select the RunMacro in the Action Column.
Type macProcess (or select it from the drop-down list) to insert the process macro that we have created in step 6.
Type &Report in the Macro Name column in the third row and select OpenReport in the Action column.
Select an existing Report from your application in the Report Name control.
Select Print Preview in the View control.
Save the macro with the name macSubMenu and close it.

Modify Macro: mcrShortCut
Open the macro mcrShortCut that we have created last week.
Type MenuGroup in the next row (as shown in the image given below) and select the AddMenu option from the drop-down list under the Action column.
Type Form/Report in the Menu Name control below the Action Arguments.
Type macSubMenu (or select from the drop-down list) in the Menu Macro Name control.
Save and close the macro mcrShortCut.
If you remember on which Form you have attached the earlier macro Shortcut Menu last week, then you can open that Form in normal view and right-click on the Form to display the Form/Report Sub-Menu we added to the earlier menu. If You don't remember then open one of your existing Forms in Design View.
Display the Property Sheet (View - -> Properties or press ALT+Enter).
Click on the Other Tab.
Set the Shortcut Menu Property value to Yes.
Set the Shortcut Menu Bar Property value to macMenu (you can select the macro from the drop-down list also).
Save and close the Form.

The Shortcut Menu Trial Run.
Open the Form in Normal View.
Right-Click on a field to display the Shortcut Menu. The new Group Menu that we have added will appear at the end of the Menu with the description Form/Report.
Place the mouse over the Form/Report menu group to display its Sub-Menu items.

The menu definition now remains in Macros and we are directly using it on Forms. But, we can convert the Menu Macro into a regular:

Menu Bar (like File, Edit, etc.) OR
Toolbar OR
Shortcut Menu.

If you would like to convert this macro menu into a Menu Bar then we must make a small change in the macMenu macro.

Converting Macro into Menu Bar.

Open macMenu in design view.
Type myMenu in the Menu Name control under Action Arguments.
Save and close the macro.
Click on the macMenu macro to select it.
Highlight the Macro in Tools Menu.
Click on Create Menu from Macro Option.
A Menu Bar will appear on top with the label myMenu.
Click on myMenu option to display the menu options, which we have created macros.

Note: You should not attempt to convert the same macMenu to a Toolbar Menu; a name conflict will take place.

Quick Toolbar Button.

Tip: To create a quick Toolbar Button with the same set of macros, do the following:

Make a copy of the macros: macMenu, mcrShortCut, and macSubMenu and name them as mac_Menu, mcr_ShortCut, and mac_SubMenu.
Open mcr_ShortCut macro in design view.
Click on the last line and change the Menu Macro Name control value to mac_SubMenu, save, and close the Macro.
Open the mac_Menu macro in the design view.
Change the Menu Name control value to myToolbar.
Change the Menu Macro Name control value to mcr_ShortCut, save, and close mac_Menu Macro.
Click on the mac_Menu Macro to select it.
Highlight Macro in Tools Menu to display its Options.
Click on Create Toolbar from the Macro option to convert the macro into a Toolbar; the myToolbar button will appear near the existing Toolbar with the label myToolbar.
You may click on it to display the Menu.

Create Menus with Macros

a.p.r. pillaiNovember 29, 2010Forms, Macro, Menus, Menus and Toolbars 1 comment

Introduction - MS-Access 2003.

We have learned how to create Menus and Toolbars through Toolbars - ->Customize options, in earlier Posts. If you would like to have a second look at them, then the links are given below:

Calendar and Toolbars - Access 2003
Custom Menus and Toolbars - Access 2003
Custom Menus and Toolbars-2 - Access 2003
Inputbox and Simple Manus

There are other methods to create simple custom menus for Forms/Reports and use them with your Applications. We are going to learn how to create a Shortcut Menu with Macros and attach it to a Form.

Sample Demo Run.

Select the Macro Tab on the Objects pane.
Click New to open a new Macro in Design View.
If the Macro Name Column is not visible, then click on the Macro-Name Toolbar Button (with xyz label) to display the Macro Name Column.
Type Clo&se under the Macro Name column in the first row.
Select Close from the drop-down list of Action.
Type &Preview in the second row under Macro Name.
Under Action select the OpenReport option from the drop-down list.
In the Property Sheet under Action Arguments select a Report from your list of Reports (I have selected Employees in the sample image given above) in the Report Name Property.
Select Print Preview in the View Property.
Type &Form in the third row under the Macro Name column.
Select Open Form in the Action Column.
Select an existing Form's name from the Form Name Property drop-down list.
Select the Form in the View property.
Type a dash (-) character in the next row under the Macro Name column.
Type &GotoNext in the fifth row under the Macro Name column.
Select GotoRecord in the Action Column.
Type &Minimize in the sixth row under Macro Name.
Select Minimize from the drop-down list in Action Column.
Save the Macro with the name mcrShortCut and close it.

You can add as many commands or actions like opening Queries or for running Programs can be added to your menu for the convenience of your Application Users.

Defining Macro as Menu.

Our work on the Macro Menu is complete, but a little more work is left to define the Macro as a Menu.

Open a new macro in the design view.
Select AddMenu from the drop-down list in Action Column.
Leave the Menu Name Property Blank. This is used for Group Menu Names when you have group Menus with different sets of actions. In this case, this Macro will have more than one AddMenu Action line with a different set of Menu Macros.
Select mcrShortCut in the Menu Macro Name Property.
Save the macro with the name mcrShortCutMenu.
Open one of your Forms in Design View (don't open the form that you have used in the OpenForm action in the first macro).
Display the Form's Property Sheet (View - ->Properties).
Set the following Property Values of the Form as given below:
- Shortcut Menu = Yes
- Shortcut Menu Bar = mcrShortCutMenu
Save the Form.
Open the Form in a normal view.
Right-Click on the Form to display the Shortcut Menu. See the sample image given below: