Invoke Word Mail Merge from Access2007

Introduction

Mail Merge is a powerful feature that is used to create and print form letters in the Microsoft Word Application. For example, employment contracts often contain fixed terms but require variable details, such as the employee's name, address, basic pay, allowances, and contract period. Traditionally, these documents were preprinted with standard terms and included blank spaces or dotted lines where personal details were filled in manually or with a typewriter. With Mail Merge, however, the document can be printed in its complete form, with personal details automatically inserted into the appropriate locations alongside the standard contract terms. This process can be automated and generated for multiple employees within minutes.

All that’s required is to create a table containing employees’ personal details, design a single Mail Merge Word document with the standard terms of the employment contract, link the Access table to the Word document, and insert the personal detail fields wherever needed. Once set up, the document can be merged and printed directly to the printer.

In MS Access 2007, part of this mail merge process can be initiated directly by selecting a table or query to link with the Mail Merge document in Microsoft Word. You can either connect the table/query to an existing Word document or create a new document linked to the selected data source. Let us create a sample document to see how this works in Microsoft Access 2007.

Prepare for a Demo Run.

1. Open Microsoft Access 2007.

2. Open the sample database Northwind2007.accdb, or import the Employees table from this database into any database that you wish to open to try this out.

3. Click on the Employees Table in the navigation pane to select it.

4. Select External Data -> Export -> More -> Merge it with Microsoft Word.

   Microsoft Word Mail Merge Wizard opens up and gives you two choices: either to open an existing Word document or to create a new one and link the Employees Table with the document. 

   

   Right-click to open Large Image in New Tab/Window.

   Depending on what option you have selected, the Employee Table is linked to that document.

5. Select the Word Menu Mailings -> Insert Merge Fields to display the linked Employees Table field list. See the image given below.

   

   Right-click to open Large Image in New Tab/Window.

6. Now, you must place the attached Table's data fields in appropriate locations on the Document's body to insert their contents there.  I have placed the name and address of the attached Employees Table on the Document.  The sample Document image is given below:

   

   Right-click to open Large Image in New Tab/Window.

7. Place the insertion point where you want the field content to appear, then select the field from the Mailings -> Insert Merge Fields Menu.

8. Repeat this action to place other fields in appropriate places in the document.

9. You can preview the Document by selecting Mailings -> Preview Results at any preparation stage of the document.  The sample preview of the above test fields is given below:

   

   Right-click to open Large Image in New Tab/Window.

If the Employees table has 50 records, then 50 copies of this Document will be printed when you select the option Mailings -> Finish & Merge -> Print Documents; i.e., one document for each employee with their respective personal details merged in.

Technorati Tags: Microsoft

• Memo Field and Data Filtering
• Lively Controls on Form
• Create your own Color Palette
• Colors 24 bits and Binary Conversion
• Hexadecimal Numbering System

Read More

Copy Paste Data from Excel to Access2007

Introduction.

Microsoft Office Applications already provide built-in methods to transfer data between them. This can be implemented by importing or exporting data, directly linking to the source while keeping the original data intact in the parent application, or by simply copying data to the clipboard and pasting it into another program. These options have long been available.

In earlier versions of Access, you first needed to create a table with matching field types before pasting or appending data. Access 2007 has made this process much simpler. For example, you no longer need to create a table beforehand when pasting data from Excel. Instead, Access 2007 prompts you to confirm whether the copied data includes header rows. If you select Yes, Access automatically creates a new table (using the worksheet name) and pastes the data into it, assigning the correct field types.

Let us find out how?

1. Open Microsoft Excel and create a small database with the sample data given below:

   

   Right-click to open Large Image in New Tab/Window.

2. Open Microsoft Access 2007 and open an existing .accdb database or create a new one.

3. Make the Excel database window active.

4. Highlight the Excel database range, including the header row.

5. Select Copy from the Home Menu, to transfer the data into the Clipboard.

6. Make the Access 2007 database window active.

7. Right-click on the Navigation Pane of Tables and select Paste from the shortcut menu.  The following message box is displayed:

   

   Right-click to open Large Image in New Tab/Window.

8. If you have included the header line of the data while coping it, then you may click on the Yes Command Button; otherwise, select No.

A new Table will be created with the Worksheet name.  The header cell values will be used as field names.  The field data type (Text, Date, Number, etc.) will be correctly defined depending on the data type that you have copied from Excel.

If you have selected No, then the data will still be pasted into a new table, but the field names will be F1, F2, F3, etc.

Technorati Tags: Microsoft

1. Roundup Excel Function in MS-Access
2. Proper Excel Function in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007
7. Microsoft Excel-Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel- 2
11. Opening an Excel Database Directly
12. Create Excel, Word Files from Access

Read More

User-level Access Security and Access2007

Introduction.

In versions of Microsoft Access before Access 2007, User-level and Object-level Security were reliable methods for protecting applications. I secured all my Access applications with User- and Group-level security, which proved highly effective when the applications were shared over a network. Because of this, I never needed to lock VBA modules with a password or convert the applications into a compiled form (MDE format) to prevent tampering. User-level security provided sufficient control to assign users precisely the level of access intended for them within the application.

If you wish to continue using this feature in Access 2007, you must avoid converting earlier databases (with the .mdb extension) to the newer .accdb format. Once converted, all User-level security settings are removed and cannot be reinstated. However, you can still maintain and use User-level security in Access 2007 as long as your databases remain in Access 2003 or earlier formats (with the .mdb extension).

Database Objects and Permissions

With User-level Security, you can control what users can do and what they should not do.  Check the following table to get some idea as to how to set permissions on each object type and what they do:

Permission	Applies to these objects	Result
Open/Run	Entire database, forms, reports, macros	Users can open or run the object, including procedures in code modules.
Open Exclusive	Entire database	Users can open a Database and lock out other users.
Read Design	Tables, queries, forms, macros, code modules	Users can open the listed objects in the Design view. Note: Whenever you grant access to the data in a table or query by assigning another permission, such as Read Data or Update Data, you also grant Read Design permissions because the design must be visible to correctly present and view the data.
Modify Design	Tables, queries, forms, macros, code modules	Users can change the design of the listed objects.
Administer	The entire database, tables, queries, forms, macros, and code modules	Users can assign permissions to the listed objects, even when the user or group does not own the object.
Read Data	Tables, queries	Users can read the data in a table or query. To grant user permissions to read queries, you must also give those user permissions to read the parent tables or queries. This setting implies Read Design permission, which means that users can read your table or query design in addition to the data.
Update Data	Tables, queries	Users can update the data in a table or query. Users must have permission to update the parent table or queries. This setting implies both Read Design and Read Data permissions.
Insert Data	Tables, queries	Users can insert data into a table or query. For queries, users must have permission to insert data into the parent tables or queries. This setting implies both Read Data and Read Design permissions.
Delete Data	Tables, queries	Users can delete data from a table or query. For queries, users must have permission to delete data from the parent Tables or Queries. This setting implies both Read Data and Read Design permissions.

Technorati Tags: Microsoft Security

Earlier Post Link References:

• Create your own Color Palette
• Colors 24 bits and Binary Conversion
• Hexadecimal Numbering System
• Octal Numbering System
• Learn Binary Numbering System-2

Read More

Macros and Temporary Variables

Introduction.

If you are using Microsoft Access 2007 or a later version, there is a useful new feature: the SetTempVar action in Macros, which allows you to define global variables. Once defined, these variables can be used across other macros, event procedures, forms, or reports. The temporary variables remain in memory until you explicitly clear them using the RemoveTempVar action (for a single variable) or the RemoveAllTempVars action (to clear all). All temporary variables are automatically removed from memory when you close the database.

The TempVar Usage in Macros.

Let us try a quick example to understand the usage in macro:

1. Select Macros from the Create Menu.

2. Select SetTempVar Action in the first row.

3. Type myName in the Name argument.

4. Type the expression Inputbox(“Type your Name”) in the expression argument.

5. Save the Macro with a name (say macDefineVar).

6. Right-click on the macro and select Run from the shortcut menu (or Double-click) to execute the Macro.  The InputBox Function will run and prompt for a value to type.

7. Type your name and click the OK Command Button.

   Your name is stored in the Variable myName. We have used the Function, InputBox() in the expression argument.  You can use constant values, functions, or expressions to assign values to the variable myName.

8. Open a new form in the design view.

9. Insert a Text Box in the details section of the Form.

10. Type the expression =Tempvars!myName in the Control Source property.

11. Change the form from Design view to Form View.

    Your name will now appear in the text box. The above example demonstrates how to define a temporary variable and reference it in expressions on a form. Next, let us see how to remove this variable from memory.

12. Close the Form.

13. Select Macro from the Create menu to open up a new macro in the design view.

14. Select RemoveTempvar from the Action list.

15. Type myName in the Name parameter.

16. Save the macro with the name macRemoveVar.

17. Double-click on the macRemoveVar macro to execute it.

18. Open the form again to check whether your name still appears in the text box on the form or not.

The text box will be empty, indicating that the variable myName does not exist in memory.  The RemoveTempvar action needs a variable name as a parameter.

TempVar Usage in Query.

Let us take this a step further and build something more practical for real-world scenarios. This time, we will calculate the order-wise percentage based on the total Order quantity. We explored a similar problem earlier in the blog post Percentage in Total_Query.

The key requirement here is to obtain the sum of all order quantities to calculate the percentage for each individual order. In the earlier example, we accomplished this by creating a separate query to calculate the total order quantity and then linking it with a second query, grouped by Order Number, to compute the percentages.

Here we will initialize a Temporary Variable with the sum of Quantity and use the Variable name in the percentage calculation expression.

1. Import the Order Details table from the Northwind sample database.

2. Select Query Design from the Create menu; don't select any table or query from the displayed list.

3. Change the Query in SQL view; copy and paste the following SQL string and save the Query with the name OrderPercentageQ:

   SELECT [Order Details].OrderID, First([Order Details].UnitPrice) AS UnitPrice, Sum([Order Details].Quantity) AS Quantity
   FROM [Order Details]
   GROUP BY [Order Details].OrderID;
   

4. Open the macro macDefineVar in the design view.

5. Change the variable name, myName, to TotalQuantity (myName variable will remain in memory).

6. Change the expression InputBox(“Enter your Name”) to DSum(“Quantity”,”[Order Details]”).  Do not add the = symbol at the beginning of the expression.  Save the macro with the change.

7. Double-click on the macro to run and calculate the total quantity and store the value in the temporary variable TotalQuantity.

   We will modify the OrderPercentageQ with the addition of a new column that calculates the order-wise percentage of total orders.

8. Open a new Query in SQL View.

9. Copy and paste the following SQL String into the SQL editing window of the new Query and save it with the name OrderPercentageQ2:

   SELECT [Order Details].OrderID, First([Order Details].UnitPrice) AS UnitPrice, Sum([Order Details].Quantity) AS Qty, Sum([quantity])/[tempvars]![totalQuantity]*100 AS Percentage
   FROM [Order Details]
   GROUP BY [Order Details].OrderID;
   

10. Open the Query in Design View and check how we have written the expression in the last column to calculate the percentage using the temporary variable [tempvars]![totalQuantity].

11. Change the Query View into Datasheet View to display the Order-wise percentage of Total Quantity.

The TempVar Usage in VBA.

We can work with the Temporary Variable (Tempvars Object) in VBA.

With the Add method, we can define a Temporary Variable and assign an initial value to it.

Add() method of TempVars Object:

    Syntax: TempVars.Add "Variable Name", "Initial Value"

    Example-1: TempVars.Add "TotalQuantity", DSum("Quantity", "[Order Details]")

    OR

    Example-2: TempVars!TotalQuantity =  DSum("Quantity", "[Order Details]")

You can define a total of 255 temporary variables in this way. 

Remove method of TempVars Object:

The Remove() method erases only one variable and frees the memory occupied by the variable.

    Syntax: TempVars.Remove "Variable Name"

    Example: TempVars.Remove "TotalQuantity"

RemoveAll method of TempVars Object:

The RemoveAll() method removes all the temporary variables defined with the Add() method.

    Syntax: TempVars.RemoveAll

    Example: TempVars.RemoveAll

The Count property gives the count of all temporary variables defined in memory:

Example: Debug.Print TempVars.Count returns the count of temporary variables defined in memory

TempVars Item Indexes.

Each temporary variable stored in memory is assigned an index number, starting from 0 up to (total number of variables – 1). A variable can be referenced by its Item index, which allows you to read its Name or Value, or even assign a new value to it. The Debug.Print TempVars.Item(0).Name prints the name of the variable.

SumofQuantity = TempVars.Item(0).Value

You can also use this reference to modify the existing value in the temporary variable.

TempVars.Item(0).Value = TempVars.Item(0).Value + 1

You should not use a subscript beyond the existing number of temporary variables in memory; otherwise, an error will occur.  If you have defined 5 variables, then the valid index numbers are 0 to 4.

Earlier Post Link References:

• Colors 24 bits and Binary Conversion
• Hexadecimal Numbering System
• Octal Numbering System
• Learn Binary Numbering System-2
• Learn the Binary Numbering System

Read More

Easy-Read Reports

Introduction.

When computer reports, accounting ledger statements, or purchase invoices are printed with closely spaced lines, readability improves if alternate lines are shaded with a light background color. Traditionally, pre-printed stationery was used for this purpose. Line printers typically print six lines of data within a one-inch vertical space. To emphasize headlines, the double-strike method was used, where the hammer struck each character twice—this being the only enhanced printing style available in line printers.

Line-printer-based reports were designed on graph-paper-like sheets, with carefully written code to position headings, data lines, and summaries precisely on the pre-printed stationery.

An A4 sheet (8.5 × 11 inches) provides 66 print lines vertically. Of these, one inch at the top and half an inch at the bottom are reserved as margins, while the alternate data lines are shaded in green—similar to the sample image shown below.

Right-click to open Large Image in New Tab/Window.

A4-size paper has 80 print positions across when a 10-character-per-inch (pitch) font size is used. This can be increased to 96 characters if a 12-character-per-inch character size is used.

The above details provide a general background about computer stationery. Reports designed for line printers were created with these specifications in mind. When necessary, you can design reports for plain paper by setting the section height and the text box control height to one-sixth of an inch, matching the line spacing used by traditional line printers.

Light Shading of Alternate Report Data Lines

We will use a simple trick to print alternate lines with a shaded background, making the report easier to read. There is no need for pre-printed stationery, as the shading will be applied dynamically during printing.

You can design a Quick Report and add a few lines of VBA code in the report’s class module. If you already have a report with closely spaced detail lines, you may skip directly to step 3 below.

1. Import Products Table from MS-Access sample database Northwind.mdb

2. Use the Report Wizard to design a Tabular Report with the Products Table, like the sample image given below:

   

   Right-click to open Large Image in New Tab/Window.

3. Open the Report in Design View.

4. Select all the controls on the Detail Section of the Report and drag them to the right to get enough space to draw a Text Box on the left side to display Serial Numbers on the Report Lines.  Drag the heading lines and position them to the right.

5. Draw a Text Box on the left side and write the expression =1 in the Control Source Property.  Change the Name Property Value to SRL.

6. Change the Running Sum Property Value to Over All.

7. Create a Label control at the Page Header above the Text Box and change the Caption to SRL (for Serial Number).

8. Select all the controls in the Detail Section together and display the Property Sheet (F4 or Alt+Enter).

9. Change the Top Property Value to 0.  All the controls will be shifted and positioned at the top edge of the Detail Section.

10. Select the Rectangle Tool from the Toolbox and draw a rectangle around all the text boxes in the Detail Section (see the design view image above).

11. Display the Property Sheet of Rectangle (F4) and change the Name Property Value to Box1.

12. Select the Send-to-Back option from the Arrange Menu to position the rectangle behind the text box controls.

13. Reduce the Detail Section height so that there is no empty space below the TextBox controls.

14. Display the VBA Code Module of the Report (ALT+F11).

15. Copy and paste the following lines of VBA Code into the Code Module and save the Report:

    The Report Module VBA Code.

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim xSrl
    xSrl = [SRL]
    If xSrl / 2 = Int(xSrl / 2) Then
       [Box1].BackColor = &HCCC8C2
    Else
       [Box1].BackColor = &HFFFFFF
    End If
    End Sub
    

16. Open the report in Print Preview.  You will find the result as shown in the report image at the top of this page.

• Hexadecimal Numbering System
• Octal Numbering System
• Learn Binary Numbering System-2
• Learn the Binary Numbering System
• Scaling the Chart Object

Read More

Top N Records in Query

Top N Records in Query.

We have seen the usage of different types of complicated Queries like the following:

• Cross-Tab Union Queries for Charts
• Union Query
• Percentage of Total Query
• Finding Consecutive Dates in a Query
• Multiple Parameters in Query
• Auto Numbering in Query Column
• Sub-Queries in Expressions

Today, we will learn how to define and extract the top 100 records, or a certain percentage of the total records, based on the values in a particular Column.

Review of Rules of Queries.

You need to know only a few rules to work with this type of Query.

1. You can select several Columns of data from the source for output.

2. You must sort one or more columns of data in ascending or descending Order, and the leftmost sorted column will pick the top valuation records.

3. If the output contains duplicate records (i.e., two or more records with identical values in all columns), you can set the Unique Values property to Yes, which is equivalent to using a DISTINCT clause in the SQL SELECT statement, to suppress the duplicates.

4. If the query has more than one Table/Query as the source, and duplicate records are found in the output, set the Unique Records Property to Yes (DISTINCTROW clause in the SELECT statement) to suppress duplicates.

Create a Sample Query.

1. Create a new database or one of your existing databases.

2. Import the Order Details and Products Table from the Northwind.mdb sample Database. There is a Combobox in the Order Details Table that retrieves the Product Name from the Products Table. The Product Code in the Order Details Table is linked to the Product Code in the Products Table. Open a new Query in SQL View (without selecting a Table/Query from the displayed list).

3. Copy and paste the following SQL String into the SQL window, and save the Query as Order_DetailsQ.

   SELECT TOP 100 [Order Details].ProductID, [Order Details].UnitPrice
   FROM [Order Details]
   WHERE ((([Order Details].OrderID) Between 10248 And 10300))
   ORDER BY [Order Details].UnitPrice DESC;

4. Open the Query in Design View and check the order of field placement and the Sort Field.

5. Right-click an empty area above the column grid to display the Query Shortcut Menu, select the Properties… option to display the Property Sheet. Check the image below:

   

   Right-click to open Large Image in New Tab/Window.

6. Ensure that the Top Values Property is set to 100 to filter the Query to 100 records with the highest Unit Price values.

7. Change the Query View into Datasheet View to display the output records.  See the image given below:

   

   Right-click to open Large Image in New Tab/Window.

   The Order Details table contains multiple records of the same product under different OrderIDs. In this example, we have intentionally excluded the OrderID field from the data columns, using it only in the criteria to select records with OrderIDs between 10248 and 102300, which also results in some duplicate records. As shown in the image above, several duplicate product records appear in the output. This scenario provides an opportunity to experiment with the Unique Values property settings to remove duplicates.

   Eliminating Duplicate Records.

8. Change the Top Values property to All, and the Query to Datasheet View. The output will be about 150 records for OrderID Range between 10248 and 102300.

9. Change the Query in Design View and display its Property Sheet.

10. Set the Top Values property to 100 and the Unique Values property to Yes.

11. Change the Query in the Datasheet View and inspect the output.

    Now the duplicate records are suppressed (29 records removed), leaving only 71 records in the output. The next property, Unique Records, can be set to Yes to achieve the same result when fields from two or more tables or queries are joined in a query design. This is useful when the output contains duplicate records due to a one-to-many relationship between the tables.

    We have specified 100 records in the Top Values Property, but the Unique Values property setting reduced the number of records to 71 after suppressing duplicates. 

12. Change the Top Values Property setting from 100 to 25% and change the View to Datasheet.

Using the Percentage setting, the output returns only one-fourth of the total records. With Unique Values set to Yes, this yields 18 records, out of a total of 71. With Unique Values set to No, it returns 39 records, which is approximately one-fourth of the total 150 records.

The Top Values Property sets can be a specific number or a percentage of Total Records.

• Octal Numbering System
• Learn Binary Numbering System-2
• Learn the Binary Numbering System
• Scaling the Chart Object
• User and Group Check

Read More

Attachment Field in Access2007

Attachment Field in Access 2007.

Working with images or animations in applications like Microsoft Access has always been enjoyable. I have used the Office Assistant for message boxes in all my Access applications, particularly when creating and deploying common VBA library programs across the network.

Not everyone may know that it is possible to display custom images in the Office Assistant control. I have leveraged this feature to display custom greetings to Access users on special occasions such as Christmas, Eid, and Onam simply by replacing the standard company logo image on the server. I was extremely disappointed when Microsoft removed this feature in Office 2007.

For those still using Microsoft Access 2003 or earlier, the following links provide tips and tricks for using the Office Assistant with MsgBox:

• MsgBox with Office Assistant
• MsgBox with Options Menu
• Office Assistant with Check-Box Menu
• Selection of Office Assistant
• Color and Picture in MsgBox
• Office Assistant and MsgBox Menus
• Office Assistant and MsgBox Menus-2
• Office Assistant and MsgBox Menus-3

The Attachment field type in Access 2007 has significant flexibility, allowing multiple external documents or images to be stored in a single record without inflating the database size. This overcomes the limitations of the older Object Linking and Embedding (OLE) method, which was commonly used in earlier MS Access Versions for storing, editing, or displaying images. While a Hyperlink field can link to only one external file or a single internal object (such as a form or report), the Attachment field supports multiple items per record.

This feature is useful for storing and retrieving essential documents related to a record, such as project site plans, diagrams, contract agreements, engineering drawings, or employees’ family photos. Each attached document or image can be edited in its native application, preserving full functionality.

A Sample Demo.

1. Open Microsoft Access 2007.

2. If you have already created the Northwind 2007 sample database, open it; otherwise, select Local Templates from the Template Categories.

3. Click on the Northwind 2007 Template to select it.

4. Click the Folder icon on the right side of the File Name control, select the required folder, and save Northwind 2007.accdb Database.

5. Open Northwind 2007.accdb Database,

6. Close the Home Form.

7. Select Object Type from the drop-down list in the Navigation Pane and select Tables.

8. Right-click on the Employees table and select Design View from the Shortcut Menu.

9. Use the right scroll bar to move the field list up and bring the last field, Attachments (field type: Attachment), into view.

10. Now that we have seen the Attachment Field in the Employees Table (or you can create a new Table with the Attachment Field if you prefer), close the Design View.

11. Open the Employees Table in Datasheet View.

12. Move the horizontal scroll bar and bring the attachment field into view. See the sample image shown below:

    

    Right-click to open Large Image in New Tab/Window.

13. The second column (highlighted) is the attachment field where a paper clip image and a number in brackets (zero) display how many attachments are in each record.

14. There are no attachments in any of those records so far, so we will add one; double-click the attachment field in the first record.

15. The Attachment control opens up. Click on the Add… Command Button to browse for files on the hard disk. You may select a Word Document, Excel File, PDF file, or Image.

16. Repeat this action to attach more files to the same field.

17. Click OK to close the dialog box.  You will now see a number appearing in brackets, indicating how many attachments are in that field of the record.

18. Double-click the attachment field to open and show the attached files.

19. Click on one of the files to select it.

20. If you click on the Remove Command Button, you can remove the selected attachment, or click Open to open the document in its parent/preview Application.

21. If you right-click the attachment field, the Manage Attachment shortcut menu is displayed.  Selecting this option will open the earlier dialog box we have seen for attaching /removing/opening external files.

Technorati Tags: Microsoft

• Learn Binary Numbering System-2
• Learn the Binary Numbering System
• Scaling the Chart Object
• User and Group Check
• User-Defined Data Type

Read More

PrimaryKey usage with Many Fields

PrimaryKey usage with Many Fields.

When designing a master table in Microsoft Access, ensure that unique values are in a column to make data retrieval easier. For example, in the Employees table of the Northwind.mdb sample database, each employee is identified by a unique employee code. But who ensures that the uniqueness of the values entered into the employee-code field?

It is possible to validate user input using VBA before saving the record. Database systems already have this functionality of a primary key index. The primary key prevents duplicate values and organizes the data automatically in ascending or descending order. 

Usage of Indexes in Programs.

We use these field values in programs to retrieve the specific information very fast.  Let us write a small VBA Routine to see how this is used in programs.

Public Function PrimaryKey_Example1(ByVal EmpCode As Integer)
Dim db As Database, rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenTable)
rst.Index = "PrimaryKey" 'activate the Index on Employee Code

rst.Seek "=", EmpCode 'we are looking for the record of Employee Code provided
'Let us test whether the search for Employee Code was successfull or not
If Not rst.NoMatch Then
    MsgBox "EC: " & rst![ID] & " - " & rst![First Name] & " " & rst![last name]
Else
    MsgBox "EC: " & EmpCode & " Not found!"
End If

Set rst = Nothing
Set db = Nothing

End Function 

The statement rst.Index = "PrimaryKey" activates the index named PrimaryKey. A primary key index can include more than one field, and a table may contain several indexes within the Indexes collection, each defined with different field combinations. If a single field is not sufficient to ensure uniqueness, additional related fields can be included in the index. You can activate the appropriate index based on how you want the data organized before continuing with the processing steps.

The Primary Key Field contents don't always need to be numbers only; they can be any value such as FirstName or LastName fields, or both, or any combination of field types: text, date, number, etc., except the Memo field.  You can give any suitable name to the Index in the Index Name column and use it to activate a specific Primary Index.

Right-click to open Large Image in New Tab/Window.

The Recordset's Seek() method is used for search operations on the Table.  One of the Indexes should be active before the Seek() operation can be executed. 

In the above example, the rst.Seek "=", the Empcode statement checks for the Employee Code passed to the Index_Example1() function.  The statement looks for an exact match ("=" ) very quickly.

When the Index is defined with more than one field, then search keys must be separated with commas in the Seek() method.  Let us modify the above program to use multiple values for the Index Keys in the Seek method, assuming that FirstName and LastName fields are the members of MyIndex.

Index having more than one Field Value.

Public Function PrimaryKey_Example2(ByVal strFirstName As String, ByVal strLastName As String)
Dim db As Database, rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenTable)
rst.Index = "MyIndex" 'activate the Index

'Search for the Employee record
rst.Seek "=", strFirstName, strLastName

'Let us test whether the search for the Employee was successfull or not
If Not rst.NoMatch Then
    MsgBox "Employee: " & rst![ID] & " - " & rst![First Name] & " " & rst![last name]
Else
    MsgBox "Employee: " & strFirstName & " " & strLastName & " Not found!"
End If

Set rst = Nothing
Set db = Nothing

End Function

• Learn the Binary Numbering System
• Scaling the Chart Object
• User and Group Check
• User-Defined Data Type
• Budgeting and Control

Read More

Percentage on Report Summary

Percentage on Report Summary.

Earlier, we solved this problem using a Query in the blog post Percentage on Total Query. This time, let’s see how to achieve the same result on a Report. Our task is to display each detail line’s value as a percentage of the report’s summary total.

The approach is simple:

1. Create a Report that lists the detailed values and includes a report-level summary (such as the total).

2. Insert a Text Box in the Detail section.

3. In the Text Box’s Control Source, write an expression that divides the detail value by the Report Summary total.

This way, each record line will show its proportional contribution to the overall total directly on the Report.

Design a Sample Report to Try.

1. Import the Order Detail Table from Microsoft Access Sample Database: C:\Program Files\Microsoft Office\Office11\Sample\Northwind.mdb

2. Open a new Query in SQL View, without selecting a Table from the displayed list.

3. Copy and paste the following SQL String into the SQL editing window of the new Query:

   SELECT [Order Details].[Order ID], Sum([Order Details].Quantity) AS TQuantity, Sum([Order Details].[Unit Price]) AS UnitPrice, Sum([Unit Price]*[Quantity]) AS TotalPrice
   FROM [Order Details]
   GROUP BY [Order Details].[Order ID];
   

4. Save the Query named OrderSummary.

5. Design a Report with the Detail Section and Report Footer summary controls using the OrderSummary as the Source.

   

   Right-click to open Large Image in New Tab/Window.

6. Click the TextBox at the Report Footer to select it.

7. Display the Property Sheet (F4 or ALT+Enter) of the Text Box.

8. Change the Name Property Value to GTPrice (stands for Grand Total Price).

9. Write the expression =Sum([TotalPrice]) in the Control Source Property.

10. Select the Text Box at the right end of the Detail Section and display its Property Sheet.

11. Write the expression =[TotalPrice]/[GTPrice] in the Control Source Property.

12. In the Format Property, select the Percent format from the drop-down list.

13. Type 2 in the Decimal Places Property.

14. Save and Close the Report.

15. Open the OrderSummary in Print Preview and check the detail line percentage value calculated on the Report Footer Grand Total Price.

Creating Page-Level Totals.

Want to know how to calculate and display Page-wise control totals? You can learn it from here.

• Scaling the Chart Object
• User and Group Check
• User-Defined Data Type
• Budgeting and Control
• Change Secure DB to Unsecured

Read More

Embedded Macros in Access2007

Embedded Macros in Access2007.

The only difference between stand-alone macros and embedded macros is that embedded macros do not appear in the Navigation Pane under the Macros group. Instead, they are written directly within a Form, Report, or a control’s event property. When you copy a Form or Report, its embedded macros go with it.

Let’s now explore how embedded macros are created.

Creating Embedded Macros.

1. Open Microsoft Access 2007.

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 on the right side of the property.

   

   Right-click to open Large Image in New Tab/Window.

7. The Macro Builder option is already in selected state. Click the OK Command Button to accept it.

   The Macro is open in Design View.  The 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 the 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 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.

• User and Group Check
• User-Defined Data Type
• Budgeting and Control
• Change Secure DB to Unsecured
• Menus with Options Group Control

Read More