Introduction to the Microsoft Access Object Library

Introduction to the Microsoft Access Object Library

Introduction to Libraries

A library is a set of classes (and objects) and techniques used to solve a particular problem. When it comes to a database system, a library is a group of classes and techniques used to create and manage a database in a particular way. Because there are many requirements and various options for computer databases nowadays, there are various libraries to create databases.

As we have seen so far, Microsoft Access provides its own mechanism for creating and managing databases. It provides the tools you need to start and complete a database project.

Practical Learning: Introducing Database Programming

1. Start Microsoft Access
2. Click Blank Desktop Database
3. Set the file name as Payroll System1
4. Click Create
5. On the Ribbon, click File and click Options
6. In the left list of the Access Options dialog box, click Current Database
7. In the main list, click Overlapping Windows
8. Click OK on the dialog box
9. Click OK on the message box
10. On the Ribbon, click File and click Close
11. In the list of files, click Payroll System1
12. On the Ribbon, click Create and click Form Design
13. Double-click the Properties button and, in the Property Sheet, change the following characteristics:
    Caption: Fun Department Store - Application Preparation
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
    Min Max Buttons: Min Enabled
14. Save the form as Application Preparation
15. From the Controls section of the Ribbon, click the Label and click the form
16. Type Tables
17. From the Controls section of the Ribbon, click the Button and click the form
18. Complete the design of the form as follows (we applied the Bodoni MT Black font with size 20 to all labels and buttons):

    

    Control		Name	Caption
    Label			Tables
    Line
    Button		cmdMaritalsStatusTable	Maritals Status
    Button		cmdTimeSheetsTable	Time Sheets
    Button		cmdFilingsStatusTable	Filings Status
    Button		cmdPayrollsTable	Payrolls
    Button		cmdEmployeesTable	Employees
    Button		cmdClose	Close

19. Save the form

The MAOL

Microsoft Access is equipped with a library, the Microsoft Access Object Library. Microsoft Access Object Library (MAOL) is a library used by Microsoft Access to create and manage databases. The library is automatically available when you start a new database or if you open an existing database. This library is available so you don't have to "load" it to programmatically create and manage databases.

To check the availability of the Microsoft Access Object Library, on the main menu of Microsoft Visual Basic, you can click Tools and click References... In the Available References list of the References dialog box, you can see that the Microsoft Access 16.0 Object Library is checked:

The Microsoft Access Object Library relies on classes to do its work. The most fundamental class in this library is called Object. Therefore, when using variables that would use this library, you can declare a variable of this class and then initialize it.

One of the most fundamental objects in the Microsoft Access Object Library is named DBEngine. Everything that exists in your database comes directly or indirectly from this object. Because this object is static (it does not change) and is automatically available when you start a new database (it is one of the default objects of the application), you don't need to declare a variable for it. If you want to use it, simply type its name and it is available.

Sessions

When you start a database in Microsoft Access, you are said to have started a session. If one database is not enough for what you are currently doing, you can open another existing database or you can create a new one. In the same way, you can open as many databases as you want. The group of databases opened by a user is referred to as a session:

Workspaces

In Microsoft Access, a session is programmatically considered, or called, a workspace and it is represented by the Workspace object. When you start a new database, you get a default workspace. If necessary, you can create one or more additional workspaces. The group of workspaces you are using is stored in a collection named Workspaces. To identify the workspaces, the DBEngine class is equipped with an indexed property named Workspaces. Therefore, to identify a workspace inside a DBEngine object, access the Workspaces() property and pass either the index or the name of the workspace. The first workspace in the DBEngine object can be identified with DBEngine.Workspace(0). The second workspace can be identified as DBEngine.Workspace(1) and so on.

Microsoft Access Object Library and Databases

Creating a Database

In the Microsoft Access Object Library, a database is an object of type Database. Therefore, if you want to start an object associated with a database, you can declare a variable of type Object or of type Database.

Remember, if you declare a variable using the Object class, when using the variable in the Code Editor, the Intellisense will not assist you.

To assist you with creating a database, the Workspace object is equipped with a method named CreateDatabase. Its syntax is:

Workspace.CreateDatabase(ByVal Name As String, _
			 ByVal Optional locale As String, _
			 ByVal Optional options As Variant) As Database

The first argument can be the name of the database you want to create. If you provide only the name of the database, it would be created in the same folder where the action was initiated. For example, if you are already working in a database and you create a new database by providing a name, the new database would be created in the same folder where the current database resides. Here is an example:

Private Sub cmdDatabase_Click()
    DBEngine.Workspaces(0).CreateDatabase("Exercise.accdb", . . .)
End Sub

If you want, you can store the new database in a drive and folder of your choice. To do this, provide the complete path and the name of the database as the first argument. This would be done as follows:

Private Sub cmdCreate_Click()
    DBEngine.Workspaces(0).CreateDatabase("C:\Microsoft Access Database Development\Exercise.accdb", . . .)
End Sub

The second argument of this method is required and it specifies the language used for the database. This is also referred to as the collation. This argument is passed as a constant named value and can be one of the following:

 Here is an example:

Private Sub cmdCreate_Click()
    CreateDatabase("Exercise.accdb", dbLangGreek)
End Sub

The third argument of the CreateDatabase() method is to pass some options to use when creating the database. This optional argument can be a constant specified as dbEncrypt, if you want the database to be encrypted. If you don't pass this constant, the database would not be encrypted.

If you want to specify the version of Microsoft Jet used in the new database, the options argument can be one of the following values:  dbVersion10, dbVersion11, dbVersion20, or dbVersion30. If you don't specify one of these values, the dbVersion30 would be used.

If you want the database to be encrypted and you want to specify the version of Microsoft Jet used in the new database, combine the dbEncrypt constant with one of the version values.

Getting a Reference to a Created Database

When the CreateDatabase() method has finished, it returns a reference to the database that was created. In fact, you must always obtain that reference. The database is recognized as the Database object of the workspace. To get it, first declare a variable of type Object. Here is an example:

Private Sub cmdDatabase_Click()
    Dim dbExercise As Object
    
End Sub

To get a reference to the new database, use the Set operator and assign the returned value of the method to your Database or Object variable. Here is an example that creates a new database named Exercise.accdb in the current folder:

Private Sub cmdDatabase_Click()
    Dim dbExercise As Object
    
    Set dbExercise = DBEngine.Workspaces(0).CreateDatabase("Exercise.accdb", dbLangGeneral)
End Sub

We mentioned that, when you start a database, the DBEngine object is automatically made available to you and it is equipped with a default workspace whose index is 0. Because these are already available, you don't have to include them in your statements if you want to use only the defaults. Therefore, you can omit them when creating a database. The above code could be written as:

Private Sub cmdDatabase_Click()
    Dim dbExercise As Database
    
    Set dbExercise = CreateDatabase("Exercise.accdb", dbLangGeneral)
End Sub

Opening a Database

Once a database exists, you can open it. To support the ability to open a database, the Workspace class is equipped with a method named OpenDatabase. Its syntax is:

Public Function Workspace.OpenDatabase(ByVal Name As String, _
				       ByVal Optional Options As Boolean, _
				       ByVal Optional ReadOnly As Boolean, _
				       ByVal Optional Connect As String) As Database

Only the first argument is required and it is passed as a string. You can pass the name of the database file with its extension. Here is an example:

Private Sub cmdOpenDatabase_Click()
    DBEngine.Workspaces(0).OpenDatabase "Example.accdb"
End Sub

After opening the database, if you want to work on it, you should get a reference to it when opening it. To allow you to do this, the OpenDatabase() method returns an Object object. Here is an example:

Private Sub cmdOpenDatabase_Click()
    Dim dbExample As Database
    
    Rem Open the Example.accdb database and get a reference to it
    Set dbExample = DBEngine.Workspaces(0).OpenDatabase("Example.accdb")
    
    . . . Use the dbExample database as you see fit

End Sub

When opening the database, you can lock it so that other people or applications cannot access it at the same time with you. To prevent other items (they are called processes) from accessing the database, pass a second argument as True. On the other hand, you may want to allow other processes to access the same database. To specify this option, pass the second argument as False.

Like the second argument, the third is optional. If you are opening the database and want to do something on it, such as modifying it, pass the third argument as False. If you don't want to perform any modification on the database, pass the third argument as True.

The fourth argument allows you to provide connection information.

Closing a Database

While a database is being used, it consumes computer resources such as memory. After using it, you should close the database to free the resources it was using and make them available to other applications.

To provide the ability to close a database, the Database class is equipped with a method named Close. Its syntax is:

Database.Close()

As you can see, this method does not take any argument. Before calling it, make sure you get a reference to the database to be closed. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim dbExercise As Object
    
    Set dbExercise = CreateDatabase("Exercises1.accdb", dbLangGeneral)
    dbExercise.Close
End Sub

When calling this method, if you try to close a database that is currently closed, you would receive a 2467 error.

The Tables of a MAOL Database

Introduction

In the Microsoft Access Object Library, a table is an object of type Object.

Creating a Table

To programmatically create a table using the Microsoft Access Object Library, the Database class is equipped with a method named CreateTableDef. Therefore, to create a table, first declare a variable for the database and then initialize it with the CreateTableDef() method of the current database object. This method can take as argument the name of the new table. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
End Sub

After creating the table, you must add it to the current database. To support this, the CurrentDb object is equipped with a property named TableDefs. TableDefs is in fact a collection. The TableDefs collection is equipped a method named Append that is used to add a new table to the current database. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    . . .
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

A Reference to a Table

In most cases, before performing an operation on a table, you will need to indicate what table you are referring to. This is usually easy to the user who can visually see the table. As for you the database developer, you can first programmatically get a reference to the table you intend to work on.

If a table exists already, to get a reference to it using the Microsoft Access Object library, first declare a variable of type Object. Then, assign the TableDefs property of the current database to the variable. TableDefs is a collection of the tables of the current database. To specify what table you are referring to, you can pass its name, as a string to the TableDefs indexed property. Here is an example:

Private Sub cmdGetReference_Click()
    Dim curDatabase As Object
    Dim tblStudents 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")
    
End Sub

Instead of using its name, you can also pass the numeric index of the table to the TableDefs property.

Deleting a Table

To delete a table using the Microsoft Access Object Library, pass the name of the undesired table to the Detele() method of the TableDefs property of the database. Here is an example from the Microsoft Access Object Library:

Private Sub cmdDeleteTable_Click()
    Dim curDatabase As Object

    Set curDatabase = CurrentDb

    curDatabase.TableDefs.Delete "Books"
End Sub

The Queries of a MAOL Dadabase

Introduction

In the Microsoft Access Object Library, a query is of type Object. The queries of a database are stored in a collection named QueryDefs. A query can be access by its index or its name. This can be done by adding parentheses to a database variable and passing either the index or the name of the query.

Creating a Query

To let you programmatically create a query using the Microsoft Access Object Library, the Database class is equipped with a method named CreateQueryDef. The syntax of this method is:

Public Function CreateQueryDef(ByVal Optional NewQueryName AS String,
			       ByVal Optional SQLStatement As String) As Object

The first argument is the name you will give to the new query. The second argument is a SQL statement that specifies the contents of the query. Here is an example:

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

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    strStatement = "SQL Statement"
    
    ' Create a new query named CustomersRecords
    Set qryCustomers = curDatabase.CreateQueryDef("CustomersRecords", strStatement)
    
    Set qryCustomers = Nothing
End Sub

Introduction to the Columns of a Table

A Column as a Field

A column of a database is of type Object. To give you access to the collection of fields of a table, in the Microsoft Access Object Library, the TableDef object is equipped with a property named Fields.

Creating a Field

We saw how to start a programmatic creation of a table in the Microsoft Access Object Library as follows. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    . . .
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

To support the creation of a column using the Microsoft Access Object Library, its table is equipped with a method named CreateField. This method takes three arguments and its syntax is:

Public Function CreateField(ByVal Optional ColumnName As String, _
			    ByVal Optional DataType As FieldType, _
			    ByVal Optional Size As Integer) As Object

Before calling this method, you can declare a variable of type Object for the new column. When calling this method, get a reference to it and assign it to the variable you would have created for the column. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField(ColumnName, DataType, Size)
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

None of the arguments to the CreateField() method is required. The first argument of the CreateField() method, name, specifies the name of the new column. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("colFullName", DataType, Size)
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

If you call the method without specifying the name of the column, at one time or another before actually creating the field, you will have to set its name. Otherwise you will receive an error. To specify the name of the column, access its Name property and assign the desired string. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students1")
    
    Set fldFullName = tblStudents.CreateField
    fldFullName.Name = "FullName"
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

The second argument to the CreateField() method specifies the data type that would be applied on the column. If you omit this optional argument, you must specify a data type before actually creating the field. To specify the data type of a column, assign the desired type to its Type property.

The third argument holds a piece of information that has to do with either the computer memory or the number of characters.

After creating a column, you must add it to the table. To support this, the table object is equipped with a property named Fields, which is a collection. We saw that the collections classes are equipped with a method named Add. The Fields collection of the table is equipped with an equivalent method but named Append. This method takes as argument an object that represents the collection. Here is an example

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("colFullName", DataType, Size)
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

If you are not planning to use the field many times, you don't have to first declare a variable for it. You can call the CreateField() method directly in the Append() method. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

    Set curDatabase = CurrentDb
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    tblStudents.Fields.Append tblStudents.CreateField("colFullName", DataType, Size)
    
    curDatabase.TableDefs.Append tblStudents
End Sub

Deleting a Column

If you have a column you don't need anymore on a table, you can remove that column. To programmatically delete a column, if you are using the Microsoft Access Object Library, call the Delete() method of the TableDef object and pass it the name of the column. The syntax of this method is:

TableDef.Fields.Delete ColumnName

In this formula, replace ColumnName with the name of the column you want to delete. Here is an example:

Private Sub cmdModifyPersons_Click()
    Dim curDatabase As Object
    Dim tblPersons As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblPersons = curDatabase.TableDefs("Persons")
    
    tblPersons.Fields.Delete "DateHired"
End Sub

Before deleting a column, make sure it exists, otherwise, you would receive a 3265 error:

Even if the column exists, before deleting it, make sure its table is closed. Otherwise, you would receive a 3211 error. You can check these issues using error handling.

The Data Type of a Column

Text-Based Fields

We saw how to start the creation of a field in the Microsoft Access Object Library as follows:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("colFullName", DataType, Size)
    
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

The second argument of the CreateField() method must be the data type applied on the column. To create a text-based field, the second argument can be dbText. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("colFullName", dbText)
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

You can also specify the data type as DB_TEXT. The dbText or DB_TEXT type is equivalent to the Short Text data type used in Microsoft Access.

To specify the maximum number of characters a text-based column can hold, provide a third argument to the CreateField() method and enter the desired number. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("FullName", dbText, 120)
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

Integral Fields

If you are creating a field that will hold small natural numbers, set its data type as either DB_BYTE or dbByte. If you are creating a field that will use general natural numbers, you can set its data type as DB_INTEGER or dbInteger. If you are creating a field that can contain large numbers, set its data type as DB_LONG or dbLong.

Boolean Fields

If you are programmatically creating a column for Boolean values, set its data type as dbBoolean. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colIsMarried As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append colFullName
    Set colIsMarried = tblEmployees.CreateField("IsMarried", dbBoolean)
    tblEmployees.Fields.Append colIsMarried
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblEmployees
End Sub

You can also use the DB_BOOLEAN type.

Single-Precision Numeric Fields

If you are creating a field for decimal values without regards to precision, specify its data type as dbSingle or DB_SINGLE.

Double-Precision Numeric Value Fields

To create a column that can use decimal numbers with double-precision, specify its data type as dbDouble or DB_DOUBLE. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colWeeklyHours As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
    tblEmployees.Fields.Append colWeeklyHours
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

Binary Fields in Microsoft Access Object Library

If you are creating a field that will accept binary values (such as hexadecimal numbers), specify its data type as DB_BINARY. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblBooks As Object
    Dim colShelf As Object

    Set curDatabase = CurrentDb
    Set tblBooks = curDatabase.CreateTableDef("Books")
    
    Set colShelf = tblBooks.CreateField("Shelf", DB_BINARY)
    tblBooks.Fields.Append colShelf
    
    curDatabase.TableDefs.Append tblBooks
End Sub

Currency Fields

If you are programmatically creating a column to hold monetary values, set its data type as dbCurrency or DB_CURRENCY. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim colFullName As Object
    Dim tblEmployees As Object
    Dim colWeeklyHours As Object
    Dim colHourlySalary As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
    tblEmployees.Fields.Append colWeeklyHours
    Set colHourlySalary = tblEmployees.CreateField("HourlySalary", DB_CURRENCY)
    tblEmployees.Fields.Append colHourlySalary
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

Date/Time Fields

To programmatically create a column that would hold date/time values, set its data type to DB_DATE. Here is an example:

Private Sub cmdTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDateHired As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT, 80)
    tblEmployees.Fields.Append colFullName
    Set colDateHired = tblEmployees.CreateField("DateHired", DB_DATE)
    tblEmployees.Fields.Append colDateHired
    
    curDatabase.TableDefs.Append tblEmployees
    
    MsgBox "A table named Employees has been created."
End Sub

You can also use the dbDate data type.

Practical Learning: Introducing Table Creation

1. On the form, right-click the Maritals Status button and click Build Event...
2. In the Choose Builder dialog box, click Code Builder and click OK
3. Change the document as follows:

   Private Sub cmdMaritalsStatusTable_Click()
       Dim fldMaritalStatus As Object
       Dim dbDepartmentStore As Object
       Dim tblMaritalsStatus As Object
       Dim fldMaritalStatusID As Object
   
       ' Get a reference to the current database
       Set dbDepartmentStore = CurrentDb
       ' Create a new table named MaritalsStatus
       Set tblMaritalsStatus = dbDepartmentStore.CreateTableDef("MaritalsStatus")
       
       ' Create a field for the Marital Status ID column
       Set fldMaritalStatusID = tblMaritalsStatus.CreateField("MaritalStatusID", DB_INTEGER)
       ' Add the new column to the table
       tblMaritalsStatus.Fields.Append fldMaritalStatusID
       
       ' Create a field for the Marital Status column
       Set fldMaritalStatus = tblMaritalsStatus.CreateField("MaritalStatus", DB_TEXT, 25)
       ' Add the new column to the table
       tblMaritalsStatus.Fields.Append fldMaritalStatus
   
       dbDepartmentStore.TableDefs.Append tblMaritalsStatus
       
       cmdMaritalsStatusTable.Enabled = False
       Application.RefreshDatabaseWindow
   End Sub

4. In the Object combo box, select cmdFilingsStatusTable
5. Implement the event as follows:

   Private Sub cmdFilingsStatusTable_Click()
       Dim fldFilingStatus As Object
       Dim tblFilingsStatus As Object
       Dim dbDepartmentStore As Object
   
       Set dbDepartmentStore = CurrentDb
       Set tblFilingsStatus = dbDepartmentStore.CreateTableDef("FilingsStatus")
       
       Set fldFilingStatus = tblFilingsStatus.CreateField("FilingStatusID", dbInteger)
       fldFilingStatus.Required = True
       tblFilingsStatus.Fields.Append fldFilingStatus
       
       Set fldFilingStatus = tblFilingsStatus.CreateField("FilingStatus", dbText, 25)
       fldFilingStatus.Required = True
       tblFilingsStatus.Fields.Append fldFilingStatus
       
       Set fldFilingStatus = tblFilingsStatus.CreateField("Description", dbMemo)
       tblFilingsStatus.Fields.Append fldFilingStatus
   
       dbDepartmentStore.TableDefs.Append tblFilingsStatus
       
       cmdFilingsStatusTable.Enabled = False
       Application.RefreshDatabaseWindow
   End Sub

Other Topics on Fields

Automatically Incrementing the Value of a Field

To support the ability to automatically increment the integral values of a field, the Attributes property of the Object class provides a constant named dbAutoIncrField. To apply this attribute, first create the field. Then, access its Attributes property and assign dbAutoIncrField to it. Here is an example:

Private Sub cmdTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colEmployeeID As Object
    Dim colFullName As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colEmployeeID = tblEmployees.CreateField("EmployeeID", dbLong)
    colEmployeeID.Attributes = dbAutoIncrField
    tblEmployees.Fields.Append colEmployeeID
    Set colFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append colFullName
    
    curDatabase.TableDefs.Append tblEmployees
    MsgBox "A table named Employees has been created"
End Sub

Learning: Automatically Incrementing the Value of a Field

1. In the Object combo box, select cmdEmployeesTable
2. To implement the event, start the code of the table as follows:

   Private Sub cmdEmployeesTable_Click()
       Dim fldEmployee As Object
       Dim tblEmployees As Object
       Dim dbDepartmentStore As Object
   
       Set dbDepartmentStore = CurrentDb
       Set tblEmployees = dbDepartmentStore.CreateTableDef("Employees")
       
       Set fldEmployee = tblEmployees.CreateField("EmployeeID", dbLong)
       fldEmployee.Attributes = dbAutoIncrField
       tblEmployees.Fields.Append fldEmployee
   End Sub

Setting a Default Value for a Field

To provide a default value to a field, access its DefaultValue property and assign the desired value to it. The value must be a string for a text-based field. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object
    Dim fldGender As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("FullName", dbText, 120)
    tblStudents.Fields.Append fldFullName
    
    Set fldGender = tblStudents.CreateField("Gender", dbText, 20)
    fldGender.DefaultValue = "Female"
    tblStudents.Fields.Append fldGender
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

After specifying a default value for a column, during data entry, you or the user can skip that field. If you skip the field, the field would receive the default value as its value.

Practical Learning: Setting a Default Value for a Field

1. In the Object combo box, select cmdTimeSheetsTable
2. Implement the event as follows:

   Private Sub cmdTimeSheetsTable_Click()
       Dim fldTimeSheet As Object
       Dim tblTimeSheets As Object
       Dim dbDepartmentStore As Object
   
       Set dbDepartmentStore = CurrentDb
       Set tblTimeSheets = dbDepartmentStore.CreateTableDef("TimeSheets")
   
       tblTimeSheets.Fields.Append tblTimeSheets.CreateField("TimeSheetNumber", dbLong)
       tblTimeSheets.Fields.Append tblTimeSheets.CreateField("EmployeeNumber", dbText, 10)
       
       Set fldTimeSheet = tblTimeSheets.CreateField("StartDate", dbDate)
       fldTimeSheet.DefaultValue = Date
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week1Monday", dbSingle)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week1Tuesday", dbSingle)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week1Wednesday", dbSingle)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week1Thursday", dbSingle)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week1Friday", dbSingle)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week1Saturday", dbSingle)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week1Sunday", dbSingle)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week2Monday", DB_SINGLE)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week2Tuesday", DB_SINGLE)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week2Wednesday", DB_SINGLE)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week2Thursday", DB_SINGLE)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week2Friday", DB_SINGLE)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week2Saturday", DB_SINGLE)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
       Set fldTimeSheet = tblTimeSheets.CreateField("Week2Sunday", DB_SINGLE)
       fldTimeSheet.DefaultValue = 0#
       tblTimeSheets.Fields.Append fldTimeSheet
   
       dbDepartmentStore.TableDefs.Append tblTimeSheets
       cmdTimeSheetsTable.Enabled = False
       Application.RefreshDatabaseWindow
   End Sub

The Nullity of a Field

To support the nullity of a value, the field uses a Boolean property named Required. By default, the value of this property is False, which means the user can skip the field during data entry. If you set this property to True, the user must enter a value for the field. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFirstName As Object, fldLastName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFirstName = tblStudents.CreateField("FirstName", dbText)
    tblStudents.Fields.Append fldFirstName
    
    Set fldLastName = tblStudents.CreateField("LastName", dbText)
    fldLastName.Required = True
    tblStudents.Fields.Append fldLastName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

During data entry, if a value is not provided for the field, the compiler would produce a 3314 error:

Practical Learning: Setting the Nullity for a Field

1. In the Object combo box, select cmdEmployeesTable
2. Change the implementation of the event as follows:

   Private Sub cmdEmployeesTable_Click()
       Dim fldEmployee As Object
       Dim tblEmployees As Object
       Dim dbDepartmentStore As Object
   
       Set dbDepartmentStore = CurrentDb
       Set tblEmployees = dbDepartmentStore.CreateTableDef("Employees")
       
       Set fldEmployee = tblEmployees.CreateField("EmployeeID", dbLong)
       fldEmployee.Attributes = dbAutoIncrField
       tblEmployees.Fields.Append fldEmployee
       
       Set fldEmployee = tblEmployees.CreateField("EmployeeNumber", dbText, 10)
       tblEmployees.Fields.Append fldEmployee
       
       Set fldEmployee = tblEmployees.CreateField("FirstName", DB_TEXT, 25)
       tblEmployees.Fields.Append fldEmployee
       Set fldEmployee = tblEmployees.CreateField("LastName", DB_TEXT, 25)
       fldEmployee.Required = True
       tblEmployees.Fields.Append fldEmployee
       Set fldEmployee = tblEmployees.CreateField("Address", dbText, 100)
       tblEmployees.Fields.Append fldEmployee
       Set fldEmployee = tblEmployees.CreateField("City", dbText, 40)
       tblEmployees.Fields.Append fldEmployee
       Set fldEmployee = tblEmployees.CreateField("County", dbText, 40)
       tblEmployees.Fields.Append fldEmployee
       Set fldEmployee = tblEmployees.CreateField("State", dbText, 5)
       fldEmployee.Required = True
       tblEmployees.Fields.Append fldEmployee
       Set fldEmployee = tblEmployees.CreateField("ZIPCode", DB_TEXT, 20)
       tblEmployees.Fields.Append fldEmployee
       Set fldEmployee = tblEmployees.CreateField("MaritalStatusID", dbInteger)
       tblEmployees.Fields.Append fldEmployee
       Set fldEmployee = tblEmployees.CreateField("Exemptions", DB_INTEGER)
       tblEmployees.Fields.Append fldEmployee
       Set fldEmployee = tblEmployees.CreateField("HourlySalary", dbDouble)
       tblEmployees.Fields.Append fldEmployee
       Set fldEmployee = tblEmployees.CreateField("FilingStatusID", dbInteger)
       tblEmployees.Fields.Append fldEmployee
   
       cmdEmployeesTable.Enabled = False
       dbDepartmentStore.TableDefs.Append tblEmployees
   End Sub

3. In the Object combo box, select cmdPayrollsTable and implement the event as follows:

   Private Sub cmdPayrollsTable_Click()
       Dim fldPayroll As Object
       Dim tblPayrolls As Object
       Dim dbDepartmentStore As Object
   
       Set dbDepartmentStore = CurrentDb
       Set tblPayrolls = dbDepartmentStore.CreateTableDef("PayrollSystem")
       
       Set fldPayroll = tblPayrolls.CreateField("PayrollNumber", dbLong)
       fldPayroll.Attributes = dbAutoIncrField
       tblPayrolls.Fields.Append fldPayroll
   
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeNumber", dbText, 10)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeFirstName", DB_TEXT, 25)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeLastName", DB_TEXT, 25)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeAddress", dbText, 100)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeCity", dbText, 40)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeCounty", dbText, 40)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeState", dbText, 5)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeZIPCode", dbText, 20)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeMaritalStatus", DB_TEXT, 30)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeExemptions", dbInteger)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeHourlySalary", dbDouble)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("EmployeeFilingStatus", dbInteger)
       
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetNumber", dbLong)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetStartDate", dbText, 25)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek1Monday", DB_SINGLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek1Tuesday", DB_SINGLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek1Wednesday", DB_SINGLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek1Thursday", DB_SINGLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek1Friday", DB_SINGLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek1Saturday", DB_SINGLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek1Sunday", DB_SINGLE)
   
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek2Monday", dbSingle)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek2Tuesday", dbSingle)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek2Wednesday", dbSingle)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek2Thursday", dbSingle)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek2Friday", dbSingle)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek2Saturday", dbSingle)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TimeSheetWeek2Sunday", dbSingle)
   
       tblPayrolls.Fields.Append tblPayrolls.CreateField("RegularTime", dbDouble)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("Overtime", dbDouble)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("RegularPay", dbDouble)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("OvertimePay", dbDouble)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("GrossSalary", dbDouble)
   
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TaxableGrossWagesCurrent", DB_DOUBLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("AllowancesCurrent", DB_DOUBLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("FederalIncomeTaxCurrent", DB_DOUBLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("SocialSecurityTaxCurrent", DB_DOUBLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("MedicareTaxCurrent", DB_DOUBLE)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("StateIncomeTaxCurrent", DB_DOUBLE)
   
       tblPayrolls.Fields.Append tblPayrolls.CreateField("TaxableGrossWagesYTD", dbDouble)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("AllowancesYTD", dbDouble)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("FederalIncomeTaxYTD", dbDouble)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("SocialSecurityTaxYTD", dbDouble)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("MedicareTaxYTD", dbDouble)
       tblPayrolls.Fields.Append tblPayrolls.CreateField("StateIncomeTaxYTD", dbDouble)
               
       dbDepartmentStore.TableDefs.Append tblPayrolls
       cmdPayrollsTable.Enabled = False
       Application.RefreshDatabaseWindow
   End Sub
   

1. Return to Microsoft Access and switch the form to Form View
2. Click each of the buttons in the Tables section
3. Save and close the Payroll System form
4. On the Ribbon, click Create and click Form Design
5. In the Property Sheet, change the following characteristics:
   Caption: Fun Department Store - New Time Sheet
   Auto Center: Yes
   Record Selectors: No
   Navigation Buttons: No
   Min Max Buttons: Min Enabled
6. Right-click the form and click Form Header/Footer
7. Save the form as New Time Sheet
8. Complete the design of the form as follows:
   In the below list of controls, when the Text Box is indicated, the Caption is for the accompanying label and the Name is for the text box. When the caption is not indicated, you must delete the accompanying label
   All text boxes have the Border Color as Black (or Text Black)
   We applied the Bodoni MT font with size 11 to all labels and text boxes
   The top yellow title uses the Bodoni MT Black font with size 20
   The top white title uses the Elephant font with size 18

   

   Control		Caption	Name	Other Properties
   Label		Fun Department Store		Font Color: White
   Line				Border Color: #FFC20E
   Label		New Time Sheet		Font Color: Yellow
   Text Box		Employee #:	txtEmployeeNumber
   Text Box			txtEmployeeName
   Text Box		Time Sheet #:	txtTimeSheetNumber
   Text Box		Start Date:	txtStartDate	Format: Short Date
   Text Box		End Date:	txtEndDate
   Line				Border Width: 2 pt
   Label		Monday
   Label		Tuesday
   Label		Wednesday
   Label		Thursday
   Label		Friday
   Label		Saturday
   Label		Sunday
   Text Box		Week 1:	txtWeek1Monday
   Text Box			txtWeek1Tuesday
   Text Box			txtWeek1Wednesday
   Text Box			txtWeek1Thursday
   Text Box			txtWeek1Friday
   Text Box			txtWeek1Saturday
   Text Box			txtWeek1Sunday
   Text Box		Week 2:	txtWeek2Monday
   Text Box			txtWeek2Tuesday
   Text Box			txtWeek2Wednesday
   Text Box			txtWeek2Thursday
   Text Box			txtWeek2Friday
   Text Box			txtWeek2Saturday
   Text Box			txtWeek2Sunday
   Button		Submit	btnSubmit
   Button		Close	cmdClose

9. Save the form
10. Double-click the button at the intersection of the rulers on the form
11. In the Property Sheet, click the Event tab and double-click On Load
12. Click the ellipsis button of the On Load event and change the document as follows:

    Option Compare Database
    Option Explicit
    
    Private timeSheetFound As Boolean
    
    Private Sub Form_Load()
       timeSheetFound = False
    End Sub

13. Return to Microsoft Access and close the form
14. When asked whether you want to save, click Yes
15. On the Ribbon, click Create and click Form Design
16. In the Property Sheet, change the following characteristics:
    Caption: Fun Department Store - Payroll Preparation
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
    Dividing Lines: Yes
17. Right-click the form and click Form Header/Footer
18. Save the form as Payroll System
19. Complete the design of the form as follows:
    In the below list of controls, when the Text Box is indicated, the Caption is for the accompanying label and the Name is for the text box. When the caption is not indicated, you must delete the accompanying label
    All text boxes have the Border Color as Black (or Text Black)
    We applied the Bodoni MT font with size 11 to all labels and text boxes
    The top yellow title uses the Bodoni MT Black font with size 20
    The top white title uses the Elephant font with size 18

    

    Control		Caption	Name	Other Properties
    Label		Fun Department Store		Font Color: White
    Line				Border Color: #FFC20E
    Label		Payroll Preparation		Font Color: Yellow
    Text Box		Time Sheet Start Date:	txtStartDate	Format: Short Date
    Text Box		End Date:	txtEndDate
    Text Box		Employee #:	txtEmployeeNumber
    Text Box			txtFirstName
    Text Box			txtLastName
    Text Box		Time Sheet #:	txtTimeSheetNumber
    Text Box		Address:	txtAddress
    Text Box		City:	txtCity
    Text Box		County:	txtCounty
    Text Box		State:	txtState
    Text Box		ZIP Code:	txtZIPCode
    Text Box		Marital Status:	txtMaritalStatus
    Text Box		Exemptions:	txtExemptions
    Text Box		Hourly Salary:	txtHourlySalary
    Text Box		Filing Status:	txtFilingStatus
    Button		Process Payroll	cmdProcessPayroll
    Line				Border Width: 3pt
    Label		Monday
    Label		Tuesday
    Label		Wednesday
    Label		Thursday
    Label		Friday
    Label		Saturday
    Label		Sunday
    Label		Total
    Text Box		Week 1:	txtWeek1Monday
    Text Box			txtWeek1Tuesday
    Text Box			txtWeek1Wednesday
    Text Box			txtWeek1Thursday
    Text Box			txtWeek1Friday
    Text Box			txtWeek1Saturday
    Text Box			txtWeek1Sunday
    Text Box			txtWeek1TotalTimeWorked
    Line				Border Style: Dashes
    Text Box		Regular Time:	txtWk1MonRegularTime
    Text Box			txtWk1TueRegularTime
    Text Box			txtWk1WedRegularTime
    Text Box			txtWk1ThuRegularTime
    Text Box			txtWk1FriRegularTime
    Text Box			txtWk1SatRegularTime
    Text Box			txtWk1SunRegularTime
    Text Box			txtWeek1TotalRegularTime
    Text Box		Overtime:	txtWk1MonOvertime
    Text Box			txtWk1TueOvertime
    Text Box			txtWk1WedOvertime
    Text Box			txtWk1ThuOvertime
    Text Box			txtWk1FriOvertime
    Text Box			txtWk1SatOvertime
    Text Box			txtWk1SunOvertime
    Text Box			txtWeek1TotalOvertime
    Text Box		Regular Pay:	txtWk1MonRegularPay
    Text Box			txtWk1TueRegularPay
    Text Box			txtWk1WedRegularPay
    Text Box			txtWk1ThuRegularPay
    Text Box			txtWk1FriRegularPay
    Text Box			txtWk1SatRegularPay
    Text Box			txtWk1SunRegularPay
    Text Box			txtWeek1TotalRegularPay
    Text Box		Overtime Pay:	txtWk1MonOvertimePay
    Text Box			txtWk1TueOvertimePay
    Text Box			txtWk1WedOvertimePay
    Text Box			txtWk1ThuOvertimePay
    Text Box			txtWk1FriOvertimePay
    Text Box			txtWk1SatOvertimePay
    Text Box			txtWk1SunOvertimePay
    Text Box			txtWeek1TotalOvertimePay
    Line				Border Width: 3pt
    Text Box		Week 2:	txtWeek2Monday
    Text Box			txtWeek2Tuesday
    Text Box			txtWeek2Wednesday
    Text Box			txtWeek2Thursday
    Text Box			txtWeek2Friday
    Text Box			txtWeek2Saturday
    Text Box			txtWeek2Sunday
    Line				Border Style: Dashes
    Text Box		Regular Time:	txtWk2MonRegularTime
    Text Box			txtWk2TueRegularTime
    Text Box			txtWk2WedRegularTime
    Text Box			txtWk2ThuRegularTime
    Text Box			txtWk2FriRegularTime
    Text Box			txtWk2SatRegularTime
    Text Box			txtWk2SunRegularTime
    Text Box			txtWeek2TotalRegularTime
    Text Box		Overtime:	txtWk2MonOvertime
    Text Box			txtWk2TueOvertime
    Text Box			txtWk2WedOvertime
    Text Box			txtWk2ThuOvertime
    Text Box			txtWk2FriOvertime
    Text Box			txtWk2SatOvertime
    Text Box			txtWk2SunOvertime
    Text Box			txtWeek2TotalOvertime
    Text Box		Regular Pay:	txtWk2MonRegularPay
    Text Box			txtWk2TueRegularPay
    Text Box			txtWk2WedRegularPay
    Text Box			txtWk2ThuRegularPay
    Text Box			txtWk2FriRegularPay
    Text Box			txtWk2SatRegularPay
    Text Box			txtWk2SunRegularPay
    Text Box			txtWeek2TotalRegularPay
    Text Box		Overtime Pay:	txtWk2MonOvertimePay
    Text Box			txtWk2TueOvertimePay
    Text Box			txtWk2WedOvertimePay
    Text Box			txtWk2ThuOvertimePay
    Text Box			txtWk2FriOvertimePay
    Text Box			txtWk2SatOvertimePay
    Text Box			txtWk2SunOvertimePay
    Text Box			txtWeek2TotalOvertimePay
    Button		Submit	btnSubmit
    Button		Close	cmdClose

20. Save the form
21. Double-click the button at the intersection of the rulers on the form
22. In the Property Sheet, click the Event tab and double-click On Load
23. Click the ellipsis button of the event and change the document as follows:

    Option Compare Database
    Option Explicit
    
    Private iPayrollNumber As Long
    
    Private Sub Form_Load()
        iPayrollNumber = 0
    End Sub

24. In the Object combo box, select txtStartDate
25. In the Procedure combo box, select LostFocus
26. Implement the event as follows:

    Private Sub txtStartDate_LostFocus()
        If IsNull(txtStartDate) Or IsEmpty(txtStartDate) Then
            Exit Sub
        Else
            txtEndDate = DateAdd("d", 13, CDate(txtStartDate))
        End If
    End Sub

27. Add the following procedures to the document:

    Private Sub ResetForm()
        txtPayrollNumber = ""
        txtEmployeeNumber = ""
        txtFirstName = ""
        txtLastName = ""
        txtAddress = ""
        txtCity = ""
        txtCounty = ""
        txtState = ""
        txtZIPCode = ""
        txtMaritalStatus = ""
        txtExemptions = ""
        txtHourlySalary = ""
        txtFilingStatus = ""
        
        txtWeek1Monday = "0.00"
        txtWeek1Tuesday = "0.00"
        txtWeek1Wednesday = "0.00"
        txtWeek1Thursday = "0.00"
        txtWeek1Friday = "0.00"
        txtWeek1Saturday = "0.00"
        txtWeek1Sunday = "0.00"
        txtWeek2Monday = "0.00"
        txtWeek2Tuesday = "0.00"
        txtWeek2Wednesday = "0.00"
        txtWeek2Thursday = "0.00"
        txtWeek2Friday = "0.00"
        txtWeek2Saturday = "0.00"
        txtWeek2Sunday = "0.00"
        
        txtWk1MonRegularTime = "0.00"
        txtWk1MonOvertime = "0.00"
        txtWk1MonRegularPay = "0.00"
        txtWk1MonOvertimePay = "0.00"
        txtWk1TueRegularTime = "0.00"
        txtWk1TueOvertime = "0.00"
        txtWk1TueRegularPay = "0.00"
        txtWk1TueOvertimePay = "0.00"
        txtWk1WedRegularTime = "0.00"
        txtWk1WedOvertime = "0.00"
        txtWk1WedRegularPay = "0.00"
        txtWk1WedOvertimePay = "0.00"
        txtWk1ThuRegularTime = "0.00"
        txtWk1ThuOvertime = "0.00"
        txtWk1ThuRegularPay = "0.00"
        txtWk1ThuOvertimePay = "0.00"
        txtWk1FriRegularTime = "0.00"
        txtWk1FriOvertime = "0.00"
        txtWk1FriRegularPay = "0.00"
        txtWk1FriOvertimePay = "0.00"
        txtWk1SatRegularTime = "0.00"
        txtWk1SatOvertime = "0.00"
        txtWk1SatRegularPay = "0.00"
        txtWk1SatOvertimePay = "0.00"
        txtWk1SunRegularTime = "0.00"
        txtWk1SunOvertime = "0.00"
        txtWk1SunRegularPay = "0.00"
        txtWk1SunOvertimePay = "0.00"
        
        txtWk2MonRegularTime = "0.00"
        txtWk2MonOvertime = "0.00"
        txtWk2MonRegularPay = "0.00"
        txtWk2MonOvertimePay = "0.00"
        txtWk2TueRegularTime = "0.00"
        txtWk2TueOvertime = "0.00"
        txtWk2TueRegularPay = "0.00"
        txtWk2TueOvertimePay = "0.00"
        txtWk2WedRegularTime = "0.00"
        txtWk2WedOvertime = "0.00"
        txtWk2WedRegularPay = "0.00"
        txtWk2WedOvertimePay = "0.00"
        txtWk2ThuRegularTime = "0.00"
        txtWk2ThuOvertime = "0.00"
        txtWk2ThuRegularPay = "0.00"
        txtWk2ThuOvertimePay = "0.00"
        txtWk2FriRegularTime = "0.00"
        txtWk2FriOvertime = "0.00"
        txtWk2FriRegularPay = "0.00"
        txtWk2FriOvertimePay = "0.00"
        txtWk2SatRegularTime = "0.00"
        txtWk2SatOvertime = "0.00"
        txtWk2SatRegularPay = "0.00"
        txtWk2SatOvertimePay = "0.00"
        txtWk2SunRegularTime = "0.00"
        txtWk2SunOvertime = "0.00"
        txtWk2SunRegularPay = "0.00"
        txtWk2SunOvertimePay = "0.00"
        
        txtWeek1TotalTimeWorked = "0.00"
        txtWeek1TotalRegularTime = "0.00"
        txtWeek1TotalOvertime = "0.00"
        txtWeek1TotalRegularPay = "0.00"
        txtWeek1TotalOvertimePay = "0.00"
        txtWeek2TotalTimeWorked = "0.00"
        txtWeek2TotalRegularTime = "0.00"
        txtWeek2TotalOvertime = "0.00"
        txtWeek2TotalRegularPay = "0.00"
        txtWeek2TotalOvertimePay = "0.00"
    
        txtRegularTime = "0.00"
        txtOvertime = "0.00"
        txtRegularPay = "0.00"
        txtOvertimePay = "0.00"
        txtGrossSalary = "0.00"
        txtTaxableGrossWagesCurrent = "0.00"
        txtAllowancesCurrent = "0.00"
        txtFederalIncomeTaxCurrent = "0.00"
        txtSocialSecurityTaxCurrent = "0.00"
        txtMedicareTaxCurrent = "0.00"
        txtStateIncomeTaxCurrent = "0.00"
        txtTaxableGrossWagesYTD = "0.00"
        txtAllowancesYTD = "0.00"
        txtFederalIncomeTaxYTD = "0.00"
        txtSocialSecurityYTD = "0.00"
        txtMedicareTaxYTD = "0.00"
        txtStateIncomeTaxYTD = "0.00"
    End Sub
    
    Private Sub CalculateWeek1Monday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Monday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1MonRegularTime = FormatNumber(RegularTime)
        txtWk1MonOvertime = FormatNumber(Overtime)
        txtWk1MonRegularPay = FormatNumber(RegularPay)
        txtWk1MonOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Tuesday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Tuesday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1TueRegularTime = FormatNumber(RegularTime)
        txtWk1TueOvertime = FormatNumber(Overtime)
        txtWk1TueRegularPay = FormatNumber(RegularPay)
        txtWk1TueOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Wednesday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Wednesday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1WedRegularTime = FormatNumber(RegularTime)
        txtWk1WedOvertime = FormatNumber(Overtime)
        txtWk1WedRegularPay = FormatNumber(RegularPay)
        txtWk1WedOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Thursday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Thursday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1ThuRegularTime = FormatNumber(RegularTime)
        txtWk1ThuOvertime = FormatNumber(Overtime)
        txtWk1ThuRegularPay = FormatNumber(RegularPay)
        txtWk1ThuOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Friday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Friday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1FriRegularTime = FormatNumber(RegularTime)
        txtWk1FriOvertime = FormatNumber(Overtime)
        txtWk1FriRegularPay = FormatNumber(RegularPay)
        txtWk1FriOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Saturday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Saturday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1SatRegularTime = FormatNumber(RegularTime)
        txtWk1SatOvertime = FormatNumber(Overtime)
        txtWk1SatRegularPay = FormatNumber(RegularPay)
        txtWk1SatOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Sunday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Sunday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1SunRegularTime = FormatNumber(RegularTime)
        txtWk1SunOvertime = FormatNumber(Overtime)
        txtWk1SunRegularPay = FormatNumber(RegularPay)
        txtWk1SunOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Monday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Monday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2MonRegularTime = FormatNumber(RegularTime)
        txtWk2MonOvertime = FormatNumber(Overtime)
        txtWk2MonRegularPay = FormatNumber(RegularPay)
        txtWk2MonOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Tuesday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Tuesday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2TueRegularTime = FormatNumber(RegularTime)
        txtWk2TueOvertime = FormatNumber(Overtime)
        txtWk2TueRegularPay = FormatNumber(RegularPay)
        txtWk2TueOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Wednesday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Wednesday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2WedRegularTime = FormatNumber(RegularTime)
        txtWk2WedOvertime = FormatNumber(Overtime)
        txtWk2WedRegularPay = FormatNumber(RegularPay)
        txtWk2WedOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Thursday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Thursday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2ThuRegularTime = FormatNumber(RegularTime)
        txtWk2ThuOvertime = FormatNumber(Overtime)
        txtWk2ThuRegularPay = FormatNumber(RegularPay)
        txtWk2ThuOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Friday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Friday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2FriRegularTime = FormatNumber(RegularTime)
        txtWk2FriOvertime = FormatNumber(Overtime)
        txtWk2FriRegularPay = FormatNumber(RegularPay)
        txtWk2FriOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Saturday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Saturday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2SatRegularTime = FormatNumber(RegularTime)
        txtWk2SatOvertime = FormatNumber(Overtime)
        txtWk2SatRegularPay = FormatNumber(RegularPay)
        txtWk2SatOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Sunday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Sunday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2SunRegularTime = FormatNumber(RegularTime)
        txtWk2SunOvertime = FormatNumber(Overtime)
        txtWk2SunRegularPay = FormatNumber(RegularPay)
        txtWk2SunOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateCurrentStateTaxes()
        Dim withheldTaxes As Double
        Dim taxableIncome As Double
        Dim filingStatus As Integer
    
        withheldTaxes = 0#
        taxableIncome = CDbl(txtGrossSalary)
        filingStatus = Left(txtFilingStatus, 1)
        
        If txtState = "" Then
            Exit Sub
        End If
        
        ' These evaluations are based on the 2015 tax schedule
        ' I used the information (taxes summaries/schedules) from http://www.bankrate.com/finance/taxes/check-taxes-in-your-state.aspx
        Select Case txtState
            Case "DE"
                If taxableIncome <= 2000# Then
                    withheldTaxes = 0#
                ElseIf taxableIncome <= 5000# Then
                    withheldTaxes = (taxableIncome - 2001#) * 0.022
                ElseIf taxableIncome <= 10000# Then
                    withheldTaxes = ((5000# - 2001#) * 0.022) + ((taxableIncome - 5001#) * 0.039)
                ElseIf taxableIncome <= 20000# Then
                    withheldTaxes = ((5000# - 2001#) * 0.022) + ((10000# - 5001#) * 0.039) + ((taxableIncome - 10001#) * 0.048)
                ElseIf taxableIncome <= 25000# Then
                    withheldTaxes = ((5000# - 2001#) * 0.022) + ((10000# - 5001#) * 0.039) + ((20000# - 10001#) * 0.048) + ((taxableIncome - 20001#) * 0.052)
                ElseIf taxableIncome <= 60000# Then
                    withheldTaxes = ((5000# - 2001#) * 0.022) + ((10000# - 5001#) * 0.039) + ((20000# - 10001#) * 0.048) + ((25000# - 20001#) * 0.052) + ((taxableIncome - 25001#) * 0.0555)
                Else ' if( taxableIncome > 600000.00 )
                    withheldTaxes = ((5000# - 2001#) * 0.022) + ((10000# - 5001#) * 0.039) + ((20000# - 10001#) * 0.048) + ((25000# - 20001#) * 0.052) + ((60000# - 25001#) * 0.0555) + ((taxableIncome - 60000#) * 0.066)
                End If
    
            Case "MD"
                If filingStatus = 1 Then
                    If taxableIncome <= 1000# Then
                        withheldTaxes = taxableIncome * 0.02
                    ElseIf taxableIncome <= 2000# Then
                        withheldTaxes = 20# + ((taxableIncome - 1000#) * 0.03)
                        ' withheldTaxes = (1000.00 * 0.02) + ((taxableIncome - 1000.00) * 0.03)
                    ElseIf taxableIncome <= 3000# Then
                        withheldTaxes = 20# + 29.97 + ((taxableIncome - 2000) * 0.04)
                        ' withheldTaxes = (1000 * 0.02) + (999 * 0.03) + ((taxableIncome - 2000.00) * 0.04)
                    ElseIf taxableIncome <= 150000# Then
                        withheldTaxes = 20# + 29.97 + 39.96 + ((taxableIncome - 3000#) * 0.0475)
                        ' withheldTaxes = (1000.00 * 0.02) + (999.00 * 0.03) + (999.00 * 0.04) + ((taxableIncome - 3000.00) * 0.0475)
                    ElseIf taxableIncome <= 175000# Then
                        withheldTaxes = 20# + 29.97 + 39.96 + 6982.4525 + ((taxableIncome - 150000#) * 0.05)
                        ' withheldTaxes = (1000.00 * 0.02) + (999.00 * 0.03) + (999.00 * 0.04) + ((150000.00 - 3001) * 0.0475) + ((taxableIncome - 150000.00) * 0.05)
                    ElseIf taxableIncome <= 225000 Then
                        withheldTaxes = 20# + 29.97 + 39.96 + 6982.4525 + 1249.95 + ((taxableIncome - 175000#) * 0.0525)
                        ' withheldTaxes = (1000.00 * 0.02) + (999.00 * 0.03) + (999.00 * 0.04) + ((150000.00 - 3001.00) * 0.0475) + ((175000.00 - 150001.00) * 0.05) + ((taxableIncome - 175000.00) * 0.0525)
                    ElseIf taxableIncome <= 300000# Then
                        withheldTaxes = 20# + 29.97 + 39.96 + 6982.4525 + 1249.95 + 2624.9475 + ((taxableIncome - 150000#) * 0.055)
                        ' withheldTaxes = (1000.00 * 0.02) + (999.00 * 0.03) + (999.00 * 0.04) + ((150000.00 - 3001.00) * 0.0475) + ((175000.00 - 150001.00) * 0.05) + ((225000.00 - 175001.00) * 0.0525) + ((taxableIncome - 150000.00) * 0.055)
                    Else ' if( taxableIncome > 300000.00 )
                        withheldTaxes = 20# + 29.97 + 39.96 + 6982.4525 + 1249.95 + 2624.9475 + 4124.945 + ((taxableIncome - 300000#) * 0.0575)
                        ' withheldTaxes = (1000.00 * 0.02) + (999.00 * 0.03) + (999.00 * 0.04) + ((150000.00 - 3001) * 0.0475) + ((175000.00 - 150001) * 0.05) + ((225000.00 - 175001) * 0.0525) + ((300000.00 - 225001) * 0.055) + ((taxableIncome - 300000.00) * 0.0575)
                    End If
                Else
                    If taxableIncome <= 1000# Then
                        withheldTaxes = taxableIncome * 0.02
                    ElseIf taxableIncome <= 2000# Then
                        withheldTaxes = 20# + ((taxableIncome - 1000#) * 0.03)
                    ElseIf taxableIncome <= 3000# Then
                        withheldTaxes = 20# + 29.97 + ((taxableIncome - 2000#) * 0.04)
                    ElseIf taxableIncome <= 100000# Then
                        withheldTaxes = (1000# * 0.02) + (999# * 0.03) + (999# * 0.04) + ((taxableIncome - 3000#) * 0.0475)
                    ElseIf taxableIncome <= 125000 Then
                        withheldTaxes = (1000# * 0.02) + (999# * 0.03) + (999# * 0.04) + ((100000# - 3001#) * 0.0475) + ((taxableIncome - 100000#) * 0.05)
                    ElseIf taxableIncome <= 150000# Then
                        withheldTaxes = (1000# * 0.02) + (999# * 0.03) + (999# * 0.04) + ((100000# - 3001#) * 0.0475) + ((125000# - 100001#) * 0.05) + ((taxableIncome - 125000#) * 0.0525)
                    ElseIf taxableIncome <= 250000# Then
                        withheldTaxes = (1000# * 0.02) + (999# * 0.03) + (999# * 0.04) + ((100000# - 3001#) * 0.0475) + ((125000# - 100001#) * 0.05) + ((150000# - 125001#) * 0.0525) + ((taxableIncome - 150000) * 0.055)
                    Else ' if( taxableIncome > 250000 )
                        withheldTaxes = (1000# * 0.02) + (999# * 0.03) + (999# * 0.04) + ((100000# - 3001#) * 0.0475) + ((125000# - 100001#) * 0.05) + ((150000# - 125001#) * 0.0525) + ((250000# - 150001#) * 0.055) + ((taxableIncome - 250000#) * 0.0575)
                    End If
                End If
    
            Case "PA"
                ' Pennsylvania has a flat tax rate of 3.07% on individual income, with no personal exemptions.
                withheldTaxes = taxableIncome * 0.0307
    
            Case "VA"
                If taxableIncome <= 3000# Then
                    withheldTaxes = taxableIncome * 0.02
                ElseIf taxableIncome <= 5000# Then
                    withheldTaxes = (3000# * 0.02) + ((taxableIncome - 3001#) * 0.03)
                ElseIf taxableIncome <= 17000# Then
                    withheldTaxes = (3000# * 0.02) + ((17000# - 3001#) * 0.03) + ((taxableIncome - 5001#) * 0.05)
                Else ' if( taxableIncome > 17000 )
                    withheldTaxes = (3000# * 0.02) + ((17000# - 3001#) * 0.03) + ((17000# - 5001#) * 0.05) + ((taxableIncome - 17001#) * 0.0575)
                End If
                
            Case "WV"
                If taxableIncome <= 10000# Then
                    withheldTaxes = taxableIncome * 0.03
                ElseIf taxableIncome <= 25000 Then
                    withheldTaxes = (10000# * 0.03) + ((taxableIncome - 10001) * 0.04)
                ElseIf taxableIncome <= 40000 Then
                    withheldTaxes = (10000# * 0.03) + ((25000 - 10001) * 0.04) + ((taxableIncome - 25001) * 0.045)
                ElseIf taxableIncome <= 60000 Then
                    withheldTaxes = (10000# * 0.03) + ((25000 - 10001) * 0.04) + ((40000 - 25001) * 0.045) + ((taxableIncome - 40001) * 0.06)
                Else ' if( taxableIncome > 60000 )
                    withheldTaxes = (10000# * 0.03) + ((25000 - 10001) * 0.04) + ((40000 - 25001) * 0.045) + ((60000 - 40001) * 0.06) + ((taxableIncome - 60001) * 0.065)
                End If
                
            Case Else
                withheldTaxes = 0#
        End Select
    
        txtStateIncomeTaxCurrent = FormatNumber(withheldTaxes)
    End Sub

28. Close Microsoft Visual Basic and return to Microsoft Access
29. Save and close the form

Microsoft Office Access Database Engine Object

Introduction

Microsoft Office ships with a library that can be used to perform all types of operations on a database and used throughout all applications of the Microsoft Office family. This library is called Microsoft Office Access Database Engine Object (We will refer to it as MOADE). Like every library, it has a version. In Microsoft Office 2016, it is the Microsoft Office 16.0 Access Database Engine Object Library.

The Microsoft Office 16.0 Access Database Engine Library is loaded by default when you start a new database in Microsoft Access. To check it, on the main menu of Microsoft Visual Basic, click Tools and click References...:

Using the MOADE Library

Like the other database libraries, the Microsoft Office 16.0 Access Database Engine Object lbrary relies on many objects and collections. The most top object of the Microsoft Office 16.0 Access Database Engine Object library is called Access. One of the properties of the Access object is called Application. To reference the Microsoft Office 16.0 Access Database Engine Object library in your application, declare a variable of type Access.Application. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
End Sub

After declaring the variable, you must initialize it using the Set operator. To do this, call the CreateObject() function. Its syntax is:

CreateObject(class,[servername])

This method takes two arguments. The first argument is the name of a class. For Microsoft Access, the class is named Microsoft.Access. This class can be followed by the version number. For example, Microsoft Office Access 2016's version is 16. In this case, the class would be specified as Access.Application.16. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application.16")
End Sub

In reality, you can pass the argument as Access.Application without the version. If you do this, the database engine would find out about the latest installation of Microsoft Access in your computer using the drivers installed.

When calling the CreateObject() method, only the first argument is required. If you are trying to use a database that resides on a server, pass the name of that server as the second argument.

MOADE and Databases

Creating a Database

To support the creation of a database, the Access.Application object is equipped with a method named NewCurrentDatabase. Its syntax is:

Access.Application.NewCurrentDatabase(filepath, FileFormat, Template, SiteAddress, ListID)

Only the first argument is required. You can pass it as the name of the new database. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application")
    ROSH.NewCurrentDatabase "Red Oak High School"
End Sub

If you don't specify an extension, the database engine would find out the latest version of Microsoft Access that is installed in your computer. If it is Microsoft Access 2007 - 2016, a new database with extension .accdb would be created. Still, if you want, you can add an extension. If you have Microsoft Access 2016 but want to specify the version of database you want to create, pass the second argument. The second argument is a member of the AcNewDatabaseFormat enumeration. The available values are:

Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application")
    ROSH.NewCurrentDatabase "Red Oak High School", _
    			    AcNewDatabaseFormat.acNewDatabaseFormatUserDefault
End Sub

The other arguments are optional.

Opening a Database

Once a database has been created, in order to use it, you must open it. To open a database in the Microsoft Office 16.0 Access Database Engine library, you can call the OpenCurrentDatabase() method of the Access.Application class. Its syntax is:

Access.Application.OpenCurrentDatabase(filepath, Exclusive, bstrPassword)

The first argument can be the name of the database. If you provide (only) the name of the database, the engine would look for it in the same directory as the application that called it. Otherwise, you should provide the complete path to the database.

Closing a Database

After using a database, you should close it to free the resources it was using. To close a database using the Microsoft Office 16.0 Access Database Engine Library, call the CloseCurrentDatabase() method. Its syntax is:

Access.Application.CloseCurrentDatabase()

As you can see, this method takes no argument. After calling this method, set its variable to Nothing. Here is an example:

Private Sub cmdCloseDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application")
    ROSH.CloseCurrentDatabase
    
    Set ROSH = Nothing
End Sub

When calling this method, the database must have been previously opened. If the database was not opened or it was already closed, you would receive a 2467 error.

Practical Learning: Ending the Lesson

• Close Microsoft Access