Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Practical Examples: Applying These Techniques to Your Application

The potential applications for the methodologies learned in this chapter are endless. This section explores just a few of the ways you can apply these techniques. The examples here are located in Chap14Ex.mdb.

Using Recordset Methods on a Data-Entry Form

At times, you might want to disable the default record movement and add, edit, or delete functionality from a form and code all the functionality yourself. You might want to perform these actions if you are going against client/server data and want to execute additional control over the data-entry environment. You also might want to use these techniques when you are developing applications for both the Access and Visual Basic environments and are striving for maximum code compatibility. Regardless of your reasons for using the following techniques, it is a good idea to know how to assign a Recordset object to a form and then use the form's underlying recordset to display and modify data.

Figure 14.3 shows a form in which the navigation buttons and record selectors have been removed. The form contains six command buttons: Move Previous (<), Move Next (>), Add, Delete, Find, and Exit. All the buttons use the recordset underlying the form to move from record to record in the form and modify the data contained within the form.

The RecordSource property of the form is not set. The Load event of the form is responsible for assigning a Recordset object to the form. Listing 14.75 shows the Load event of the form.

Listing 14.75 The Load Event Assigning a Recordset Object to the Form

The code begins by declaring and instantiating an ADODB Recordset object. It then sets three properties of the Recordset object: the ActiveConnection, the CursorType, Cursor Location, and the LockType. The Open method is used to open a recordset, based on the tblClients table. Finally, a Set statement is used to assign the recordset to the recordset underlying the form.

NOTE

When an ADO recordset is assigned to a form, and the form is based on Jet data, the form is rendered read-only. If an ADO recordset is assigned to a form based on SQL data, the form is rendered read/write. If you want to render a form based on Jet data as read/write, you must set the CursorLocation property of the Recordset object to adUseClient.

Listing 14.76 shows the code for the Move Previous button.

Listing 14.76 Code for the Move Previous Button

This routine performs the MovePrevious method on the Recordset property of the form. If the BOF property becomes True, indicating that the record pointer is before the first valid record, the MoveNext method is performed on the Recordset property of the form to return the record pointer to the first record in the recordset. Finally, the bookmark of the form is synchronized with the bookmark of the Recordset property. Listing 14.77 shows the code for the Move Next button.

Listing 14.77 Code for the Move Next Button

The code for the Add button is a little tricky, as Listing 14.78 shows.

Listing 14.78 Code for the Add Button

The AddNew method is performed on the Recordset property of the form. This method creates a buffer in memory that is ready to accept the new data. When the Update method is issued, the record pointer is moved to the new record. Because the CompanyName field is a required field, you must populate it with data before issuing the Update method on the Recordset property.

By setting the bookmark of the form to the Bookmark property of the recordset, you synchronize the form with the new record. In a production environment, you would want to clear out all the text boxes and force the user to save or cancel before the AddNew or Update methods are issued.

The process of deleting a record is quite simple, as Listing 14.79 shows.

Listing 14.79 Deleting a Record

CAUTION

Because the tblClients table is linked to the tblProjects table, the process of deleting a client will render an error if that client has associated projects. This must be handled using standard error handling techniques.

This code verifies that the user actually wants to delete the record, and then issues the Delete method on the Recordset property of the form. Because the current record no longer is valid, the code calls the Click event of the cmdNext button.

The last piece of code involved in the form is the code for the Find button, as shown in Listing 14.80.

Listing 14.80 Code for the Find Button

This routine begins by storing the bookmark of the current record to a Variant variable. Users are prompted for the client ID they want to locate, and then the Find method is issued on the Recordset property of the form. If the EOF property is True, the user is warned, and the bookmark of the recordset is set to the value within the Variant variable, returning the record pointer to the position it was in prior to the search. If the client ID is found, the bookmark of the form is synchronized with the bookmark of the Recordset property.