Introduction to Data Analysis

 Field Selection

 Introduction
 Once a table contains values, you can use them as you see fit. For example, you can retrieve the values of a table. This is referred to as selecting one or more values from a table. To programmatically perform this operation, you use the SQL.
 Practical Learning: Introducing Data Analysis
1. Start Microsoft Access
2. Open the FunDS1 database from Lesson 15
 Selecting a Field

Data selection in the SQL consists of using the SELECT keyword. The primary formula to follow is:

`SELECT What FROM WhatObject;`

The What factor can be the name of a column of a table or query. The WhatObject factor can be the name of a table or a query.

To specify the column you want to select, replace the What factor in the syntax with the name of the desired column. Here is an example:

`SELECT LastName FROM Employees;`
 Selecting All Fields

To select everything from a table or query, you can use the asterisk as the What factor of our formula. For example, to select all records, you would use the statement as follows:

`SELECT * FROM Employees;`

Alternatively, you can precede the * with the ALL keyword. Here is an example:

`SELECT ALL * FROM Employees;`
 Selecting Some Fields

To consider more than one column in a statement, you can list them in the What factor of our formula, separating them with a comma. The formula to use is:

`SELECT Column1, Column2, Column_n FROM WhatObject;`

Here is an example:

```SELECT FirstName, LastName, HourlySalary
FROM Employees;```

When writing the name of a table, a query, or a column, if it's in more than one word, you must include it in square brackets. To be safe, even if the name is in one word, you should still include it in square brackets. Based on this, the above statement would be written as follows:

`SELECT * FROM [Employees];`

Another suggestion you can use is to qualify the name of each column to indicate the table or query it belongs to. To do this, type the name of the table or query, followed by a period, followed by the name of the column or the *. Here is an example:

```SELECT Employees.FirstName, Employees.LastName
FROM Employees;```

You can also delimit each name with square brackets as follows:

```SELECT [Employees].[FirstName], [Employees].[LastName]
FROM [Employees];```
 Using Built-In Functions

The Visual Basic language is equipped with an impressive library of functions. These functions can also be used in queries and even included in SQL statements. The SQL interpreter of Microsoft Access can recognize these functions as long as you use them appropriately.

Imagine that you want to create a column in a query and that column should hold the full name of each employee. In a column of a table, you could use an expression such as:

`Employee: [FirstName] & " " & [MiddleName] & " " & [LastName] `

The SQL statement would be:

```SELECT Employees.DateHired,
[FirstName] & " " & [MiddleName] & " " & [LastName] AS Employee
FROM   Employees;```

Imagine that you only want to include a middle initial instead of the whole middle name. You can use the Left\$ function to retrieve the first character of the middle name and include the call to that function in your query. Here is an example:

```SELECT Employees.DateHired,
[FirstName] & " " & Left([MiddleName],1) & " " & [LastName]
FROM   Employees;
```

In this case, some records don't have a middle initial because they don't have a name. For the records that don't display a middle name, we can write a conditional statement, using the IIf() function, to check it and taking the appropriate action accordingly. Here is the result:

```SELECT Employees.DateHired,
IIf(IsNull([MiddleName]),
[FirstName] & " " & [LastName],[FirstName] & " " &
UCase(Left([MiddleName],1)) & " " & [LastName])
FROM Employees;```

In the same way, you can use any of the built-in functions we reviewed in previous lessons.

 Queries Fundamentals

 Introduction to Queries

After creating a table and filling it up with some values, you can explore them. One way you can do this consists of isolating records based on specific conditions. This technique of isolating records is also referred to as filtering. To filter records of a table and display the results to the user, you have various alternatives.

Data filtering is performed using the data manipulation language (DML) of the SQL and other means provided by Microsoft Access. To filter data, you can either create a query or write a SQL statement.

 Practical Learning: Creating a Query
1. To start a new form, on the Ribbon, click Create
2. In the Forms section, click Form Design
3. Double-click the button at the intersection of the rulers.
In the Properties window, click Record Source, then click its ellipsis button
4. In the Tables property page of the Show Table dialog box, double-click Manufacturers, StoreItems, Categories, and SubCategories
5. On the Show Table dialog box, click Close
6. From the StoreItems list, double-click ItemNumber and DateEntered
7. From the Manufacturers list, double-click Manufacturer
8. From the Categories list, double-click Category
9. From the SubCategories list, double-click SubCategory
10. From the StoreItems list, double-click ItemName, UnitPrice, and DiscountRate
11. In the lower section of the window, click DiscountRate and press Tab
12. Type DiscountAmount: IIf(IsNull(DiscountRate), '', FormatNumber(UnitPrice * DiscountRate))
13. Press Tab
14. Type AfterDiscount: IIf(IsNull(DiscountRate), '', FormatNumber(UnitPrice - DiscountAmount))
15. Close the Query Builder
16. When asked whether you want to save, click Yes
17. Save the form as InventoryAnalysis
18. Complete the design of the form as follows:
19. Switch the form to Form View to see the result:
20. Switch the form to Design View
 Opening a Query

Once a query has been created and saved, it becomes a regular database object. If a query exists already, to use it, the user can open it like a table. To programmatically open a query, you can call the OpenQuery() method of the DoCmd object. This method takes one string argument as the name of the query. Here is an example:

```Private Sub cmdOpenVideoTitles_Click()
DoCmd.OpenQuery "VideoTitles"
End Sub```
 Closing a Query

After using a query, the user can close it like a regular window by clicking its system Close button. To programmatically close a query, you can call the Close() method of the DoCmd object, passing the first argument as acQuery and the second argument as the name of the query. Here is an example:

```Private Sub cmdCloseVideoTitles_Click()
DoCmd.Close acQuery, "VideoTitles"
End Sub```

When this method is called, it checks whether the query is opened. If a query with that name is opened, it would be closed. If no query with that name is opened, nothing would happen.

 Indexes

 Introduction

As you may know from reading books, an index is a list of words that makes it easy to locate information. When it comes to a book, an author or editor can check each chapter to get key words, create a list of those word and put that list at the end of the book. When it comes to a database, you can ask the database engine to use the values of one or more columns as the basis of an index. In other words, you would create an index based on one or more fields and the databse engine would take care of everytihng behind the scenes, which would consist of eventually find the records when a request is made.

 Creating an Index

To programmatically create an index in SQL, use the following formula:

`CREATE [UNIQUE] INDEX IndexName ON ObjectName(Column(s) Options);`

The CREATE INDEX expression and the ON keyword are required. Like every object, an index must have a name, in this case ObjectName. You must also specify the table or a query that holds the list whose index you wan to create. You must also specify the column(s) that has(have) the values used for the index. Here is an example of creating an index:

```Private Sub cmdCreateTable_Click()
DoCmd.RunSQL "CREATE TABLE Contractors" & _
"(" & _
"      ContractorCode int, " & _
"      FirstName varchar(20)," & _
"      LastName varchar(20) NOT NULL," & _
"      Title varchar(50) NULL," & _
"      HourlySalary double" & _
");"
End Sub

Private Sub cmdCreateIndex_Click()
DoCmd.RunSQL "CREATE INDEX LocateContractors ON Contractors(ContractorCode);"
End Sub```

In most cases, you use only one column as the basis of your index. You can also use more than one column. In this case, in the parentheses of the name of the table, list the columns separated by commas. Here is an example:

```Private Sub cmdCreateIndex_Click()
DoCmd.RunSQL "CREATE INDEX FindContractors " & _
"ON Contractors(LastName, FirstName);"
End Sub```

By habit or pure tradition, most people start the name of an index with either idx or IDX_.

 Options on Indexes

There are many characteristics to apply to an index.

If you create an index on a column that includes null values, the index may end up with null values. If you want the database engine to exclude null values, add a WITH DISALLOW NULL clause. Here is an example:

```Private Sub cmdCreateIndex_Click()
DoCmd.RunSQL "CREATE INDEX IDX_Contractors " & _
"ON Contractors(ContractorCode) " & _
"WITH DISALLOW NULL;"
End Sub```

Another option is to ask the databsae engine to ignore null values in the index you are creating. To do this, add a WITH IGNORE NULL clause. Here is an example:

```Private Sub cmdCreateIndex_Click()
DoCmd.RunSQL "CREATE INDEX IDX_Contractors " & _
"ON Contractors(ContractorCode) " & _
"WITH IGNORE NULL;"
End Sub```

In most cases, you want to make sure that all words used in an index are unique (an index with a repeating word can be confusing). Normally, this is the default characteristic of an index. Still, if you want to enforce it, you can precede the word INDEX with UNIQUE. Here is an example:

```Private Sub cmdCreateIndex_Click()
DoCmd.RunSQL "CREATE UNIQUE INDEX IDX_Contractors " & _
"ON Contractors(ContractorCode);"
End Sub```

In most cases, an index is created on the column(s) used as the primary key. Normally, if your table formally includes a primary key, that (those) column(s) is(are) used as the index. Otherwise, when creating an index, if you want to indicate that it is based on the column(s) used as the primary key, add a WITH PRIMARY clause to it. Here is an example:

```Private Sub cmdCreateTable_Click()
DoCmd.RunSQL "CREATE TABLE Contractors" & _
"(" & _
"      ContractorCode int, " & _
"      FirstName varchar(20)," & _
"      LastName varchar(20) NOT NULL," & _
"      Title varchar(50) NULL," & _
"      HourlySalary double" & _
");"
End Sub

Private Sub cmdCreateIndex_Click()
DoCmd.RunSQL "CREATE UNIQUE INDEX IDX_Contractors " & _
"ON Contractors(ContractorCode) " & _
"WITH PRIMARY;"
End Sub```
 Deleting an Index

To delete an index, use the following forrmula:

`DROP INDEX IndexName ON TableName;`

Here is an example:

```Private Sub cmdDeleteIndex_Click()
DoCmd.RunSQL "DROP INDEX IDX_Contractors ON Contractors;"
End Sub```
 Sorting Records

 Introduction

One of the actions you can take consists of rearranging the list of records in an order other than the one in which they were entered. For example, a user enters the list of students in the order they arrive. At one time the user may want to see a list of students in alphabetical order based on they last names. Rearranging a list of records is referred to as sorting. Microsoft Access provides the means of sorting records on all database objects, including tables, queries, and forms.

To programmatically sort records on a table, a query, or a form, etc, call its OrderBy() method and pass it the name of the column on which the sorting would be based. After calling OrderBy(), access its OrderByOn Boolean property and set its value to True. Here is an example:

```Private Sub cmdSortByLastName_Click()
OrderBy = "LastName"
OrderByOn = True
End Sub```

To remove the sorting, access the OrderByOn and set its value to False.

 Practical Learning: Introducing Sorting Records
1. On the Ribbon, click Design and, in the Controls section, click More.
Make sure the Use Control Wizards option is selected .
In the Controls section of the Ribbon, click the Combo Box and click the left side under the Form Footer bar
2. In the first page of the Combo Box Wizard, click the second radio button (I Will Type In The Values That I Want) and click Next
3. Click under Col1 and type Item Number
4. Press the down arrow key and type Date Entered
5. Complete the list with Manufacturer, Category, Sub-Category, Item Name, Unit Price, and Price After Discount
6. Click Next
7. In the third page of the wizard, accept the first radio button and click Next
8. Change the label to Sort by:
9. Click Finish
10. In the Properties window, change its Name to cbxColumnNames
11. In the Controls section of the Ribbon, click the Combo Box and click on the right side of the previously added combo box in the Form footer section
12. In the first page of the Combo Box Wizard, click the second radio button (I Will Type In The Values That I Want) and click Next
13. Click under Col1 and type Ascending Order
14. Press the down arrow key and type Descending Order

15. Click Next
16. In the third page of the wizard, accept the first radio button and click Next
17. Change the label to in and click Finish
18. In the Properties window, change the combo box' Name to cbxSortOrder
19. Using the objects in the Controls section of the Ribbon, complete the design of the Footer section as follows:

 Control Name Caption Additional Properties Combo Box cbxColumnNames Sort by: Row Source Type: Value List Combo Box cbxSortOrder in Button cmdRemoveFilterSort Remove Filter/Sort
 Sorting Records With SQL

In the SQL, to sort a field in ascending order, you can include the ORDER BY clause in your statement. The syntax used would be:

`SELECT What FROM WhatObject ORDER BY WhatField;`

The field used as the basis must be recognized as part of the selected columns. Imagine you have created a list of staff members made of their first and last names in a table named Employees. If you want to order the list in alphabetical order based on the LastName column, you would use a statement such as:

`SELECT FirstName, LastName FROM Employees ORDER BY LastName;`

If you use the * operator to include all fields, you can order the list based on any of the table's fields, as we learned during data analysis. Imagine that you have created a query that includes all fields. The following statement would list the records of the Employees table based on the alphabetical order of the LastName column:

`SELECT * FROM Employees ORDER BY LastName;`

By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in ascending order of a query that includes the first and last names, the above statement can also be written as follows:

`SELECT FirstName, LastName FROM Employees ORDER BY LastName ASC;`

The second statement can be written as:

`SELECT * FROM Employees ORDER BY LastName ASC;`

If you want to sort records in descending order, use the DESC keyword instead. It produces the opposite result to the ASC effect. To sort records in reverse alphabetical order, the above two statements can be written as:

`SELECT FirstName, LastName FROM Employees ORDER BY LastName DESC;`

The second statement can be written as:

`SELECT * FROM Employees ORDER BY LastName DESC;`

If you want to programmatically create a query from one of these statements, remember that you can use the CreateQueryDef() method.

 Practical Learning: Sorting Records on a Form
1. Double-click the Sort By combo box to access its properties and click the Events tab
2. Double-click After Update, then click its ellipsis button
3. Implement the event as follows:
```Option Compare Database
Option Explicit

Private strColumnName As String
Private strSortOrder As String

Private Sub cbxColumnNames_AfterUpdate()
On Error GoTo cbxColumnNames_AfterUpdate_Error

' Get the string selected in the Sort By combo box
' and find its equivalent column name
If cbxColumnNames = "Item Number" Then
strColumnName = "ItemNumber"
ElseIf cbxColumnNames = "Date Entered" Then
strColumnName = "DateEntered"
ElseIf cbxColumnNames = "Manufacturer" Then
strColumnName = "Manufacturer"
ElseIf cbxColumnNames = "Category" Then
strColumnName = "Category"
ElseIf cbxColumnNames = "Sub-Category" Then
strColumnName = "SubCategory"
ElseIf cbxColumnNames = "Item Name" Then
strColumnName = "ItemName"
ElseIf cbxColumnNames = "Unit Price" Then
strColumnName = "UnitPrice"
ElseIf cbxColumnNames = "Price After Discount" Then
strColumnName = "AfterDiscount"
Else
strColumnName = ""
End If

' Sort the records based on the column name from the combo box
Me.OrderBy = strColumnName
Me.OrderByOn = True

' Set the In combo box to ascending order by default
cbxSortOrder = "Ascending Order"

Exit Sub

cbxColumnNames_AfterUpdate_Error:
MsgBox "There was an error when trying to sort the records. " & _
"Please report the error as follows." & vbCrLf & _
"Error #: " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"he is not sleeping at this time."
Resume Next
End Sub```
4. In the Object combo box, select cbxSortOrder
5. In the Procedure combo box, select AfterUpdate
6. Implement the event as follows:
```Private Sub cbxSortOrder_AfterUpdate()
On Error GoTo cbxSortOrder_AfterUpdate_Error

' Unless the user selects Descending Order...
If cbxSortOrder = "Descending Order" Then
strSortOrder = "DESC"
Else ' We will consider that it should be sorted in ascending order
strSortOrder = "ASC"
End If

Me.OrderBy = strColumnName & " " & strSortOrder
Me.OrderByOn = True
Exit Sub

cbxSortOrder_AfterUpdate_Error:
MsgBox "There was an error when trying to sort the records. " & _
"Please report the error as follows." & vbCrLf & _
"Error #: " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"he is not sleeping at this time."
Resume Next
End Sub```
7. In the Object combo box, select cmdRemoveFilterSort
8. Implement the event as follows:
```Private Sub cmdRemoveFilterSort_Click()
Me.OrderBy = ""
Me.OrderByOn = False

Me.cbxColumnNames = "Item Number"
Me.cbxSortOrder = "Ascending Order"
End Sub```
10. Using the combo box in the Form Footer section, try sorting the records by the Manufacturer
11. Click the Remove Filter/Sort button
12. Sort the records by Unit Price in descending order
13. Close the form
14. When asked whether you want to save, click Yes
 Sorting and Data Joins

If you create a SQL expression what involves more than one table, you can use one of the fields to sort the records. To do this, after the join expression, add your sort clause. Here is an example:

```Private Sub cmdUseJoin_Click()
RecordSource = "SELECT Employees.EmplNbr, " & _
"       Employees.FirstName, " & _
"       Employees.LastName, " & _
"       Departments.Department " & _
"FROM Employees " & _
"INNER JOIN Departments " & _
"      ON Employees.DeptCode = Departments.DeptCode " & _
"ORDER BY Employees.LastName;"

txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
txtFirstName.ControlSource = "FirstName"
txtLastName.ControlSource = "LastName"
txtDepartment.ControlSource = "Department"
End Sub```

Of course, you can use any column in the SQL statement. Here is an example that uses a column of the second table:

```Private Sub cmdUseJoin_Click()
RecordSource = "SELECT Employees.EmplNbr, " & _
"       Employees.FirstName, " & _
"       Employees.LastName, " & _
"       Departments.Department " & _
"FROM Employees " & _
"INNER JOIN Departments " & _
"      ON Employees.DeptCode = Departments.DeptCode " & _
"ORDER BY Departments.Department;"

txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
txtFirstName.ControlSource = "FirstName"
txtLastName.ControlSource = "LastName"
txtDepartment.ControlSource = "Department"
End Sub```

 Data Filtering

 Introduction

Data filtering consists of isolating particular records based on a condition, also called a criterion or criteria. You start data filtering by selecting one or more columns that hold data you are interested in. This allows you to have access to all values of the table. Then, among the selected columns, instead using all available records, you specify a condition. Only the records that abide by the rule(s) you set would be produced.

 Practical Learning: Introducing Data Filtering
1. In the Navigation Pane, right-click InventoryAnalysis and click Design View
2. In the Controls section of the Ribbon, click the Combo Box and click under the Form Footer bar
3. In the first page of the wizard, click the second radio button
4. Click Next
5. In the second page of the wizard, click under Col1 and type All
6. Press the down arrow key and type Men
7. Press the down arrow key and type Girls
8. Press the down arrow key and type Boys
9. Press the down arrow key and type Women

10. Click Next
11. Click Next
12. Click Finish
13. In the Controls section of the Ribbon, click the Combo Box and click under the Form Footer bar
14. In the first page of the wizard, click the second radio button
15. Click Next
16. In the second page of the wizard, click under Col1 and type lower than
17. Press the down arrow key and type lower than or equal to
18. Press the down arrow key and type equal to
19. Press the down arrow key and type higher than or equal to
20. Press the down arrow key and type higher than
21. Press the down arrow key and type different from
22. Click Next
23. Click Next
24. Click Finish
25. In the Controls section of the Ribbon, click the Text Box and click under the Form Footer bar
26. Delete its accompanying label
27. In the Controls section of the Ribbon, click the Button and click under the Form Footer bar. If the wizard starts, click Cancel
28. Complete the design of the form as follows:

 Control Name Caption Combo Box cbxCategories Show items for Combo Box cbxOperators Show items whose price is Text Box txtUnitPrice Button cmdShowPrices Show
29. Save the form
 WHERE is the Criterion?

To specify a condition, you combine the SELECT expression with the WHERE operator. The basic formula to follow is:

`SELECT What FROM WhatObject WHERE Expression;`

The What factor is used to specify the column(s) whose data would be considered. The WhatObject is the name of the table (or query) that holds the data. The SELECT and the WHERE keywords are required. The Expression is the condition that will set the rule to follow. The Expression is a real expression. It can be made of the name of a column involved in a special conditional statement.

 Programmatically Filtering Data

To programmatically perform data filtering on a table, a query, a form, or a report, you can use its Filter property and assign it the WHERE condition. To apply the filter, access its FilterOn property and assign it a value of True. To remove the filter, assign the False value to its FilterOn property.

 Practical Learning: Filtering Data
1. On the form, click the Show Items For combo box
2. In the Properties window, click Event and double-click After Update
3. Click its ellipsis button and implement the event as follows:
```Private Sub cbxCategories_AfterUpdate()
On Error GoTo cbxCategories_AfterUpdate_Error

Filter = "Category = '" & cbxCategories & "'"
FilterOn = True

Exit Sub

cbxCategories_AfterUpdate_Error:
MsgBox "There was an error when trying to filter the records. " & _
"Please report the error as follows." & vbCrLf & _
"Error #: " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"he is not sleeping at this time."
Resume Next
End Sub```
4. Locate the Click event of the cmdRemoveFilterSort button and change it as follows:
```Private Sub cmdRemoveFilterSort_Click()
Me.OrderBy = ""
Me.Filter = ""

Me.OrderByOn = False
Me.FilterOn = False

Me.cbxColumnNames = "Item Number"
Me.cbxSortOrder = "Ascending Order"
cbxCategories = ""
cbxOperator = ""
txtUnitPrice = "0.00"
End Sub```
6. Switch the form to Form View
7. In the Show Items For combo box, select Boys
8. Click the Show button

9. Save the form
 Data Filtering With Operators

Imagine that you have a table of students and you want to extract only a list of male students. Yyou would write the condition as Gender = Male. This would be done as follows:

`SELECT * FROM Students WHERE Gender="Male";`

Remember that you can use the square brackets around the name of a table, query, or column in a SQL statement. Here is an example:

`SELECT * FROM [Students] WHERE [Gender]="Male";`

Here is an example where the names of of tables and columns are included in square brackets and the names of columns are qualified with periods:

```SELECT [Students].[FirstName], [Students].[LastName]
FROM [Students]
WHERE [Students].[Gender]="Male";```

The opposite to the equality condition is the not equality. For example, instead of getting a list of male students as above, to get a list of students who are not male, you can write the condition as <>"Male" as follows:

`SELECT * FROM Students WHERE Gender<>"Male";`
 Practical Learning: Filtering Records WHERE True
1. In the Object combo box, select cmdShowPrices
2. Implement the event as follows:
```Private Sub cmdShowPrices_Click()
On Error GoTo cmdShowPrices_Click_Error

Dim strFilter As String
Dim dUnitPrice As Double

If Me.cbxOperator = "lower than" Then
strFilter = "UnitPrice < "
ElseIf Me.cbxOperator = "lower than or equal to" Then
strFilter = "UnitPrice <= "
ElseIf Me.cbxOperator = "equal to" Then
strFilter = "UnitPrice = "
ElseIf Me.cbxOperator = "higher than or equal to" Then
strFilter = "UnitPrice >= "
ElseIf Me.cbxOperator = "higher than" Then
strFilter = "UnitPrice > "
ElseIf Me.cbxOperator = "different from" Then
strFilter = "UnitPrice <> "
Else
MsgBox "You must select an operation to perform.", _
vbOKOnly Or vbInformation, "Fun Department Store"
Exit Sub
End If

If IsNull(txtUnitPrice) Then
MsgBox "You must specify a unit price.", _
vbOKOnly Or vbInformation, "Fun Department Store"
Exit Sub
End If

Filter = strFilter & CDbl(txtUnitPrice)
FilterOn = True

Exit Sub

cmdShowPrices_Click_Error:
MsgBox "There was an error when trying to sort the records. " & _
"Please report the error as follows." & vbCrLf & _
"Error #: " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"he is not sleeping at this time."
Resume Next
End Sub```
3. In the Object combo box, select cmdClose
4. Implement the event as follows:
```Private Sub cmdClose_Click()
On Error GoTo cmdClose_Click_Err

DoCmd.Close , ""

cmdClose_Click_Exit:
Exit Sub

cmdClose_Click_Err:
MsgBox "Error #: " & Err.Number & vbCrLf & _
"Description: " & Err.Description

Resume cmdClose_Click_Exit

End Sub```
6. Switch the form to Form View
7. In the Show Items Whose Price Is combo box, select higher than or equal to
8. In the next text boxk, type 250.00
9. Click the Show button
10. Save and close the form
 Conditionally Opening a Form or Report

Imagine you create a form that shows the records of a table or query. When navigating among the records, imagine the user comes to a particular record he or she wants to print. If you simply write normal code to open the related report, it would show all records from the beginning. Fortunately, Microsoft Access provides an easy mechanism to execute such a scenario.

If you add a button to a form in Design View, if the Button Wizard starts, you can follow it to select the report that would be opened when a button is clicked. Microsoft Access would write the code for you:

```Private Sub cmdPreviewRentalOrder_Click()
On Error GoTo Err_cmdPreviewRentalOrder_Click

Dim stDocName As String
Dim strWHERECondition As String

stDocName = "RentalOrders"
strWHERECondition = "RentalOrderID = " & RentalOrderID
DoCmd.OpenReport stDocName, acPreview, , strWHERECondition

Exit_cmdPreviewRentalOrder_Click:
Exit Sub

Err_cmdPreviewRentalOrder_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRentalOrder_Click

End Sub```