Home

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

Auto-Increment

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

Fields Sizes

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

  1. Set the fields sizes as follows:
      
    Field Name Data Type Field Size Format
    CustomerName   80  
    CustomerAddress   80  
    CustomerCity   50  
    CustomerState   2  
    CustomerZIPCode   20  
    CarMakeModel   40  
    CarYear   8  
    Part1Name   60  
    Part1UnitPrice Number Double Fixed
    Part1Quantity Number Byte  
    Part1SubTotal Number Double Fixed
    Part2Name   60  
    Part2UnitPrice Number Double Fixed
    Part2Quantity Number Byte  
    Part2SubTotal Number Double Fixed
    Part3Name   60  
    Part3UnitPrice Number Double Fixed
    Part3Quantity Number Byte  
    Part3SubTotal Number Double Fixed
    Part4Name   60  
    Part4UnitPrice Number Double Fixed
    Part4Quantity Number Byte  
    Part4SubTotal Number Double Fixed
    Part5Name   60  
    Part5UnitPrice Number Double Fixed
    Part5Quantity Number Byte  
    Part5SubTotal Number Double Fixed
    JobPerformed1   60  
    JobPrice1 Number Double Fixed
    JobPerformed2   60  
    JobPrice2 Number Double Fixed
    JobPerformed3   60  
    JobPrice3 Number Double Fixed
    JobPerformed4   60  
    JobPrice4 Number Double Fixed
    JobPerformed5   60  
    JobPrice5 Number Double Fixed
    TotalParts Number Double Fixed
    TotalLabor Number Double Fixed
    TaxRate Number Double Percent
    TaxAmount Number Double Fixed
    RepairTotal Number Double Fixed
    RepairDate Date/Time Medium Date  
    TimeReady Date/Time Medium Time  
  2. Save and close the table

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 LearningPractical 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 LearningPractical 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 LearningPractical 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
 

Previous Copyright © 2005-2010 FunctionX, Inc. Next