Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Embedded Macros in Access2007

There is only one difference between the stand alone Macros and embedded macro; they will not appear in the navigation pane under Macros Group.  They are written directly on Forms/Reports or on their control's event property.  When you make a copy of the Form or Report the embedded macros goes along with it.

Let us try and find out how embedded macros are created?

  1. Open Microsoft Access2007.
  2. Open a Report in Design View.
  3. Press F4 or Alt+Enter to display the Property Sheet of the Report.
  4. Click on the Event Tab of the Property Sheet.
  5. Click on the On Load Event Property to select it.
  6. Click on the Build (. . .) button at the right side of the property.
  7. The Macro Builder option is already in selected state, Click OK Command Button to accept it.

    The Macro is open in Design View.  Title bar of the macro indicates Catalog:Report:On Load (the Report Name:Object Type:Event Type) where the macro will be embedded. 

  8. Select MsgBox Action and type my Embedded Macro in the Message Action Argument.
  9. Type On Load Info in the Title Argument of MsgBox Action.
  10. Click on the Close Toolbar button to save and close the Macro.
  11. Save and Close the Report.
  12. Right-Click on the Report in the navigation pane and select Open to open the Report in Report View.  The embedded macro will run and you will see the message.
  13. You can modify the macro by following steps 2 to 6.  You can add several actions in the Macro if needed.

Report Design in Access2007

In Microsoft Access 2007 there are some interesting design time features; Report View (not Print Preview) and Layout View besides Design View and Print Preview. These two new views allows to work with the report interactively.  In earlier versions of Access we had only Design View and Print Preview options.

Report View looks almost like Print Preview but with lots of difference. You can search for data on the Report, Copy data from report into the Clipboard, Filter Records and find out summary values of the filtered records. If you want to retain the filtered records and show them when you open the Report you can do that by setting the Filter on Load property value to Yes.

Layout View is somewhat like Design View on that you can rearrange fields while viewing the report contents, add or remove fields, increase or decrease field sizes or set data field property values to reflect changes on the report data.

  1. Let us try out the above features; open Microsoft Access 2007.
  2. Open one of your Databases.
  3. Import the Order Details Table from Northwind.mdb sample database
  4. Click on the Table to select it.
  5. Click on the Report Option from Create Menu to create a Report with basic design and save it with the name Order Details.
  6. Right-Click on the Order Details Report in the navigation pane and click on Open option to open the Report in Report View.

Now, let us try search, filter, copy operations on the Report View mode.

  1. Click on the OrderID field on the first record on the Report to select it.
  2. Click on the Find Toolbar button (the field glass icon) under the Home menu.
  3. Type 10251 in the Find What: control and click on the Find Next Command Button.  The first record with OrderID number 10251 is highlighted.  You can repeat the search operation by clicking on the Find Next Command Button.
  4. Let us do some filter action; click on the Cancel to cancel the Find operation and close the dialog box.
  5. Click on the Filter Toolbar Button.  The following filter control will be displayed over the report.
  6. Click on the Select All option to remove all the check-marks.
  7. Put check-marks on OrderID Numbers 10251 and 10255.
  8. Click OK Command Button to filter records with the selected OrderIDs on the Report.
  9. You can click on the Toggle Filter Toolbar button to display all the records or filter the records again.

To copy selected records from the Report to the Clipboard:

  1. Click at left border of the topmost record on the report and drag down to highlight few records to select them.
  2. Click on Copy toolbar button under Home Menu to copy the highlighted records on to the Clipboard.  These records you can paste into Excel, Word etc.

If you want to see the same set of filtered records every time you open the Report then you must change a Property Value of the Report.

  1. Select Design View option from View Menu.
  2. Select the Property Sheet option to display Property Sheet of the Report.
  3. Click on the Data Tab of the Property Sheet.
  4. Set the Filter on Load property value to Yes.
  5. You can add or remove OrderID numbers in the IN clause of the Filter condition in the Filter property value, if needed.
  6. Save the changes and close the Report.
  7. Open the Report in Print Preview and check whether the filter action is in effect or not.
  8. Close the Report.

Now, it is time to try out the Layout View options.

  1. Right-click on the Order Details Report in the navigation pane.
  2. Select Layout View from the displayed shortcut menu.
  3. Click and hold on the OrderID heading, drag and drop it after the Discount Column.  You can not only do this with the heading row but also on any row of records.
  4. Click on any row in the Discount column to select the column.
  5. Press delete key to remove the column from the Report.

    Let us try to bring that column back into the Report from the Field List of the Source Table.

  6. Click on the Add Existing Fields Toolbar Button from the Format Menu.
  7. Drag the Discount Field and drop it between Unit Price and OrderID fields.
  8. While the Discount Field is still in selected state click on the Arrange Menu to display its Toolbar.
  9. Click on the Property Sheet Tool to display the Property Sheet of Discount Field.
  10. Click on the Format Tab of the Property Sheet.
  11. Change the Decimal Places property value to 2 and change the Width Property value from 1 inch to 0.75 inch.

The interactivity feature is very powerful in designing Microsoft Access Reports and makes the design task easier and interesting too.

Technorati Tags:

Memo Field Text Formatting

We know that Memo Fields in Microsoft Access Tables gives us much needed flexibility to record variable length Text data.  But, in Access Versions earlier than 2007 allows us to enter in plain text format only, like we do in Notepad.

The need for some form of text formatting was always felt necessary to organize them in the form of Numbered List or Bullet List or highlight text to draw attention to important points etc. 

Well, the waiting is over and here it is in Microsoft Access2007.

Let us quickly try and find out how it works.

  1. Open Microsoft Access2007
  2. If you have an earlier MS-Access Version Northwind.mdb sample database then open it, otherwise select Sample from the displayed list of Templates and click on the Northwind 2007 Template.
    • Click on the Folder Icon at the right side to select the folder to save the Northwind 2007.accdb file and click on the Create Command Button to create and open the file.
    • Click on the Login Command Button
    • Close the Home Form.
  3. Open the Employees Table in Design View.
  4. Click on the Notes Field to select it.
  5. Click on the Text Format Property on the General Tab of the Field Properties Sheet.  Current value in the Property is Plain Text.
  6. Select Rich Text from the drop down list to replace Plain Text.
  7. You will see a warning message as below; click Yes to apply the change.
  8. Close the Design View and save the changes.
  9. Open the Employees Table in Datasheet View.
  10. Expand the Row Height of the records by dragging the intersection of the two records at the left border.
  11. Bring the Notes Field values into view by moving the Scrollbar to the right and increase the column width, see the image below.
  12. You can now format the Memo Field Text as you apply Text formatting in MS-Word.
  13. In the example image above I have created a Numbered List, Bullet Text, applied Bold, Italics, highlighting, alternate rows fill/back color etc.

Sub-Query in Query Column Expressions

Queries are the main data processing component of any database systems.  Queries work behind the scene shaping the data into Reports and other form of outputs.  Microsoft Access Users (mostly beginners) attempt to create the output for a Report by chaining several Tables in Queries together and the report data is expected to form in one or two query steps.  This approach may not be the correct one because it will be difficult to get the proper output for the Report.

First, you must plan your Report by defining layout, contents, grouping, summary etc.  If several related tables are involved in organizing the required data then you may take smaller steps of joining few tables or queries together in a Query.  Use this Query as input and combine with other tables in the next step.  You may create intermediate tables and crate Queries out of them for further processing of information.  In these steps you may use Make-table, Append Table, Update etc. to process the data.

When the Report contents are complicated and difficult to create then my approach is to create a Report table and bring the data piece by piece from the source Table(s) with Queries/VBA Routines and add or update them into the Report Table before opening the Report.  These processing steps can be automated through Macros/VBA.

Necessary report parameters like date-range or filtering criteria values will be collected in a parameter table to use them in data processing queries.  There will be options in the Parameter Form either to Re-run the Report creation procedure with changed parameter values or to open the existing Report in Preview/Print mode. 

Here, we are trying to explore the usage of Sub-Queries in Queries to filter or incorporate data from tables or from other Queries.

Let us look at a simple Query that uses a sub-query in the criteria section to filter data from the Orders Table.   In the Orders Table there are about 830 Orders ranging OrderIDs from 10248 to 11077.  We need to filter certain Group of Orders (say Order Numbers 10248,10254,10260,10263,10267,10272,10283) for review.

Following is an SQL of a sample Query that filters the above Orders without the use of a Sub-Query:

SELECT Orders.*
FROM Orders
WHERE (((Orders.OrderID) In (10248,10254,10260,10263,10267,10272,10283)));

The above Query does the job but the problem is when we want a different set of Orders to be filtered then the Query's Criteria line needs to be modified physically to replace with new set of Order Numbers to filter.  We can not expect the User to do this task manually, instead we must provide them with a facility to key-in the Order Numbers into a table (ParamTable with a single field: OrderNumber) and use it as criteria.  This method will facilitate automatic detection of change of values in the table at run time.  The User can simply type the Order Numbers on a Datasheet Form and click a Command Button to run the query with the changed order numbers.  With this method we need a Sub-Query in the criteria row to compare the Order Numbers in the Paramtable with the Order Numbers in the Orders Table and filter data.

We will modify the Query to insert a Sub-Query in the Criteria Row to pull the values from the ParamTable and to use the OrderNumber field values as criteria. 

The modified SQL String of the Query is given below:

SELECT Orders.*
FROM Orders
WHERE (((Orders.OrderID) In (SELECT OrderNumber FROM OrderParam)));

The Sub-Query string in the Criteria Row is in Bold in the SQL above.

It is likely that you already came across the above sub-query before, but we are going to learn how to use a Sub-Query as an expression in a Query Column to incorporate values from a different table, related to the Query Source Table. This usage is not so common and it is very useful at difficult times.

When several tables are used in a Query with LEFT JOIN or RIGHT JOIN relationships it becomes difficult to link all the related tables this way to incorporate summary values of one table. This is more so when one-to-many relationships are involved.

We will use Orders and Order Details Tables from the Northwind.mdb sample database for our example. Import both these tables from the Northwind.mdb sample database from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.

The sample Query (in normal style), given below, uses both the Tables in the Query, linked on OrderID Field of both tables to create an Order-wise Summary from the Order Details Table.

SELECT Orders.OrderID,
 Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderVal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID;

The same result can be achieved without placing the Order Details Table in the Query design. We will write an expression in a separate Column using a Sub-Query to pull the summary Order-wise Total Value directly from Order Details Table. Here is the example SQL String:

SELECT Orders.*,
    (SELECT  Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderValue
     FROM [Order Details] AS ODetails WHERE ([ODetails].[OrderID] = [Orders].[OrderID])
GROUP BY [ODetails].OrderID) AS OrderVal, [OrderVal]*0.12 AS Tax
FROM Orders;

There are several records in the Order Details Table for a single order in the Orders Table.  Through the Sub-Query (Total Query) we sum-up the Sale Value of each Order and places the result in its corresponding row in the output.  So, the Sub-Query runs in a separate Column as an expression independently for each row for the Orders table for the output.

The new column name: OrderValue created can be part of other expressions and we have calculated the Tax value, 12% of OverValue, in a separate column.

Let us take a closer look the Sub-Query.

  1. The SELECT clause uses only one output column (Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderValue) and the expression is named as OrderValue.  You should not use more than one columns in the SELECT clause.
  2. In the FROM clause the Order Details Table is given a new name (ODetails) and this name is used to qualify the OrderID field in the WHERE clause.  The OrderID field appears in both Orders and Order Details Tables.
  3. The WHERE clause in the Sub-Query is necessary to match the OrderIDs of both tables and place the calculated values in their matching row of Order Records.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module 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 Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database 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 Excel 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 function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...


Blog Archive

Recent Posts