Details on Data Entry

Details on Record Creation

The Nullity of a Field

When performing data entry, you can expect the user to skip any column whose value is not available and move to the next. In some cases, you may required that the value of a column be specified before the user can move on. If you are creating the table in the Design View, to require that the user enter a value for a particular column, in the lower section of the window, use the Required Boolean property.

By default, the value of the Required property is set to No, which means the user doesn't have to provide a value for the column in order to create the record. If you want to require the value, set this property to Yes.

If you are programmatically creating the column using SQL, if you want to let the user add or not add a value for the column, type the NULL keyword on the right side of the data type. If you want to require a value for the column, type NOT NULL. Here are examples: Private Sub cmdTable_Click() DoCmd.RunSQL "CREATE TABLE Contractors(" & _ "FullName TEXT NOT NULL, " & _ "AvailableOnWeekend LOGICAL NULL, " & _ "OwnsACar BIT, " & _ "CanShareOwnCar YESNO);" End Sub

In this case, when performing data entry, the user must always provide a value for the FullName column in order to create a record. If you omit to specify the nullity of a field, it is assumed NULL.

Practical Learning: Requiring Field Values

1. Start Microsoft Access and open the College Park Auto Shop1 database from the previous lesson
2. In the Database window, click the Tables button. Right-click RepairOrders and click Design View
3. Click CustomerName and in the lower section, set the Required property to Yes

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 apply an integer data type to it but 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. 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, " & _
                 "AvailableOnWeekend LOGICAL NULL, " & _
                 "OwnsACar BIT, " & _
                 "CanShareOwnCar YESNO);"
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 2, 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 as 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), " & _
                 "AvailableOnWeekend LOGICAL NULL, " & _
                 "OwnsACar BIT, " & _
                 "CanShareOwnCar YESNO);"
End Sub

When reviewing the data types available for fields, we saw that some of them could use a string-based data type, namely TEXT, CHAR, or VARCHAR. By default, in the Design View of a table and when creating a column, if you set its data type to Text, it can hold (only) 50 characters. If you programmatically create a table and you set a column's data type to TEXT, CHAR, or VARCHAR, it is made to hold 255 characters. Fortunately, you can control the maximum number of characters that would be allowed in a column during data entry.

If you are creating a table in the Design View, to control the number of characters that a user can enter under a column, after setting its Data Type to Text, in the lower section of the window, use the Field Size property to exercise this control. You can set a value from 1 to 255.

If you are programmatically creating a string-based column using either the Microsoft Access Object Library or DAO, to specify the maximum number of characters it can hold, provide a third argument to the CreateField() method and enter the desired number. Here is an example for a column created using the Microsoft Access Object library:

Private Sub cmdAddColumn_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim colFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblStudents = curDatabase.TableDefs("Students")
    
    Set colFullName = tblCustomers.CreateField("FullName", DB_TEXT, 60)
    tblCustomers.Fields.Append colFullName
End Sub

If you are creating the table using SQL, to specify the number of characters of the string-based column, add the parentheses to the TEXT, the CHAR, or the VARCHAR data types, and in the parentheses, enter the desired number. Here are examples:

Private Sub cmdTableCreator_Click()
    Dim conExercise As ADODB.Connection
    Dim strSQL As String
    
    Set conExercise = New ADODB.Connection
    conExercise.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source='C:\Programs\Exercise1.mdb'"
    
    strSQL = "CREATE TABLE Contractors (" & _
             "DateHired DateTime," & _
             "EmplNumber Text(6)," & _
             "Dept int," & _
             "FirstName VarChar(20)," & _
             "LastName varchar(20)," & _
             "Address varchar(50)," & _
             "City VARCHAR(40)," & _
             "State CHAR(2));"
    
    conExercise.Execute strSQL
    
    conExercise.Close
    Set conExercise = Nothing
End Sub

Practical Learning: Setting Field Sizes

Masking a Value

A mask is a technique of specifying how a value would display to the user. This depends on the type of value, the language settings in Control Panel, and probably the users' habits. The mask used by the values of a column is set in the Input Mask field in the lower section of the table or the Properties window of a form, in Design View.

Practical Learning: Masking the Fields

1. Reopen the GCS2 database
2. In the upper section of the view, click CustomerPhone
3. In the lower section of the view, click Input Mask to select its value and press Delete to remove the default mask
4. Reopen the College Park Auto Shop1 database 
5. Open the RepairOrders table in Design View and click RepairDate
6. In the lower section of the window, set the Input Mask property to 99\->L<LL\-00;0;_
7. In the top section of the window, click TimeReady
8. In the lower section of the window, set the Input Mask property to 09:00\ >LL;0;_
9. Save and close the table
10. In the Database window, click the Forms button
11. Right-click NewRepairOrder and click Design View
12. Change the properties of some controls as follows:
     

    Name	Format
    txtPart1UnitPrice	Fixed
    txtPart1Quandity	General Number
    txtPart1SubTotal	Fixed
    txtPart2Name
    txtPart2UnitPrice	Fixed
    txtPart2Quandity	General Number
    txtPart2SubTotal	Fixed
    txtPart3Name
    txtPart3UnitPrice	Fixed
    txtPart3Quandity	General Number
    txtPart3SubTotal	Fixed
    txtPart4Name
    txtPart4UnitPrice	Fixed
    txtPart4Quandity	General Number
    txtPart4SubTotal	Fixed
    txtPart5Name
    txtPart5UnitPrice	Fixed
    txtPart5Quandity	General Number
    txtPart5SubTotal	Fixed
    txtJobPerformed1
    txtJobPrice1	Fixed
    txtJobPerformed2
    txtJobPrice2	Fixed
    txtJobPerformed3
    txtJobPrice3	Fixed
    txtJobPerformed4
    txtJobPrice4	Fixed
    txtJobPerformed5
    txtJobPrice5	Fixed
    txtTotalParts	Currency
    txtTotalLabor	Currency
    txtTaxRate	Percent
    txtTaxAmount	Currency
    txtRepairTotal	Currency
    txtRepairDate	Medium Date Input Mask: 99\->L<LL\-00;0;_
    txtTimeReady	Medium Time Input Mask: 09:00\ >LL;0;_

13. Save and close the form

Default Values

Introduction

A default value allows a column to use a value that is supposed to be common to most cells of a particular column. The default value can be set as a constant value or it can use a function that would adapt to the time the value is needed.

Practical Learning: Setting Default Values

1. Reopen the GCS2 database and open the NewCleaningOrder form in Design View 
2. In the upper section of the view, click CustomerPhone
3. In the lower section of the view, click Default Value and type "(000) 000-0000"
4. In the same way, set the default values of the following columns:
    

   Field Name	Default Value
   CustomerPhone	"(000) 000-0000"
   UnitPriceShirts	1.25
   UnitPricePants	1.75
   Item1	"None"
   Item2	"None"
   Item3	"None"
   Item4	"None"
   TaxRate	0.0575

5. Save the table
6. In the Database window, click Forms and double-click CleaningOrders
7. Right-click its title bar and click Form Design
8. Using the Properties window, set the formats of the Windows controls as follows:
    

   Name	Format
   txtSubTotalShirts	Currency
   txtSubTotalPants	Currency
   txtSubTotalItem1	Currency
   txtSubTotalItem2	Currency
   txtSubTotalItem3	Currency
   txtSubTotalItem4	Currency
   txtCleaningTotal	Currency
   txtTaxRate	Percent
   txtTaxAmount	Currency
   txtOrderTotal	Currency

9. Save the form and switch it to Form View
10. Enter a few records and close the form

Default Values of Expressions

When it comes to setting default values, the table has various limitations. For example, if you create a table with three columns such as FirstName, LastName, and FullName, obviously you may want the FullName to combine the first name and the last name but still give the user the opportunity to change the result. If you write an expression to do this in the Default Value property of the FullName column, you would not get the intended result. To specify a default value in a scenario like this one, you should use the form or report that will show the values. Fortunately, when the values are changed in the form, the table would also be updated.

To specify the default value of a column using a form, you can write an expression that would be applied when necessary.

Practical Learning: Using Expressions to Set Default Values

1. Reopen the GCS2 database
2. In the upper section of the view, click CustomerPhone
3. In the lower section of the view, click Input Mask to select its value and press Delete to remove the default mask
4. Reopen the College Park Auto Shop1 database 
5. Open the RepairOrders table in Design View and click RepairDate
6. In the lower section of the window, set the Input Mask property to 99\->L<LL\-00;0;_
7. In the top section of the window, click TimeReady
8. In the lower section of the window, set the Input Mask property to 09:00\ >LL;0;_
9. Save and close the table
10. In the Database window, click the Forms button
11. Right-click NewRepairOrder and click Design View
12. Change the properties of some controls as follows:
     

    Name	Format	Decimal Places:	Default Value
    txtCustomerName
    txtCustomerAddress
    txtCustomerCity
    txtCustomerState
    txtCustomerZIPCode
    txtCarMakeModel
    txtCarYear		0
    txtProblemDescription
    txtPart1Name
    txtPart1UnitPrice	Fixed		0
    txtPart1Quandity	General Number	0	0
    txtPart1SubTotal	Fixed		0
    txtPart2Name
    txtPart2UnitPrice	Fixed		0
    txtPart2Quandity	General Number	0	0
    txtPart2SubTotal	Fixed		0
    txtPart3Name
    txtPart3UnitPrice	Fixed
    txtPart3Quandity	General Number	0
    txtPart3SubTotal	Fixed		0
    txtPart4Name
    txtPart4UnitPrice	Fixed		0
    txtPart4Quandity	General Number	0	0
    txtPart4SubTotal	Fixed		0
    txtPart5Name
    txtPart5UnitPrice	Fixed		0
    txtPart5Quandity	General Number	0	0
    txtPart5SubTotal	Fixed		0
    txtJobPerformed1
    txtJobPrice1	Fixed		0
    txtJobPerformed2
    txtJobPrice2	Fixed		0
    txtJobPerformed3
    txtJobPrice3	Fixed		0
    txtJobPerformed4
    txtJobPrice4	Fixed		0
    txtJobPerformed5
    txtJobPrice5	Fixed		0
    txtTotalParts	Currency		0
    txtTotalLabor	Currency		0
    txtTaxRate	Percent		0.0775
    txtTaxAmount	Currency		0
    txtRepairTotal	Currency		0
    txtRepairDate	Medium Date Input Mask: 99\->L<LL\-00;0;_		=Date()
    txtTimeReady	Medium Time Input Mask: 09:00\ >LL;0;_		=Time()

13. Save the form
14. Create a record as follows:
     
    
15. Click the Submit Repair Order button
16. Create another repair order as follows:
     
    
17. Close the New Repair Order form