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. 

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.

Do the following to get that done:

  1. Click on the Office Button (Top left corner).
  2. Select Access Options.
  3. Select Current Database.
  4. Move the scrollbar down to find the Ribbon and Toolbar Options as shown on 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.

Share:

Sub-Report Summary Value in Main Report Calculations

Using 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..
  4. Sample Database Download Links, for Microsoft Access 2003 and 2007 Versions, are given below.
  5. Download the 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 percentage of each category of products sales value on Grand-Total Sales Value (or Pcercentage = Category Sales Value / Total Sales value of all Categories * 100).  We can do this by adding few text boxes on both reports and writing few expressions in them.

    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 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 child label to read as Sub-Total.
  8. Save and close the Sales by Category Subreport.
  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 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 sales value of 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’s 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 it's 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 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 at 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 in to 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 at the left side of the expression snippet?
    • Select Percent from the drop-down list of the Format property. With this 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.

    Our Report is almost finished, but we need 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 in the second page.

    This will not appear on the report from 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.

    • 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.
    • 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 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.
    • 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.

Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

Running Sum in MS-Access Query

Introduction. We are familiar with creating Auto-Numbers in Query Column, through an earlier Function created with the Name QrySeq(), publi...

Labels

Blog Archive

Recent Posts