Home

Data Entry and Record Maintenance

 

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

Finding a Value

 

Introduction

Consider the records in the following table named Videos and that is part of a database named Video Collection:

Many pieces of information are missing from this table and some others display an error. Like the regular objects of a database, records must be visited, changed, updated, and maintained. Microsoft Access provides the means of visually performing these operations. Some other times, you can perform them programmatically. The way you proceed may depend on your intended result, the sought behavior, and the library you want to use.

Looking For a Value

One of the primary actions you can perform on the values of a table or a form consists of looking for a value. This can be a prerequisite to many other operations. After opening a table in a Datasheet View or a form in Form View, to look for a value, on the toolbar, you can click the Find button Find. This would open the Find and Replace dialog box:

The Find What combo box allows you to type the value to search for. Normally, it is a combo box but, by default, it is empty. If you had performed a search before, the previous values would be in the Find What combo box so you can use them.

When the Find and Replace dialog box comes up, the field that was clicked prior to calling this window would display in the Look In combo box. If the Find What combo box contains the value you want to look for, then you can keep it. If you want to look for the value in the entire table, then click the arrow of the Look In combo box and select Table option.

The Match combo box allows you to specify if you want to consider the complete string of the field or only a section of it. The default value is Whole Field. To change it, you can click the arrow of its combo box and select another option.

If you are using Microsoft Access 2000 and if the default options are not enough, you can click the More >> button to expand the dialog box:

Once you are ready to look for the value, click Find Next.

Editing a Value

 

Visually Editing a Value

Value edition consists of changing the value held in a field with another value. To do this, a user usually examines the records to locate the value first. To do this, the user can use the Replace dialog box. To open it, after displaying a table or a form, on the toolbar, the user can click the Find button Find and click the Replace tab. As an alternative, on the main menu, the user can click Edit -> Replace:

The Replace dialog box presents some similar options as the Find dialog box. These include the Find What, Look In and Match combo boxes, also the Find Next, the Cancel and the More buttons. The Replace With combo box allows you to specify the value that will replace the existing value. Here is an example:

After specifying the values, you can click Replace. If you want to replace more than one occurrence of the undesired string, you can click Find Next to locate them one by one and replace only those you want. If you want to replace all occurrences of a value, you can click Replace All.

Editing a Value With SQL

To edit a value using SQL, you start with the UPDATE keyword and follow this formula:

UPDATE TableName
SET ColumnName = NewValue

In our formula, the TableName factor must be the name of a table in the selected or current database. The ColumnName must be a valid column in the TableName table. The NewValue factor of our formula is the new value that will replace the old one. If you use this formula, all records of the ColumnName would be given the NewValue. In some cases, you may want to change only one or more particular value. To do this, you must specify a condition that would be used to identify a record. The formula to use is:

UPDATE TableName
SET ColumnName = NewValue
WHERE Condition

The Condition factor specifies how the value will be located.

From the above table, imagine that you want to replace Peter Sagal with Peter Segal. The TableName is Videos. The column that holds the value is named Director. The NewValue is Peter Segal. The Condition can be that the shelf number is CM-8842. Based on this, to edit the value, you can use the following code:

UPDATE 	Videos
SET 	Director = "Peter Segal"
WHERE 	ShelfNumber = "CM-8842"

This can be done in code as follows:

Private Sub cmdMaintenance_Click()
    Dim strDataUpdate As String
    
    strDataUpdate = "UPDATE Videos " & _
                    "SET Director = 'Peter Segal' " & _
                    "WHERE ShelfNumber = 'CM-8842';"
    
    DoCmd.RunSQL strDataUpdate
End Sub

In the same way, in the list of videos, notice that the videos titled "Indecent Proposal" and "Fatal Attraction" have a name of director that looks the same. After checking the videos, you find out that they were actually directed by the same person. So you decide to prgrammatically edit it. You can write the SQL statement as follows:

UPDATE 	Videos
SET 	Director = "Adrian Lynn"
WHERE 	ShelfNumber = "DM-7426"

Before executing the statement, you check the table again but find out that, either by mistake or something else, another video is using the same shelf number. This means that if you execute the statement, any video that uses the same shelf number would have its director replaced, including those videos that don't have the same error. The solution to this is to make sure that you always have a way to distinguish each video from another. This is why the primary key is important. We saw in previous lessons that, when you use the AutoNumber data type on a column, Microsoft Access would take on the responsibility to assign each record a unique identifier. This value is furthermore required if the column is a primary key. Based on this, you can safely use the value of this record when doing data maintenance, such as updating a value. Therefore, the above change can be performed by using the value of the VideoID corresponding to the video whose director you want to update:

UPDATE 	Videos
SET 	Director = "Adrian Lynn"
WHERE 	VideoID = 5

This would be done programmatically as follows:

Private Sub cmdMaintenance_Click()
    Dim strDataUpdate As String
    
    strDataUpdate = "UPDATE Videos1 " & _
                    "SET Director = 'Adrian Lynn' " & _
                    "WHERE VideoID = 5;"
    
    DoCmd.RunSQL strDataUpdate
End Sub

This time, because we know that only one video has a VideoID equal to 5, only its record will be changed.

Record Maintenance

 

Introduction

Record maintenance consists of copying a record, deleting a record, or importing records from a table in another database. Microsoft Access doesn't allow inserting (creating a new record between two existing records), moving (changing the position of a record such as from index 8 to index 4 or from index 2 to index 5) or switching records (such as putting record 2 at position 5 and putting record 5 at position 2).

Copying a Record

Imagine that, in a table, you have an existing record that already holds many values of a new record you want to create. Instead of retyping all values, you can copy the existing record to the new record and make only the few needed changes. If you display a table in Datasheet View, to copy a record, you can right-click the box on the left side of the most left field of the column and click Paste:

To apply the copied record to a new one, right-click the left box of the first empty record and click Paste.

Before copying a record in a form, the form should have the Record Selectors property set to Yes. To copy a record, you can right-click the record selector and click Copy:

After copying the record, you can get to the first empty record, right-click its record selector and click Paste.

After pasting a record, you can make the necessary change. You must also manually take care of ensuring the uniqueness of values in the appropriate fields. For example, if a column is meant to hold unique values such as employee numbers or Social Security numbers, after pasting the record, to finalize it, you must make sure you change the value in the new record. Otherwise, before moving on, you would receive an error.

To programmatically copy a record, you can retrieve the value of each column and create a new record using those values.

Deleting a Record

Deleting a record consists of removing it from a table (or a form). to visually do this, after displaying a table in the Datasheet View, you can right-click the box on the left side of the record and click Delete Record:

You can also click Cut. The difference is that the Cut option would copy the record to the clipboard so you can paste it anew.

To programmatically delete a record using SQL, you combine the DELETE operator in the following primary formula:

DELETE FROM TableName

When this statement is executed, all records from the TableName table would be removed. Here is an example:

Private Sub cmdClearCustomers_Click()
    DoCmd.RunSQL "DELETE FROM Customers"
End Sub

In this case, all customer records from a table named Customers in the current database would be deleted. An alternative to the above formula is:

DELETE *
FROM TableName

In this formula, you use the * operator as the column placeholder. You can replace it with one or more names of columns but it doesn't matter because the DELETE operator signifies that the whole record will be deleted, regardless of the column name.

The TableName must be a valid name of a table in the specified or the current database. Here is an example:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE * FROM Videos;"
End Sub

If you execute this type of statement, all records from the table would be deleted. We saw above that the user can specify what particular record to delete instead of all records. You also can specify what record to remove from a table. To do this, use the following formula of the DELETE operator:

DELETE *
FROM TableName
WHERE Condition

This time, the Condition factor allows you to set the condition that would be applied to locate the record. Consider the following table:

Here is an example of specifying a condition to delete a record:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE * " & _
                 "FROM Videos " & _
                 "Director = 'Adrian Lynn';"
End Sub

When this code runs, all videos directed by Adrian Lynn would be deleted from the table. Instead of deleting all records like this, you may want to remove only one particular video. To do this, you must set a condition that sets that record apart. Once again, the condition can be easily handled by the primary key.

 

 

Previous Copyright Yevol, 2007 Next