Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Restoring disabled Full Menus Access2007

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 functions, their strength and drawbacks.  Others many 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 could, into 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.

We can do this by setting 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.

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.  User-level security feature is available in Microsoft Access2003 or earlier versions only.

Another way of preventing users from straying out is to create Custom Menus and Custom Tool bars 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 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 Sub-Routines 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 are 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 latter 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 to take a back-up, 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 loosing your database.

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 Office Button Menu as explained above.

  1. Open the database.
  2. Press ALT+F11 to display the VBA Window
  3. Press CTRL+G to display the Debugging Window (Immediate Window).
  4. Type the following command in the Debug window and press Enter Key:
    CurrentDb.Properties("AllowFullMenus").Value = True
  5. 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:

Running Workgroup Admin in Access2007

If you are serious about Microsoft Access User-level Security in earlier versions (.mdb files) of Access Databases and would like to continue  using them in Access2007, with the security settings intact, then you should not convert them into Access2007 (.accdb).  Access2007 Security concepts are totally different and once  the .mdb files are upgraded into .accdb (Access2007) format the User-level Security settings are lost and you will no longer be able to implement them in .accdb databases. 

If you have already converted .mdb database into .accdb format and lost all user-level security settings then you are not totally lost with the database. If you have a backup of the earlier version database then you are lucky. All you have to do is to restore the database from backup and use it in Access2007 with User-level security intact.  If you lost the backups too then you can save the Access2007 version database into an earlier Version 2002-2003 or 2000, by using the Save As…  option from the Office Menu (top left corner menu) and select an appropriate .mdb database format and save the active Database as a new copy.

This will not restore the User-level security settings.  You have to restore it manually from scratch by assigning access rights to each and every database object at User-Groups or individual User level.

In either case you must Join the Workgroup Information File (a database with .mdw file extension) from Access2007, to attach your database Users/Groups to Access 2007 to use with your .mdb databases or to implement new security profiles on them.

When you open an .mdb database in Access2007 the Users and Permissions Menu is visible under Database Tools.  But, your database Users or User-groups will not be visible under this option unless you Join the Wrokgroup Information File, you were using in earlier version of Access.

In Access2003 there is an option under Tools—>Security to run the Workgroup Administrator program to Create/Join a Workgroup Information File.  In Microsoft Access2000 this program is located in the Language Folder (…\1033 for U.S. English) of MS-Office C:\Program Files\Microsoft Office\Office\1033\WrkgAdm.exe to run and Join the Workgroup Information File.

But, in Microsoft Access2007 none of these options are available, except running the following command from VBA:

DoCmd.RunCommand acCmdWorkgroupAdministrator 

You can run this command from the Debug Window (Immediate Window, Alt+F11 then Ctrl+G to display) manually or from a Command Button Click Event Procedure and Create a new Workgroup Information File or Join an existing one on Server.

Once you Join the Workgroup Information File you can start using the .mdb files with their User-level security in Microsoft Access2007 or restore the lost security settings manually.

Technorati Tags:

Apply Filter to Table directly

Normally we use Filter settings on Forms/Reports to extract records on specific conditions. This can be achieved in several ways like adding a WHERE Condition (without the clause WHERE) in the parameter setting of the OpenForm Macro Action or in ApplyFilter Action parameter of a Macro or in the Docmd.OpenForm (like

DoCmd.OpenForm "myForm", acNormal, , "EmployeeID Between 5 AND 10" command line in VBA) or Filter by Selection on Form or use a Query as Record Source and so on.

But, it is very unlikely  that someone think of using the Filter Property of a Table directly to filter records when the Table is open in Datasheet View.  If you are working with someone else project and found a table showing only few records in datasheet view but you are told that the table suppose to have hundreds of records in it and want to find out what happened to the rest of the records, then read on.

We will explore how this trick works on a Table, for a change.  If you ever tried to set a Filter condition directly on a Form then you don't need any extra help to do this on your own.  To try out this we need some ready made data from Northwind.accdb sample database.  You may try it out with your own table as well.

  1. Import the Order Details  Table from Northwind.accdb sample database.
  2. Open the Table in Design view.
  3. Press F4 to display the Property Sheet of the Table.

    Table Property Sheet View

  4. Find the Filter Property and type [Order ID] Between 35 AND 45 into the property (in Access2007). Type [OrderID] Between 10249 AND 10251 in earlier Access Versions.
  5. Set the Filter On Load property value to Yes.
  6. Save and close the Table Structure.
  7. Open the Table in Datasheet View to see the Filter in action.

The output records displayed are only of Order IDs between 35 and 45.  If you design a Quick Form or Report from this Table the Record Source property will be set with an SQL SELECT statement with the WHERE condition inserted from the Filter Property settings from the Table.  But, if you modify the Filter condition on the Table later don't expect to reflect that change on the Record Source SQL of Report or Form automatically.

Like anything else in Microsoft Access If you would like to automate this through VBA, to change the filter criteria with the click of a button then here it is for you.  But, there is a small problem which I will tell you later so that you will know how important it is.  After all it is about setting a simple filter condition on the Filter Property of the Table Structure.

We can address the Filter Property of a Table in VBA either through the front-door approach or through the back-door method, so to speak.  The front-door approach is addressing the Filter Property through the TableDef.Property.Filter path and the back-door approach is addressing through Container.Documents.Properties.Filter path.  As you can see the second method is little bit lengthy, that is why I call it back-door method.  We will try examples of both methods.

The second approach is very useful when working with Forms or Reports, like taking a list of all Forms/Reports or want to change the name of a Form etc. You can refer an earlier blog post that creates a User-defined Property (Custom Property) to save values in it for opening a Form with last-worked record to continue work from that record onwards, click here to find out more about it.


  1. Copy and Paste the following VBA Code into a Standard Module of your Database.
    Public Function TableFilter1(ByVal OrderStart As Long, ByVal OrderEnd As Long)
    Dim db As Database
    Dim Tbldef As TableDef
    Set db = CurrentDb
    Set Tbldef = db.TableDefs("Order Details")
    Tbldef.Properties("Filter").Value = "[Order ID] >=" & OrderStart & " AND [Order ID] <=" & OrderEnd
    Tbldef.Properties("FilterOnLoad").Value = True
    End Function
  2. Run the above sample Code from the Debug Window or from a Command Button Click Event Procedure like the sample run given below:
TableFilter1 35,45

NB: The above Code and sample run is given for Order Details Table of Access2007.  If you are using an earlier Access Version then change the [Order ID] name to [OrderID] (i.e. without a space between Order and ID) and in the sample run type TableFilter1 10249, 10251 instead of 35,45 for OrderID range values.

If you have not tried out the manual filter method explained above or removed the filter criteria setting from the Filter Property then you will run into problems with the above program reporting an Error message stating that the Filter Property not found.

When you implement the VBA method see that an initial criteria setting is set in the Filter property of the Table.  Without the criteria setting the Filter Property will not be visible in VBA.


Copy and Paste the following VBA Code into the Standard VBA Module and run the Code in the same way as Example-1 with different set of OrderIDs:

Public Function TableFilter2(ByVal OrderStart As Long, ByVal OrderEnd As Long)
Dim db As Database, ctr As Container, doc As Document

Set db = CurrentDb

Set ctr = db.Containers("Tables")
Set doc = ctr.Documents("Order Details")
doc.Properties("Filter").Value = "[Order ID] >=" & OrderStart & " AND [Order ID] <=" & OrderEnd
doc.Properties("FilterOnLoad").Value = True

End Function
Technorati Tags:


Your email address:

Delivered by FeedBurner


Infolinks Text Ads

Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...


Blog Archive

Recent Posts