Lesson 24: Data Selections

	Introduction to Data Selections

Field Selection

Introduction

The main purpose of data filtering consists of selecting records. As you know already, records are stored in tables but they can be identified by the columns of a table. Therefore, before filtering records, you must first specify the columns that would be involved in your filtering.

Practical Learning: Introducing Filtering and Sorting

Start Microsoft Access
From the resources that accompany these lessons, open the ROSH1 database
In the navigation Pane, right-click Students and click Design View
In the Controls section of the Ribbon, click the Combo Box and click the left side under the Form Footer bar. If the Combo Box wizard starts, click Cancel
In the Properties window, change its Name to cbxColumnNames
Change the caption of the accompanying label to Sort by:
Make sure the Control Wizard button of the Ribbon is down. 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
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
Click under Col1 and type Ascending Order
Press the down arrow key and type Descending Order
Click Next
In the third page of the wizard, accept the first radio button and click Next
Change the label to in and click Finish
In the Properties window, change the combo box' Name to cbxSortOrder

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
Button	cmdClose	Close

Access the properties of the form by double-clicking the button at the intersection of the rulers and click the Events tab
Double-click On Open to select [Event Procedure], then click its ellipsis button

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 of our syntax 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 syntax, separating them with a comma except for the last column. The syntax to use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

Here is an example:

SELECT FirstName, LastName, HourlySalary
FROM Employees;

As mentioned earlier, to programmatically create a query, you can pass the SQL statement as the second argument of the CreateQueryDef() method. Here is an example that uses the Microsoft Access Library:

Private Sub cmdCreateQuery_Click()
    Dim curDatabase As Object
    Dim qryEmployees As Object
    Dim strStatement As String

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    strStatement = "SELECT DateHired, FirstName, " & _
                   "LastName, HourlySalary FROM Employees;"
    ' Create a new query named EmployeesInfo
    Set qryEmployees = curDatabase.CreateQueryDef("EmployeesInfo", strStatement)
End Sub

You can use the same approach with DAO.

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];

Data Analysis: 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.

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: Filtering and Sorting Records on a Form

Implement the event as follows:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo FormOpen_Err
    
    Dim curDatabase As Object
    Dim strColumnsNames As String
    Dim tblStudents As Object
    Dim fldColumn As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Students
    Set tblStudents = curDatabase.TableDefs("Students")
    
    ' Retrieve the name of each column of the table and
    ' store each name in the strColumnsNames string
    For Each fldColumn In tblStudents.Fields
        strColumnsNames = strColumnsNames & fldColumn.Name & ";"
    Next
    
    ' Set the strColumnsNames string as the data source of the combo box
    cbxColumnNames.RowSource = strColumnsNames
    
    ' Select the name of the first column as the default of the combo box
    cbxColumnNames = tblStudents.Fields(0).Name
    cbxSortOrder = "Ascending Order"
    
    Exit Sub

FormOpen_Err:
    MsgBox "There was an error when opening the form." & vbCrLf & _
           "Error #: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description & vbCrLf & _
           "Please contact the program vendor if " & _
       "he is not sleeping at this time."
    Resume Next
End Sub

In the Object combo box, select cbxColumnNames

In the Procedure combo box, select After Update and implement the event as follows:

Private Sub cbxColumnNames_AfterUpdate()
    Dim strColumnName As String
    Dim strSortOrder As String
    
    ' Get the name of the column from the Sort By combo box
    strColumnName = cbxColumnNames
    
    ' 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"
End Sub

In the Object combo box, select cbxSortOrder

In the Procedure combo box, select AfterUpdate and implement the event as follows:

Private Sub cbxSortOrder_AfterUpdate()
    Dim strColumnName As String
    Dim strSortOrder As String
    
    strColumnName = cboColumnNames
    
    ' 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
End Sub

In the Object combo box, select cmdRemoveFilterSort

Implement the event as follows:

Private Sub cmdRemoveFilterSort_Click()
    Me.OrderBy = "StudentID"
    Me.OrderByOn = True
    
    Me.cbxColumnNames = "StudentID"
    Me.cbxSortOrder = "Ascending Order"
End Sub

Return to the form and save it
Using the combo box in the Form Footer section, try sorting the records by the LastName is descending order
Click the Remove Filter/Sort button
Try sorting the records in ascending order based on the date of birth
Notice that the records with no date of birth appear first
Close the form
When asked whether you want to save, click Yes

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

In the Navigation Pane, right-click SelectStudents and click Design View
In the Controls section of the Ribbon, make sure the Use Control Wizard button is clicked .
Click the Combo Box and click under the Form Footer bar
In the first page of the wizard, click the second radio button
Click Next
In the second page of the wizard, click under Col1 and type All
Press the down arrow key and type Male
Press the down arrow key and type Female
Press the down arrow key and type Unknown
Click Next
Click Next
Click Finish

Complete the design of the form as follows:

Control	Name	Caption
Combo Box	cbxGenders	Show
Label		Students
Button	cmdStudentsByGender	Show

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, 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 to False value to its FilterOn property.

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

Right-click the Show button and click Build Event...
In the Choose Builder dialog box, double-click Code Builder

Implement the event as follows:

Private Sub cmdStudentsByGender_Click()
    If IsNull(cbxGenders) Then
        RecordSource = "SELECT * FROM Students"
    End If
    
    If cbxGenders = "All" Then
        RecordSource = "SELECT * FROM Students"
    End If
    
    If cbxGenders = "Male" Then
        RecordSource = "SELECT * FROM Students WHERE Gender = 'Male'"
    End If
    
    If cbxGenders = "Female" Then
        RecordSource = "SELECT * FROM Students WHERE Gender = 'Female'"
    End If
    
    If cbxGenders = "Unknown" Then
        RecordSource = "SELECT * FROM Students WHERE Gender = 'Unknown'"
    End If
End Sub

Return to Microsoft Access
Switch the form to Form View
In the bottom combo box, select Female
Click the Show button
Save 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

Details on Column Selection

Hiding a Column

Consider a table created with the following code:

Private Sub cmdTable_Click()
    Dim dbCurrent As Object
    Dim tblEmployees As Object
    Dim fldEmployee As Object
    
    Set dbCurrent = CurrentDb
    
    Set tblEmployees = dbCurrent.CreateTableDef("Employees")
    
    Set fldEmployee = tblEmployees.CreateField("DateHired", dbDate)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("FirstName", dbText, 40)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("LastName", dbText, 40)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("Department", dbText, 50)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("HourlySalary", dbDouble)
    tblEmployees.Fields.Append fldEmployee
    
    dbCurrent.TableDefs.Append tblEmployees
    MsgBox "A table named Employees has been created"
    
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('10/22/2006', 'Jerry', 'Carlton', " & _
                 "'Corporate', 22.45);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('05/12/2000', 'Christopher', 'Haloux', " & _
                 "'Accounting', 28.05);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('12/05/2005', 'Grattie', 'Sanion', " & _
                 "'Human Resources', 18.45);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('08/08/2002', 'Nicolas', 'Sands', 'IT', 19.85);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('12/04/1998', 'Dennis', 'Fleming', " & _
                 "'Corporate', 14.55);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('06/10/2008', 'Julie', 'Woods', " & _
                 "'Corporate', 30.2);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('12/05/2005', 'Barbara', 'Seans', " & _
                 "'Human Resources', 18.45);"
End Sub

Imagine that you want to create a list of employees using their names and you want to show their work department. Imagine that you want the list to include only the employees who work at the corporate office, that is, employees whose Department value is Corporate. You can create a SQL statement as follows:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Employees.Department " & _
                   "FROM Employees " & _
                   "WHERE Employees.Department = 'Corporate';"

    txtDateHired.ControlSource = "DateHired"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

Columns

Notice that the Department column is included as part of the SELECT statement. Since the list includes employees who work at the corporate office and we know that this is what the query would produce, it becomes redundant, quite useless, to include the Department column in our list. Therefore, we can hide it. To create such a query, omit the column in the SELECT statement but include it as part of your WHERE condition. Here is an example:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName " & _
                   "FROM Employees " & _
                   "WHERE Employees.Department = 'Corporate';"

    txtDateHired.ControlSource = "DateHired"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
End Sub

Notice that the Department column is not included as part of the SELECT statement. This would produce:

Employees

The Alias Name of a Column

In your SELECT statement, after specifying the column(s) as we have done so far, when you execute the query, the name of each column would appear as the column header. When creating the table, if you had explicitly specified a caption for the column, the caption would display also when the query shows its result. On a query, instead of the default name used as the caption, if you want, you can display any string of your choice for a column header. To specify a column header other than the name of the column, if you are visually creating the SQL statement in the Select Query window, in the box that receives the name of the selected column, type the desired string, followed by a colon ":", followed by the actual name of the column from the table.

In Microsoft Access (unlike many other database environments), if the column has a caption that was set in the table, creating the alias would not have any effect in the query.

If you are manually writing the SQL statement, type the actual name of the column, followed by the AS keyword, followed by the desired string. If the desired column header is in one word, you can simply type it. Here is an example:

SELECT DateHired AS EmployedSince, LastName, HourlySalary
FROM Employees;

If the string is in more than one word or contains a symbol that could be confused with an operator, you should include the whole string between an opening square bracket and a closing square bracket. In fact, you should always include the string between square brackets. Here are two examples:

SELECT DateHired AS [EmployedSince], LastName, HourlySalary AS [Pay Rate]
FROM Employees;

You can also include the string in single-quotes. Here are two examples:

SELECT DateHired AS [EmployedSince], LastName, HourlySalary AS 'Pay Rate'
FROM Employees;

A Combination or Expression of Columns

When creating a query, instead of having separate columns, you can combine two or more columns to create a string or a value that is in fact an expression. For example, you can combine a first name and a last name to create a full name.

An expression that combines columns can be performed on text-based columns. such as a first name being added to a last name to get a full name. To create this type of expression, you can use the + operator to concatenate the string as in FirstName + " " + LastName. After creating the expression, because the result is not part of the table (or the query) from which the query is based, you must give an alias name to the result. Here is an example:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.LastName + ', ' + " & _
                   "       Employees.FirstName As FullName, " & _
                   "       Employees.Department " & _
                   "FROM Employees;"

    txtDateHired.ControlSource = "DateHired"
    txtFullName.ControlSource = "FullName"
    txtDepartment.ControlSource = "Department"
End Sub

Columns

Instead of the addition operator, you can use the ampersand & operator to perform the same operator. Instead of the single-quotes used to add a string in the expression, you can use double-quotes.

Besides string, you can create a type of expression that uses a date on the table, add a number to it to get a date on another day. An expression can also be used to perform a calculation on two or more columns such as employees weekly hours multiplied by their hourly salary to get their weekly salary.

Field Selection Into a Record Set

Introduction

In the previous lesson, we saw different ways of creating simple SELECT statement and we saw how to use them. We saw that, using fields from a table, we could create a SELECT statement and use it to generate a query in the Microsoft Access Object Library or in DAO:

Private Sub cmdCreateQuery_Click()
    Dim curDatabase As DAO.Database
    Dim qryEmployees As DAO.QueryDef
    Dim strStatement As String

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    strStatement = "SELECT FirstName, LastName FROM Employees;"
    ' Create a new query named EmployeesInfo
    Set qryEmployees = curDatabase.CreateQueryDef("EmployeesInfo", strStatement)
End Sub

In the same way, we can also call the RunSQL() method of the DoCmd object and pass a SQL statement to it. Besides these techniques, we can also use a record set object.

Practical Learning: Introducing Domain Aggregate Functions

Create a blank database named Greenbelt Auto Parts1
Close the default table without saving it
From the resources that accompany these lessons, import the AutoParts table from the Exercise1 database
Click the Office button and click Access Options...
Click Current Database and click Overlapped Windows
Click OK and click OK
Close Microsoft Access
Start Microsoft Access again and open the Greenbelt Auto Part database
On the Ribbon, click Create
In the Forms section, click Form Design
In the Controls section of the Ribbon, click the Button and click the Detail section of the form. If the Button Wizard starts, click Cancel
On the form, double-click the button to access its Properties window.
Change its Name to cmdCreateTables
Change its Caption to Create Table
Right-click the Create Tables button and click Build Event...
In the Choose builder dialog box, double-click Code Builder

Implement the event as follows:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Invoices(" & _
                 "InvoiceID AutoIncrement(1, 1) " & _
                 "           primary key not null, " & _
                 "InvoiceDate Date, CustomerName varchar(80), " & _
                 "CustomerAddress varchar(80), CustomerCity varchar(50), " & _
                 "CustomerState varchar(50), " & _
                 "CustomerZIPCode varchar(40), Part1Number varchar(20), " & _
                 "Part1Name varchar(50), Part1Quantity Integer, " & _
                 "Part1SubTotal double, Part2Number varchar(20), " & _
                 "Part2Name varchar(50), Part2Quantity Integer, " & _
                 "Part2SubTotal double, Part3Number varchar(20), " & _
                 "Part3Name varchar(50), Part3Quantity Integer, " & _
                 "Part3SubTotal double, Part4Number varchar(20), " & _
                 "Part4Name varchar(50), Part4Quantity Integer, " & _
                 "Part4SubTotal double, Part5Number varchar(20), " & _
                 "Part5Name varchar(50), Part5Quantity Integer, " & _
                 "Part5SubTotal double, Part6Number varchar(20), " & _
                 "Part6Name varchar(50), Part6Quantity Integer, " & _
                 "Part6SubTotal double, " & _
                 "PartsTotal double, TaxRate double, " & _
                 "TaxAmount double, InvoiceTotal double);"
    MsgBox "A table named Invoices has been created."
End Sub

Return to Microsoft Access
Switch the form to Form View and click the button
Click OK each time to create the table and create its records
Close the form
When asked whether you want to save it, click No
On the Ribbon, click Create
In the Forms section, click Form Design
Save the form as NewInvoice

Design the form as follows (You don't have to follow the exact same design; you only need to have the same controls and names):

Control	Caption	Name	Format	Decimal Places
Text Box	Invoice Date:	txtInvoiceDate	Short Date
Group Box	Customer Information
Text Box	Name:	txtCustomerName
Text Box	Address:	txtCustomerAddress
Text Box	City:	txtCustomerCity
Text Box	State:	txtCustomerState
Text Box	ZIP Code:	txtCustomerZIPCode
Label	Part #
Label	Part Name/Description
Label	Unit Price
Label	Qty
Label	Sub Total
Text Box		txtPart1Number
Text Box		txtPart1Name
Text Box		txtPart1UnitPrice	Fixed
Text Box		txtPart1Quantity	Fixed	0
Text Box		txtPart1SubTotal	Fixed
Text Box		txtPart2Number
Text Box		txtPart2Name
Text Box		txtPart2UnitPrice	Fixed
Text Box		txtPart2Quantity	Fixed	0
Text Box		txtPart2SubTotal	Fixed
Text Box		txtPart3Number
Text Box		txtPart3Name
Text Box		txtPart3UnitPrice	Fixed
Text Box		txtPart3Quantity	Fixed	0
Text Box		txtPart3SubTotal	Fixed
Text Box		txtPart4Number
Text Box		txtPart4Name
Text Box		txtPart4UnitPrice	Fixed
Text Box		txtPart4Quantity	Fixed	0
Text Box		txtPart4SubTotal	Fixed
Text Box		txtPart5Number
Text Box		txtPart5Name
Text Box		txtPart5UnitPrice	Fixed
Text Box		txtPart5Quantity	Fixed	0
Text Box		txtPart5SubTotal	Format: Fixed
Text Box		txtPart6Number
Text Box		txtPart6Name
Text Box		txtPart6UnitPrice	Fixed
Text Box		txtPart6Quantity	Fixed	0
Text Box		txtPart6SubTotal	Fixed
Line
Text Box	Items Total:	txtPartsTotal	Fixed
Text Box	Tax Rate:	txtTaxRate	Percent
Text Box	Tax Amount:	txtAmountAmount	Fixed
Text Box	Invoice Total:	txtInvoiceTotal	Fixed
Line
Button	btnSave	Save
Button	btnClose	Close

Right-click the Reset Invoice button and click Build Event...
Double-click Code Builder

Implement the event as follows:

Private Sub cmdResetInvoice_Click()
    Me.txtInvoiceDate = Date: Me.txtCustomerName = ""
    Me.txtCustomerAddress = "": Me.txtCustomerCity = ""
    Me.txtCustomerState = "": Me.txtCustomerZIPCode = ""
    Me.txtPart1Number = "": Me.txtPart1Name = ""
    Me.txtPart1UnitPrice = "0.00": Me.txtPart1Quantity = "0"
    Me.txtPart1SubTotal = "0.00": Me.txtPart2Number = ""
    Me.txtPart2Name = "": Me.txtPart2UnitPrice = "0.00"
    Me.txtPart2Quantity = "0": Me.txtPart2SubTotal = "0.00"
    Me.txtPart3Number = "": Me.txtPart3Name = ""
    Me.txtPart3UnitPrice = "0.00": Me.txtPart3Quantity = "0"
    Me.txtPart3SubTotal = "0.00": Me.txtPart4Number = ""
    Me.txtPart4Name = "": Me.txtPart4UnitPrice = "0.00"
    Me.txtPart4Quantity = "0": Me.txtPart4SubTotal = "0.00"
    Me.txtPart5Number = "": Me.txtPart5Name = ""
    Me.txtPart5UnitPrice = "0.00": Me.txtPart5Quantity = "0"
    Me.txtPart5SubTotal = "0.00": Me.txtPart6Number = ""
    Me.txtPart6Name = "": Me.txtPart6UnitPrice = "0.00"
    Me.txtPart6Quantity = "0": Me.txtPart6SubTotal = "0.00"
    Me.txtPartsTotal = "0.00": Me.txtTaxRate = "5.75"
    Me.txtTaxAmount = "0.00": Me.txtInvoiceTotal = "0.00"
End Sub

In the Object combo box, select Form

Implement the event as follows:

Private Sub Form_Load()
    cmdResetInvoice_Click
End Sub

In the Object combo box, select txtPart1Quantity

In the Procedure combo box, select LostFocus and implement the event as follows:

Private Sub CalculateInvoice()
    Dim Part1UnitPrice As Double, Part1Quantity As Integer
    Dim Part1SubTotal As Double, Part2UnitPrice As Double
    Dim Part2Quantity As Integer, Part2SubTotal As Double
    Dim Part3UnitPrice As Double, Part3Quantity As Integer
    Dim Part3SubTotal As Double, Part4UnitPrice As Double
    Dim Part4Quantity As Integer, Part4SubTotal As Double
    Dim Part5UnitPrice As Double, Part5Quantity As Integer
    Dim Part5SubTotal As Double, Part6UnitPrice As Double
    Dim Part6Quantity As Integer, Part6SubTotal As Double
    Dim PartsTotal As Double, TaxRate As Double
    Dim TaxAmount As Double, InvoiceTotal As Double
    
    Part1UnitPrice = CDbl(Nz(txtPart1UnitPrice))
    Part1Quantity = CInt(Nz(txtPart1Quantity))
    Part1SubTotal = Part1UnitPrice * Part1Quantity
    
    Part2UnitPrice = CDbl(Nz(txtPart2UnitPrice))
    Part2Quantity = CInt(Nz(txtPart2Quantity))
    Part2SubTotal = Part2UnitPrice * Part2Quantity
    
    Part3UnitPrice = CDbl(Nz(txtPart3UnitPrice))
    Part3Quantity = CInt(Nz(txtPart3Quantity))
    Part3SubTotal = Part3UnitPrice * Part3Quantity
    
    Part4UnitPrice = CDbl(Nz(txtPart4UnitPrice))
    Part4Quantity = CInt(Nz(txtPart4Quantity))
    Part4SubTotal = Part4UnitPrice * Part4Quantity
    
    Part5UnitPrice = CDbl(Nz(txtPart5UnitPrice))
    Part5Quantity = CInt(Nz(txtPart5Quantity))
    Part5SubTotal = Part5UnitPrice * Part5Quantity
    
    Part6UnitPrice = CDbl(Nz(txtPart6UnitPrice))
    Part6Quantity = CInt(Nz(txtPart6Quantity))
    Part6SubTotal = Part6UnitPrice * Part6Quantity
    
    TaxRate = CDbl(Nz(txtTaxRate))
    
    PartsTotal = Part1SubTotal + Part2SubTotal + Part3SubTotal + _
                 Part4SubTotal + Part5SubTotal + Part6SubTotal
    TaxAmount = PartsTotal * TaxRate / 100
    InvoiceTotal = PartsTotal + TaxAmount
    
    txtPart1SubTotal = FormatNumber(Part1SubTotal)
    txtPart2SubTotal = FormatNumber(Part2SubTotal)
    txtPart3SubTotal = FormatNumber(Part3SubTotal)
    txtPart4SubTotal = FormatNumber(Part4SubTotal)
    txtPart5SubTotal = FormatNumber(Part5SubTotal)
    txtPart6SubTotal = FormatNumber(Part6SubTotal)
    
    txtPartsTotal = FormatNumber(PartsTotal)
    txtTaxAmount = FormatNumber(TaxAmount)
    txtInvoiceTotal = FormatNumber(InvoiceTotal)
End Sub

Private Sub txtPart1Quantity_LostFocus()
    CalculateInvoice
End Sub

In the Object combo box, select txtPart2Quantity
In the Procedure combo box, select LostFocus and implement the event as follows:
Private Sub txtPart2Quantity_LostFocus() CalculateInvoice End Sub
In the Object combo box, select txtPart3Quantity
In the Procedure combo box, select LostFocus and implement the event as follows:
Private Sub txtPart3Quantity_LostFocus() CalculateInvoice End Sub
In the Object combo box, select txtPart4Quantity
In the Procedure combo box, select LostFocus and implement the event as follows:
Private Sub txtPart4Quantity_LostFocus() CalculateInvoice End Sub
In the Object combo box, select txtPart5Quantity
In the Procedure combo box, select LostFocus and implement the event as follows:
Private Sub txtPart4Quantity_LostFocus() CalculateInvoice End Sub
In the Object combo box, select txtPart6Quantity

In the Procedure combo box, select LostFocus and implement the event as follows:

Private Sub txtPart5Quantity_BeforeUpdate(Cancel As Integer)
    CalculateInvoice
End Sub

In the Object combo box, select cmdClose

Implement the event as follows:

Private Sub txtPart6Quantity_BeforeUpdate(Cancel As Integer)
    CalculateInvoice
End Sub

In the Object combo box, select txtPart2Quantity

Data Selection in a Recordset

So far, when we needed a record set, we passed the name of a table or query to it. This meant that we were getting all fields from the table or query. Here is an example:

Private Sub cmdSelectSomeFields_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    Dim fldEmployee As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    . . . Use the record set here
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

In some case, you may not be interested in all fields from a table or table. In this case, you can create a record set that uses only selected fields from a table or query.

To create a record set that involves only some desired fields, create a SELECT statement and pass it to a Recordset object. Here is an example:

Private Sub cmdSelectSomeFields_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    Dim fldEmployee As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset( _
            "SELECT FirstName, LastName FROM Employees")
    
    . . . Use the record set here
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

You can use any of the techniques we saw in the previous lesson and those we will study in next sections for your record set. After creating the record set, you can use it as we have done so far. Keep in mind that only the fields that are part of the SELECT statement will be available to you.

Practical Learning: Looking for a Record in a Domain

In the Object combo box, select txtPart1Number
In the Properties window, click Event and double On Lost Focus
Click its ellipsis button

Implement the event as follows:

Private Sub txtPart1Number_LostFocus()
On Error GoTo txtPart1Number_Error

    Dim i As Integer
    Dim dbAutoParts As Database
    Dim rstAutoParts As Recordset
    
    If txtPart1Number = "" Then
        Exit Sub
    End If
    
    Set dbAutoParts = CurrentDb
    Set rstAutoParts = dbAutoParts.OpenRecordset( _
        "SELECT AutoParts.PartName , " & _
        "       AutoParts.UnitPrice " & _
        "FROM AutoParts " & _
        "WHERE AutoParts.PartNumber = '" & txtPart1Number & "';")
    
    With rstAutoParts
        Do While Not .EOF
            For i = 0 To rstAutoParts.Fields.Count - 1
                If rstAutoParts(i).Name = "PartName" Then
                    Me.txtPart1Name = rstAutoParts(i).Value
                End If
                
                If rstAutoParts(i).Name = "UnitPrice" Then
                    txtPart1UnitPrice = rstAutoParts(i).Value
                End If
                
                txtPart1Quantity = "1"
                txtPart1SubTotal = txtPart1UnitPrice

		CalculateInvoice
            Next
            
            .MoveNext
        Loop
    End With

txtPart1Number_Exit:
    Exit Sub
    
txtPart1Number_Error:
    MsgBox "The system encountered a problem when " & _
           "trying to locate the part." & vbCrLf & _
           "Error #:" & vbTab & Err.Number & vbCrLf & _
           "Error Message: " & Err.Description
    Resume txtPart1Number_Exit
End Sub

In the Object combo box, select txtPart2Number
In the Procedure combo box, select LostFocus
Implement the event using the same code as above but replacing txtPart1Number with txtPart2Number, txtPart1Name with txtPart2Name, txtItem1UnitPrice with txtItem2UnitPrice, and txtPart1SubTotal and txtPart2SubTotal
In the Object combo box, select txtPart3Number
In the Procedure combo box, select LostFocus
Implement the event using the same code as above but replacing txtPart1Number with txtPart3Number, txtPart1Name with txtPart3Name, txtItem1UnitPrice with txtItem3UnitPrice, and txtPart1SubTotal and txtPart3SubTotal
In the Object combo box, select txtPart4Number
In the Procedure combo box, select LostFocus
Implement the event using the same code as above but replacing txtPart1Number with txtPart4Number, txtPart1Name with txtPart4Name, txtItem1UnitPrice with txtItem4UnitPrice, and txtPart1SubTotal and txtPart4SubTotal
In the Object combo box, select txtPart5Number
In the Procedure combo box, select LostFocus
Implement the event using the same code as above but replacing txtPart1Number with txtPart5Number, txtPart1Name with txtPart5Name, txtItem1UnitPrice with txtItem5UnitPrice, and txtPart1SubTotal and txtPart5SubTotal
In the Object combo box, select txtPart6Number
In the Procedure combo box, select LostFocus
Implement the event using the same code as above but replacing txtPart1Number with txtPart6Number, txtPart1Name with txtPart6Name, txtItem1UnitPrice with txtItem6UnitPrice, and txtPart1SubTotal and txtPart6SubTotal
In the Object combo box, select cmdClose

Implement the event as follows:

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

    DoCmd.Close

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
End Sub

Close Microsoft Visual Basic and return to Microsoft Access
Click the first text box under Part #
Type 118448 and press Tab
Type 4 and press Tab
Type 283615 and press Tab twice
Close the form
When asked whether you want to save, click Yes