Data Entry and Record Maintenance
|
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.
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
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
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.
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.
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.
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
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.
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
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.
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.
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 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
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 |
|
|
||
Practical
Learning: Requiring Field Values
.
This would open the Find and Replace dialog box:
