Data Entry and Natural Numbers

If you want the column to hold any one of the integral types and you are working visually on the table, you must change its view. To do this, display the table in the Design View. First specify the name of the column under Field Name. In the Data Type, select Number. In the lower section of the window, click the arrow of the Field Size property and select from the list. The options you have are Byte, Integer, and Long Integer:

1. Start Microsoft Access 
2. Create a blank database named Car Inventory
3. Close the default table without saving it
4. On the Ribbon, click Create
5. In the Forms section, click Form Design
6. In the Controls section of the Ribbon, click the Button and click the Detail section of the form. If the Button Wizard starts, click Cancel
7. On the form, double-click the button to access its Properties window.
   Change its Name to cmdCreateTables
8. Change its Caption to Create Tables

All of the libraries we have used so far support all types of integral values. The Byte data type is the same as we reviewed with variables in Lesson 3. It is made for small numeric value not more than 255. If you are programmatically creating the table using the Microsoft Access Object Library and you want the column to hold these types of numbers, set its data type to dbByte or DB_BYTE. 

As mentioned in Lesson 3, if you want to use values higher than the Byte is made for, you can use the Integer type. To apply to a column you are creating the Design View of a table, after setting its Data Type to Number in the top section of the table, in the lower section select Integer as its Field Size. If you are programmatically the column using the Microsoft Access Object Library and you want this type of numeric value, set its data type to dbInteger or DB_INTEGER.

Besides the Byte and the integer, another natural number supported in the libraries is called Long or Long Integer. This is used for significantly high numbers. If you are creating a table in the Design View and you want to apply this data type, select it in the Field Size. If you are programmatically creating the column using the Microsoft Access Object Library, you can set its data type to dbLong or DB_LONG. 

Here is an example of a table that is being created with the natural number types:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colEmployeeNumber As Object
    Dim colFullName As Object
    Dim colExemptions As Object
    Dim colMaritalStatus As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colEmployeeNumber = tblEmployees.CreateField("[Employee Number]", bdLong)
    tblEmployees.Fields.Append colEmployeeNumber
    Set colFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append colFullName
    Set colMaritalStatus = tblEmployees.CreateField("MaritalStatus", dbByte)
    tblEmployees.Fields.Append colMaritalStatus
    Set colExemptions = tblEmployees.CreateField("Exemptions", dbInteger)
    tblEmployees.Fields.Append colExemptions
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

Like the Microsoft Access Object Library, the DAO supports creating integer-based fields on a table. When creating a field that would be used for small numbers, you can set its data type to either  DB_BYTE or dbByte. If you are creating a field that would general natural numbers, you can set its data type to DB_INTEGER or dbInteger. If you are creating a field that can contain very large numbers, set its data type to DB_LONG or dbLong.

Here is an example of a table that is being created with three fields that use integral types:

Private Sub cmdCreateTable_Click()
    Dim dbCurrent As DAO.Database
    Dim tblCustomers As DAO.TableDef
    Dim fldCustomerName As DAO.Field
    Dim fldCategory As DAO.Field
    Dim fldContractStatus As DAO.Field
    Dim fldContractLength As DAO.Field
    
    ' Specify the database to use
    Set dbCurrent = CurrentDb

    ' Create a new TableDef object.
    Set tblCustomers = dbCurrent.CreateTableDef("Customers")

    Set fldCustomerName = tblCustomers.CreateField("CustomerName", DB_TEXT)
    tblCustomers.Fields.Append fldCustomerName
    
    Set fldCategory = tblCustomers.CreateField("Category", DB_INTEGER)
    tblCustomers.Fields.Append fldCategory
    
    Set fldContractStatus = tblCustomers.CreateField("Contract Status", DB_BYTE)
    tblCustomers.Fields.Append fldContractStatus
    
    Set fldContractLength = tblCustomers.CreateField("Contract Length", DB_LONG)
    tblCustomers.Fields.Append fldContractLength

   ' Add the new table to the database.
   dbCurrent.TableDefs.Append tblCustomers
   dbCurrent.Close
End Sub

The SQL supports all types of natural numbers through various data types. If you want the field to hold small positive numbers that can range from 0 to 255, apply a data type named BYTE or INTEGER1 (remember that SQL is not case-sensitive). Here are examples

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE LibraryMembers(MemberName Text, " & _
                                             "MembershipStatus Byte, " & _
                                             "MembershipType Integer1);"
End Sub

To create a field that can handle normal natural numbers, apply the SHORT, SMALLINT, or the INTEGER2 data type. Here are examples:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE ClassAttendance(Course Text, " & _
                                             "Registered Short, " & _
                                             "RoomCapacity Integer1);"
End Sub

If you are creating a field to use very large natural numbers, specify its data type as INT, LONG, INTEGER, or INTEGER4. Here are examples:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE StateCensus(State Text, " & _
                                             "Men int, " & _
                                             "Women long, " & _
                                             "Children integer4);"
End Sub

When performing data entry on fields of integral types, assign a number in the natural range. If the field holds a byte-based number, assign a positive number between 0 and 255. If the field is an Integer-based type, assign a negative or positive number between –32,768 and 32,767 (in reality, in SQL, the SMALLINT data type uses numbers between –32,768 and 32,767 but the INTEGER type is made for numbers between –2,147,483,648 and 2,147,483,647). If the field is made for a long integer, you can assign any number, including a very small number or a very large number ranging from –2,147,483,648 and 2,147,483,647.

When we study relationships, we will see that, on a table, each record should be uniquely identified. This should be the case even if many records seem to have the same values for each column. We saw already that you can require that the user provide a value for each record of a certain column. In some cases, the user may not have the right value for a column but at the time, the record would need to be created, even if it is temporary. To solve this type of problem and many others, you can create a column that provides its own value. On the other hand, to create a special column that can be used to uniquely identify each record, you can ask the database engine to automatically provide a numeric value for the column.

If you are creating a column in the Design View of a table, to allow the database engine to provide a value for the column, you can specify its data type as AutoNumber. On a table, only one column can have the AutoNumber data type.

To support the ability to automatically increment the integral values of a field, the Attributes property of the Field class of the Microsoft Access Object Library, through the Object class, and of the DAO.Field 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

After creating the field, when performing data entry, remember that you will not provide a value for the auto-incrementing field. Only the other fields need values. Here is an example:

Private Sub cmdCreateRecord_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    rstEmployees.AddNew
    rstEmployees("FullName").Value = "Martial Engolo"
    rstEmployees.Update
    
    MsgBox "A new record has been added to the Employees table."
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

If you are programmatically creating the column using SQL, you can set its data type to either COUNTER or AUTOINCREMENT. Only one column of a table can have one of these data types. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "ContractorNo COUNTER, " & _
                 "FullName TEXT NOT NULL);"
End Sub

By default, when you apply the COUNTER or the AUTOINCREMENT data type, when the user creates the first record, the field under the auto-incrementing value receives a number of 1. If the user creates a second record, the auto-incrementing value receives a number of 1, and so on. If you want, you can make the first record receive a number other than 1. You can also make it increment to a value other than 1. To apply this feature, the COUNTER and the AUTOINCREMENT types use a seed in their parentheses: COUNTER(x,y) or AUTOINCREMENT(x,y). The x value represents the starting value of the records. The y value specifies how much would be added to a value to get the next. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "ContractorNo AUTOINCREMENT(5, 10), " & _
                 "FullName TEXT NOT NULL);"
End Sub

When performing data entry, a user's job should be as easy and smooth as possible. One of the aspects that take care of this is the flow of information from one list to another. In other words, data stored in one list can be made available to another list so the user not only doesn't have to enter the same piece of information in different lists but also the should be able to access, from one list or table, data stored in another table.

To allow the information to flow from one list to another, there should (must) exist a relationship between both tables. A relationship is made possible through a type of link from one table to another. This is the essence of relational databases.

The purpose of the primary key is to keep records distinct from one another. When performing data entry, you can let the user manage this and you can use some techniques to exercise a certain level of control. The data type of a primary key can be almost any type, certainly a string or a number. For example, you can use an employee number or else. The most important rule is that each record must have a unique value to distinguish it from another record. In most cases, if you let the user specify the value(s) of the primary key, this can be overwhelming. Instead of going through this configuration process, you can rely on Microsoft Access to automatically create a unique value for each record of a particular column.

A column that holds unique values that can differentiate one record from another is called a primary key. In most cases, you can create one column that would serve as the primary key of the table. To create a primary key, if working from the Design View of the table, you can right-click the column that will play this role and click Primary Key:

After doing this, a picture with a key would appear on the left box of the column name. In some cases, you can use more than one column, that is, a combination of columns, to serve as the primary key.

If you are using either the Microsoft Access Object Library or DAO, to specify that a column is used as the primary, when calling the CreateField() method of the table, pass a third argument as adKeyPrimary. Here is an example:

Private Sub cmdTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colEmployeeID As Object
    Dim colFirstName As Object
    Dim colLastName As Object

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

If you are programmatically creating the table using SQL, in Lesson 15, we saw that you could apply the COUNTER or the AUTOINCREMENT data type to a column if you want it to generate an auto-incrementing numeric value. To specify that the column is used as a primary key, add the PRIMARY KEY attribute to it. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Genders" & _
                 "(" & _
                 "GenderID COUNTER(1,1) PRIMARY KEY NOT NULL," & _
                 "Gender varchar(20)" & _
                 ");"
    MsgBox "A table named Genders has been created"
End Sub

In the SQL, you can give a specific name to a primary key. To do this, you can first create the column. Then, somewhere before the closing parenthesis of the table, specify the primary key column using the following formula:

CONSTRAINT PrimaryKeyName PRIMARY KEY(ColumnName)

In this formula, the CONSTRAINT keyword and the PRIMARY KEY (case-insensitive) expression are required. In the PrimaryKeyName placeholder, enter the name you want to give to the primary key. In the parentheses of the PRIMARY KEY expression, enter the name of the column that will be used as the primary key. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Persons" & _
                 "(" & _
                 "PersonID COUNTER(1,1) NOT NULL," & _
                 "FirstName varchar(20)," & _
                 "LastName varchar(20) NOT NULL," & _
                 "CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonID)" & _
                 ");"
    MsgBox "A table named Persons has been created"
End Sub

By convention or tradition, the name of the primary starts with PK_ followed by the name of the table. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Persons" & _
                 "(" & _
                 "PersonID COUNTER(1,1) NOT NULL," & _
                 "FirstName varchar(20)," & _
                 "LastName varchar(20) NOT NULL," & _
                 "CONSTRAINT PK_People PRIMARY KEY(PersonID)" & _
                 ");"
    MsgBox "A table named Persons has been created"
End Sub

1. Right-click the Create Tables button and click Build Event...
2. In the Choose builder dialog box, double-click Code Builder
3. Implement the event as follows:
    

   Private Sub cmdCreateTables_Click() DoCmd.RunSQL "CREATE TABLE Categories(" & _ "CategoryID AutoIncrement(1001, 1) " & _ " Primary Key Not Null, " & _ "Category VarChar(50)," & _ "DailyRate Double, WeeklyRate Double, " & _ "MonthlyRate Double, WeekendRate Double);" MsgBox "A table named Categories has been created." DoCmd.RunSQL "INSERT INTO Categories(Category, " & _ "DailyRate, WeeklyRate, MonthlyRate, WeekendRate) " & _ "VALUES('Economy', 34.95, 30.85, 28.95, 24.95);" DoCmd.RunSQL "INSERT INTO Categories(Category, " & _ "DailyRate, WeeklyRate, MonthlyRate, WeekendRate) " & _ "VALUES('Compact', 39.95, 35.75, 32.95, 29.95);" End Sub

4. Return to Microsoft Access
5. Switch the form to Form View and click the button
6. Click OK each time to create the table and create its records
7. Return to Microsoft Visual Basic

A foreign key is a column on a table whose data is coming from another table. Obviously in order to have information flowing from one table to another, the table that holds the primary information must be created. You can create it before or after creating the other table, as long as you haven't established any link between both tables, it doesn't matter what sequence you use to create them.

The table that contains a primary key and that holds the information that another table would use is called the primary table or the parent table. The table that will receive the information from the other table is called the foreign table or the child table.

You can also create a foreign key in the SQL. The basic formula to use is:

ColumnName DataType REFERENCES ParentTableName(ForeignKeyCcolumn) 

The REFERENCES keyword is required. In the ParentTableName placeholder, enter the name of the primary table that holds the information that will be accessed in the current table. In the parentheses of ParentTableName, enter the name of the primary column of the parent table. Here is an example:

CREATE TABLE Persons
(
    PersonID AUTOINCREMENT(1,1) NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    GenderID Integer REFERENCES Genders(GenderID),
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);

1. Change the previous code as code:
    

   Private Sub cmdCreateTables_Click() DoCmd.RunSQL "CREATE TABLE Cars(" & _ "CarID AutoIncrement(100001, 1) Primary Key Not Null, " & _ "TagNumber VarChar(50), " & _ "Make VarChar(50), " & _ "Model VarChar(50), " & _ "CarYear Integer, " & _ "CategoryID Integer References Categories(CategoryID), " & _ "HasCDPlayer YesNo, " & _ "HasDVDPlayer YesNo, " & _ "Available YesNo);" MsgBox "A table named Cars has been created." DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _ "CategoryID, HasCDPlayer, HasDVDPlayer, " & _ "Available) VALUES('CDJ-85F', 'Mercury', 'GrandMarquis', " & _ "2008, 1004, False, True, False)" DoCmd.RunSQL "INSERT INTO Cars(TagNumber, Make, Model, CarYear, " & _ "CategoryID, HasCDPlayer, HasDVDPlayer, " & _ "Available) VALUES('FGE-920', 'Ford', 'Escape', " & _ "2006, 1006, False, False, True)" End Sub

2. Return to Microsoft Access
3. Click the button to create the table
4. Click OK each time to create the table and add records to it
5. Close the form
6. When asked whether you want to save it, click No

A combo box is a Windows control made of two parts: a text portion and a list. A text box is used to display a selection made from a list of items. A list box displays a list of items, usually in one column, but a list box can also be configured to display more than one column.

There are various ways you can create a combo box or a list box in Microsoft Office Access 2007. The classic way is that, after displaying a form or report in Design View, in the Controls section of the Ribbon, click either the Combo Box button or the List Box button and click the form or report. When you do this, if the Control Wizards button is down, a wizard would start. If you want to create a list manually, you can click Cancel. Otherwise, you can continue with the wizard.

To programmatically create a combo box, call the CreateConotrol() function and pass the ControlType as acComboBox. The first argument is the name of the form or report on which the label will be positioned. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlGenders As Control
    
    Set ctlGenders = CreateControl("Exercise", _
                                   AcControlType.acComboBox)

    Set ctlGenders = Nothing
End Sub

To programmatically create a list box, call the CreateConotrol() function and pass the ControlType as acListBox. The first argument is the name of the form or report on which the label will be positioned. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlGenders As Control
    
    Set ctlGenders = CreateControl("Exercise", _
                                   AcControlType.acListBox)

    Set ctlGenders = Nothing
End Sub

The third argument is the section of the form or report where the control will be positioned. You can pass the fourth argument as the name of the form or report on which the label will be positioned. That is, the first and the fourth argument can be the same.

The primary reason for having a combo or a list box is to display a list of items. There are various ways you can specify this list.

When adding a combo or a list box to a form or report, if you use the wizard, it would assist you with creating and even configuring the list of items. If you don't use the wizard, you will need to create and configure the list yourself. Before creating the list of items of a combo or a list box, you must first specify how the list would be created. The property that allows you to specify the type of list is called RowSourceType. As it happens, the combo and the list boxes of Microsoft Access provide three ways to specify the origin of the list. Two options require a table (or a query, or a SQL statement).

To visually specify the list of items of a combo or a list box, each of their object (ComboBox or ListBox) is equipped with a property named RowSource.

If you want to create a list of strings to display in a combo box or list box, set the RowSourceType property to "Value List". This would be done as follows:

Private Sub Detail_Click()
    cbxGenders.RowSourceType = "Value List"
End Sub

After specifying this, to assist you with adding the items to the list of the control, the ComboBox and the ListBox classes are equipped with a collection property. This property mimics the behavior of the Collection class. For example, to add an item to the control, you can call its AddItem() method. Here are examples:

Private Sub Detail_Click()
    cbxGenders.RowSourceType = "Value List"
    
    cbxGenders.AddItem "Male"
    cbxGenders.AddItem "Female"
    cbxGenders.AddItem "Unknown"
End Sub

After creating the control, to locate an item in its list, you can use its indexed property.

Many of the combo boxes you will in your forms or reports get their values from another table through a pre-established relationship. Such combo boxes have their RowSourceType set to Table/Query. To make data entry convenient, you can allow the user to add a value from the form or report where the combo box resides. Unfortunately, after adding the new value, the combo box is not automatically updated. You or the user must manually update the combo box. The user can change the form's view to design and switch it back to Form View. This is inconvenient and most users do not that this is possible. Fortunately, the ComboBox class is equipped with a method to update itself. The method is called Requery.

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Clarksville Ice Cream1 database
3. In the Navigation Pane, right-click the IceCreamOrders form and click Design View
4. Right-click the top Add button and click Build Event...
5. In the Choose Builder dialog box, double-click Code Builder
6. Implement the event as follows:
    

   Private Sub cmdAddFlavor_Click() DoCmd.OpenForm "NewFlavor", , , , acFormAdd End Sub

7. In the Object combo box, select cmdAddIngredient and implement the event as follows:
    

   Private Sub cmdAddIngredient_Click() DoCmd.OpenForm "NewIngredient", , , , acFormAdd End Sub

8. In the Object combo box, select Form
9. In the Procedure combo box, select Activate and implement the event as follows:
     

   Private Sub Form_Activate() FlavorID.Requery IngredientID.Requery End Sub