Entering Data

The Data control is a great tool for browsing tables and editing their contents, but how about entering new information or deleting existing records? These actions require a few lines of code. Nothing extreme, but you can’t rely on the no-code approach of the Data control for data entry. To write data entry applications, you use the following methods:

1. AddNew appends a record to the table.
2. Delete deletes the current record.
3. Update writes the current record to the database.
4. reloads the data from the database (refreshes the RecordSet).

AddNew To add a new record to a table, you call the AddNew method of the control Recordset (it .must be.a Table type of RecordSet). A new blank record is appended at the end of the RecordSet, and the Data control is positioned at this record. The user can now enter. data in the data-bound controls.

Update, Refresh When the user signals an intention to commit the new record to the database (by clicking OK of moving to a new record), you call the Update method of the control’s RecordSet. To reject the data, call the Refresh method, which refreshes the RecordSet by reading it from the database. Since the newly appended record hasn’t been committed to the database, it is lost when the RecordSet is refreshed.

Delete To delete a record, call the Delete method of the control’s RecordSet. The current record is removed from the Recordset, but only after the JET engine checks any relations. that might be affected. If the database enforces certain references, you won’f be able to remove a record that’s referenced by another one. I’m not going’to discuss referential integration at length in this book, but let’s see hew the JET engine maintains the integrity of the references in a database.

Referential Integrity

The NWIND database, which also comes with Visual Basic, enforces the integrity of certain relations, To see how this works, follow these steps:

1. Using the Visual Data Manager, open the NWIND database, and doubleclick the Orders table, which contains all the invokes.
2. Select an invoice and make a note of the customer ID to which the invoice was issued.
3. Close this table, and double-click the name of the Customers table to open it in data entry mode.
4. Select the customer whose ID appeared in the invoice, and click Delete. The JET engine displays the error message shown in Figure 17.12.

This error message tells you that the.customer you are trying to delete is referenced in another table and can’t be removed.

If a relation is important for the integrity of the database, you can ask the JET engine to enforce it Enforcing referential integrity is one of the most important features of a database management system. Without it, your program would have to ensure that important relations are enforced (not a simple task).

Although you can enforce certain references from within your code, the best way to implement this feature is by incorporating referential integrity in the database itself. You can’t use the Visual Data Manager for this purpose, but if you use . a more advanced tool for designing databases, such as Microsoft Access, you can specify which relations must be enforced by the system, and this information is stored in the database itself.

VB6 at Work: The Data Entry Project

Now we are ready to build a real data entry application with add and delete features, as shown in Figure 17.13. When the user clicks the Add Record button, the application calls the AddNew method and hides the two buttons on the Form. In their place, it displays the usual OK and Cancel buttons. The user can commit the new record to the database by clicking OK or reject it by clicking Cancel. In either case, the OK and Cancel buttons are hidden, and the Add. Record and Delete Record buttons are displayed again.

The Form of the Data Entry application contains a Data control, whose RecordSet is the Titles table of the BIBLIO database. The textboxes are all bound to the Data control, and they display the Title, ISBN, Description, Subject, and Comments fields of the table.

As you recall from the discussion of the structure of the BIBLIO database, each record in the Titles table has a key that links the title to a publisher. Instead of providing a textbox in which the user can type the ID of the title’s publisher, we provide a ComboBox control with the names of all publishers; the user can select one of the publishers by name. This control isn’t the usual Visual Basic ComboBox control. It’s a data-bound ComboBox control, anti WE’ will look at it in the section” Advanced Data-Bound Controls,” later in the chapter. .

To build this application, follow these steps:

1. Add a Data control to the Form and set its DatabaseName property to the path of the BIBLIO database and its RecordSource property to the Title’s table.
2. Place the five textboxes you see on the Form in Figure 17.13 and bind them to the Data1 Data control. Set each text box’s DataField property to the appropriate field of the table.
3. Now, create four Command buttons and set their Name and Caption properties as shown in Table 11.9.

   

    

4. Position the buttons on the Form SO that the OK and Add Record buttons coincide and SO that the Cancel and Delete buttons coincide .

Now you are ready to program the application.

The Hide Buttons subroutine hides the Add Record and Delete Record buttons and displays the other two buttons. The AddNew button appends a new blank record.at the end of the RecordSet and repositions the Data control at this record. The user can enter new data in the TextBox controls and then click OK to write the changes to the database.

The Update method commits the temporary record to the database. The Show Buttons subroutine then hides the OK and Cancel buttons and displays the other two buttons.

If you cancel an add operation, you’ll be brought back to the first record in the table (that’s what happens when you refresh a RecordSet). To return to the last record displayed, the code stores the record’s ISBN field in the variable prevISBN. This action takes place when the Add .button is clicked. If the user cancels the operation, the program searches for the record with the same ISBN and displays it again.

The Delete method deletes the current record, After a delete opreation, the RecordSet isn’t moved to another record automatically. Instead, it remains on the deleted record until a Move button is pressed. The code moves to the next record, unless the record deleted was the last one, in which case it moves to.the previous record. If the deleted record was the only record in the table, the program displays a message and remains on·the current record.

.
The ShowButtons and HideButtons subroutines manipulate the Visible properties of the buttons. In addition, they hide and display the Data control so that the user. can end a data entry operation with only the Ok or Cancel button.

If you open the Data Entry application with Visual Basic, you will find a few more interesting subroutines. In the Data control’s Error event, for example, the program displays the error message and cancels the update operation. This may happen if the user edits the current record and enters invalid information in one of the fields.

In the Data control’s Validate event, the program finds out whether any data bound control has been changed, and if so, it prompts the user as to whether it should save the changes to the table. .

Typically, the code behind the Validate ‘event takes into consideration the value of the Action argument and reacts according to the action that caused the error .