Coding the Commit Button (Add)

See Also

Quick Tour

Employee Form

To commit an add, we place the dynaset in add mode using DbAddNew. We then assign the new data to the dynaset fields and update the database using DbUpdate. To make our program robust, we validate some fields before attempting to add them to the database.

Our Commit_Click() event procedure for adding records does the following:

1. Checks that at least the Employee Number and Department fields are not null.
2. Checks that the new Employee Number is not a duplicate entry.
3. Places the dynaset in add mode using DbAddNew.
4. Assigns entered data to dynaset fields using the Fields().Value property.
5. Updates database with new record, using DbUpdate.
6. Disables the Commit button.
7. Enables the Add button.

To check for duplicate entries in Step 2, we create a local dynaset with the NOCACHE option, using a SQL statement that counts the rows matching the entered Employee Number field. If we get a match (row count greater than 0), the entered employee number is a duplicate entry and an error is displayed. In this case where the SQL select statement returns only a number, creating the dynaset without a cache is a more efficient error check than catching a duplicate entry error from the server.