Home

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 Properties 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 Label and click the form
  16. Type Tables
  17. From the Controls section of the Ribbon, click the Button 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):

    Func Department Store - Database Management

    Control Name Caption
    Label Label   Tables
    Line Line    
    Button Button cmdMaritalsStatusTable Maritals Status
    Button Button cmdTimeSheetsTable Time Sheets
    Button Button cmdFilingsStatusTable Filings Status
    Button Button cmdPayrollsTable Payrolls
    Button Button cmdEmployeesTable Employees
    Button 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:

References

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:

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.

Author Note 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:

Constant Language Group
dbLangGeneral English, German, French, Portuguese, Italian, and Modern Spanish
dbLangArabic Arabic
dbLangCyrillic Russian
dbLangCzech Czech
dbLangDutch Dutch
dbLangGreek Greek
dbLangHebrew Hebrew
dbLangHungarian Hungarian
dbLangIcelandic Icelandic
dbLangNordic Nordic languages (Microsoft Jet database engine version 1.0 only)
dbLangNorwdan Norwegian and Danish
dbLangPolish Polish
dbLangSpanish Traditional Spanish
dbLangSwedfin Swedish and Finnish
dbLangTurkish Turkish

 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:

Error 3265

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:

Error 3314

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

    Fun Department Store - New Time Sheet

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

    Fun Department Store - Payroll System

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

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:

AcNewDatabaseFormat Member Constant Value Description
acNewDatabaseFormatUserDefault 0 The database engine will use the current installation of Microsoft Access
acNewDatabaseFormatAccess2000 9 A Microsoft Access database compatible with versions 2.0, 95, 97, or 2000 will be created with the extension .mdb
acNewDatabaseFormatAccess2002 10 A Microsoft Access 2002-2003 compatible database will be created with the extension .mdb
acNewDatabaseFormatAccess2007 12 A Microsoft Office Access 20137 compatible database will be created with the extension .accdb

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.

Error

Practical Learning: Ending the Lesson

  • Close Microsoft Access
 
 
   
 

Previous Copyright © 2016, FunctionX, Inc. Next