Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Legacy Custom Menus Toolbars in Access2007


You have spent a lot of time designing Custom Menus and Toolbars in your .MDB Database. You have upgraded to Microsoft Access 2007 Version. You opened your .MDB database in the new version of MS-Access. The customized menus and toolbars you have designed for your application are disappeared. 

The Add-In Menu.

Your Custom Menus and Tool Bars are not gone anywhere, all you have to do is to find them.

Custom Menus and Toolbars are safely kept inside the Add-in Menu of Microsoft Access2007. Click on the Add-in Menu to find all of them there.  Check the sample image given below for reference.

Have you noticed the group name Custom Toolbars appearing at the bottom of the Add-in Menu?

But, Access 2007 Menu Bars also appearing on the top.  You want to turn off the default Access2007 Menus and Toolbars from the top and replace it with your Custom Menus and Toolbars.

Access Option Changes.

Do the following to get that done:

  1. Click on the Office Button (Top left corner).
  2. Select Access Options.
  3. Select the Current Database.
  4. Move the scrollbar down to find the Ribbon and Toolbar Options as shown in the image given above.
  5. Select your Custom Menu Bar name from the drop-down list, replacing the text (Default).
  6. Select your Shortcut Menu Bar name from the drop-down list replacing the text (Default).
  7. Remove the check-marks from the next three options:
    • Allow Full Menus.
    • Allow Default Shortcut Menu.
    • Allow Built-in Toolbars.
  8. Click OK to save changes and come out of Access Options.

  9. One more step to go:

  10. Close the database and open it again to register the changes.

Now, your Menu Bars and Tool Bars are in complete control of your Application they will look like the image given above.

You want to restore Access 2007 Menus and Toolbars, then open .MDB file by holding the Shift-Key, to prevent Auto-Macro from running and to get control of the database window.  Go through Steps-1 to Step-9 and reverse what you did earlier.


Sub-Report Summary Value in Main Report Calculations


How to bring sub-report summary value to the main report and use it in calculations?

But, first, we need a ready-made report for our project, without the calculations part on it.

Sample database download links are given below. You may download the database version you need.  Microsoft Access2007 version database can be used for later versions of Access too.

First, take a look at the following images:

  1. Print Preview of the finished report.
  2. Original Report Preview. 
  3. Changes made on the Report in Design View, to get the result shown on the first image above.

    Download Links.

  4. Sample Database Download Links, for Microsoft Access 2007 and 2003 Versions, are given below.

  5. Download the suitable sample database.

    In short, our task is to add the sub-report category total at the Footer Section of the Sales by Category sub-Report. Get that summary value of each category to the main report header.  Calculate the percentage of each category of product sales value on Grand-Total Sales Value (or Percentage = Category Sales Value / Total Sales value of all Categories * 100).  We can do this by adding a few text boxes on both reports and writing a few expressions in them.

    The Sub-Report Changes.

    Let us start with the Sales by Category Sub-report first.

  1. Open the downloaded database.
  2. Open Sales by Category Sub-report in design view.
  3. Right-click on the Report Footer bar and select Properties to display the Property Sheet.
  4. Select the Height Property and change the property value to 0.33” or 0.838 cm.
  5. Select the Text Tool from the Toolbar above and draw a text box on the Report Footer area below the ProductSale column.
  6. Write the expression =Sum([ProductSales]) in the Control Source property of the text box. Change the Name property value to SubTotal.
  7. Modify the Caption property value of the child label to read as Sub-Total.
  8. Save and close the Sales by Category Subreport.

    The Main-Report Changes.

  9. Open Sales by Category main report in Design View.
  10. Create a Text Box, on the Header Section of the Report, to the right of the report heading.
  11. While the text box is in the selected state display the property sheet (F4).
  12. Write the expression =Sum([ProductSales]) in the Control Source property. Change the Name property value to TotalSales. Change the Caption property value of the child label to Total Sales.

    Note: The above expression will calculate the Total Product Sales value of all Categories on the main report. The same expression on the sub-report will calculate the sales value of the current category of Product (example: Beverages). Now we have the product sales value of a particular category as SubTotal on the Sub-Report and the Total Sales values of all categories of products as TotalSales. In the next step, we can find the percentage of a particular category of product sales on total sales of all categories. The simple formula can be Percentage=SubTotal/TotalSales*100.  But, SubTotal is on the Sub-Report and we cannot directly address it as SubTotal on its parent report or Main report (without specifying where it can be located in the main report having one or more child reports or sub-reports).

  13. Create a text box below the Category Header bar to the right of the Category Name heading, move the text box to the right, and position it below the Total Sales calculation control on the Report Header.
  14. Right-click on the Control Source Property of the text box and select the Build option, from the displayed list, to open the expression builder control.
    • Click on the = symbol to insert it into the expression editor window.
    • Double-click on the + symbol on the left side of the Reports option to expand and show other options.
    • Double-click on Loaded Reports.
    • Double-click on Sales by Category main report to display the Sub-Report's name.
    • Click on Sales by Category Subreport to display it's control names in the next column.
    • Find the Subtotal control in the list and double-click on it.

      The Subtotal control reference of the sub-report: [Sales by Category Subreport].[Report]![SubTotal], is inserted into the expression builder. If we know how to write this reference correctly into the Control Source property manually then we can do that without going through this route. But, once you go through this procedure a few times and study Microsoft Access’s addressing method you will know how to write them on Reports or Forms manually.

    • Click OK to come back to the Control Source property of the text box with the reference of the subtotal control and to write the rest of the expression to calculate the percentage.
    • Type /[TotalSales] to the end of the subtotal reference. Have you noticed the slash on the left side of the expression snippet?
    • Select Percent from the drop-down list of the Format property. With these settings, we don’t have to write *100 part in the expression.
    • Change the Caption of the child label to Category %. Change the Font-size of the Text box and child label to 12 points and make Font-weight Bold.

    Report Sample Print Preview

    Our Report is almost finished, but we need a little more change and that comes next after we preview the progress of our work so far

  15. Save the changes we have made on the Report and open it in Print Preview, to see the result of the changes we have done on the report so far.

    The Report should look like the image given below.

  16. Move the Report to the next page.

    The Total Sales value on the Report Header Section is not appearing on the second page.

    This will not appear on the report from the second page onwards along with the Report Heading. Report Header section values will only print on the first page of the report and Report Footer will print only on the last page of the report.  But, we want the Heading and the expression written on the Header Section to appear on every page of our Report. Whatever we insert on Page Header Section will appear on every page. We can transfer the Report Heading to Page Header Section but not the Calculation Text box. The SUM(), Count(), and other aggregate functions will not work on the Page Header Section, but we want the value from the Total Sales control to appear on the Page Header Section as well. We will do the following changes to keep the report heading and Total Sales values on all pages of the report.

    The Final Changes.

    • Open the report in design view.
    • Drag the Category Name header bar down to get enough space on the Page Header Section, to cut and paste the Report Heading there.
    • The high-light report heading and the report date controls (leave alone the Total Sales textbox), cut, and paste them into the Page Header Section.
    • Select the Total Sales text box, copy and paste it on the Page Header Section,  move and position it below the text control on the Report Header Section.
    • Write the expression =[TotalSales] (the Name of the total sales calculation text box on the header section) in the Control Source property (over-writing the existing expression) of the copied text box.

      This will display the value from the Header Section text box, where the Total Sales value is calculated.  

    • Select the Header Section text control along with the child label and set their Visible Property value to No, to keep it hidden when the report is Previewed or printed.
    • Save the report with the changes.

      Print Preview the Report.

    • Open Sales by Category report in Print Preview, move the pages forward, and check the headings and category percentage values.
  17. I am sure next time you want to do something like this you can do it in few minutes.


MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.


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