Validating Complex Data

Although you can certainly take advantage of the Input Mask property and the field and table Validation Rule properties, your application often has additional business rules that you can enforce only by adding code behind the forms you provide to edit the data. The following examples show you how several of the business rules in the Conrad Systems Contacts and Housing Reservations applications are enforced with Visual Basic code.

Checking for Possible Duplicate Names

When you design a table, you should attempt to identify some combination of fields that will be unique across all records to use as your primary key. However, when you create a table to store information about people, you usually create an artificial number as the primary key of the table because you would need to combine many fields to ensure a unique value. Even when you attempt to construct a primary key from first name, last name, address, postal code, and phone number, you still can’t guarantee a unique value across all rows.

Using an artificial primary key doesn’t mean you should abandon all efforts to identify potentially duplicate rows. Code in the frmContacts form in the Conrad Systems Contacts application checks the last name the user enters for a new record and issues a warning message if it finds any close names. For example, if the user creates a new record and enters a last name like “Viscas” (assuming John’s record is still in the table), code behind the form detects the similar name and issues the warning shown in Figure 20–7.

Figure 20–7: The application warns you about a potentially duplicate name in the contacts table.

The code searches for potential duplicates by comparing the Soundex codes of the last names. The formula for generating a Soundex code for a name was created by the U.S. National Archives and Records Administration (NARA). Soundex examines the letters by sound and produces a four-character code. When the codes for two names match, it’s likely that the names are very similar and sound alike. So, by using Soundex, the errorchecking code not only finds existing contacts with exactly the same last name but also other contacts whose name might be the same but one or both might be slightly misspelled.

Access 2007 doesn’t provide a built-in Soundex function (SQL Server does), but it’s easy to create a simple Visual Basic procedure to generate the code for a name. You can find a Soundex function in the modUtility module in both the Conrad Systems Contacts and Housing Reservations sample databases. You can find the code that checks for a potentially duplicate name in the BeforeUpdate event procedure of the frmContacts form. The code is as follows:

 Private Sub Form_BeforeUpdate(Cancel As Integer)  Dim rst As DAO.Recordset, strNames As String    ' If on a new row,    If (Me.NewRecord = True) Then      ' ... check for similar name      If Not IsNothing(Me.LastName) Then        ' Open a recordset to look for similar names        Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName FROM " & _          "tblContacts WHERE Soundex([LastName]) = '" & _          Soundex(Me.LastName) & "'")        ' If got some similar names, collect them for the message        Do Until rst.EOF          strNames = strNames & rst!LastName & ", " & rst!FirstName & vbCrLf          rst.MoveNext        Loop        ' Done with the recordset        rst.Close        Set rst = Nothing        ' See if we got some similar names        If Len(strNames) > 0 Then          ' Yup, issue warning          If vbNo = MsgBox("CSD Contacts found contacts with similar " & _           "last names already saved in the database: " & vbCrLf & vbCrLf & _            strNames & vbCrLf & _            "Are you sure this contact is not a duplicate?", _            vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then              ' Cancel the save              Cancel = True          End If        End If      End If    End If  End Sub

The code checks only when the user is about to save a new row. It opens a recordset to fetch any other contact records where the Soundex code of the last name matches the last name about to be saved. It includes all names it finds in the warning message so that the user can verify that the new contact is not a duplicate. If the user decides not to save the record, the code sets the Cancel parameter to True to tell Access not to save the new contact.

Testing for Related Records When Deleting a Record

You certainly can and should define relationships between your tables and ask Access to enforce referential integrity to prevent saving unrelated records or deleting a record that still has related records in other tables. In most cases, you do not want to activate the cascade delete feature to automatically delete related records. However, Access displays a message “the record cannot be deleted or changed because ‘tblXYZ' contains related records” whenever the user tries to delete a record that has dependent records in other tables.

You can do your own testing in code behind your forms in the Delete event and give the user a message that more clearly identifies the problem. For example, here’s the code in the Delete event procedure of the frmContacts form in the Conrad Systems Contacts application:

 Private Sub Form_Delete(Cancel As Integer)  Dim db As DAO.Database, qd As DAO.QueryDef, rst As DAO.Recordset  Dim varRelate As Variant    ' Check for related child rows    ' Get a pointer to this database    Set db = CurrentDb    ' Open the test query    Set qd = db.QueryDefs("qryCheckRelateContact")    ' Set the contact parameter    qd!ContactNo = Me.ContactID    ' Open a recordset on the related rows    Set rst = qd.OpenRecordset()    ' If we got rows, then can't delete    If Not rst.EOF Then      varRelate = Null      ' Loop to build the informative error message      rst.MoveFirst      Do Until rst.EOF        ' Grab all the table names        varRelate = (varRelate + ", ") & rst!TableName        rst.MoveNext      Loop      MsgBox "You cannot delete this Contact because you have " & _        "related rows in " & _        varRelate & _        ".  Delete these records first, and then delete the Contact.", _        vbOKOnly + vbCritical, gstrAppTitle      ' close all objects      rst.Close      qd.Close      Set rst = Nothing      Set qd = Nothing      Set db = Nothing      ' Cancel the delete      Cancel = True      Exit Sub    End If    ' No related rows - clean up objects    rst.Close    qd.Close    Set rst = Nothing    Set qd = Nothing    Set db = Nothing    ' No related rows, so OK to ask if they want to delete!    If vbNo = MsgBox("Are you sure you want to delete Contact " & _      Me.txtFullName & "?", _      vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then      Cancel = True    End If  End Sub

The code uses a special UNION parameter query, qryCheckRelateContact, that attempts to fetch related rows from tblCompanyContacts, tblCompanies (the ReferredBy field), tblContactEvents, and tblContactProducts, and returns the name(s) of the table(s) that have any related rows. When the code finds rows returned by the query, it formats a message containing names more meaningful to the user, and it includes all the tables that the user must clear to be able to delete the contact. The standard Access error message lists only the first related table that Access finds. Even when the check for related records finds no problems, the code also gives the user a chance to decide not to delete the contact after all.

Verifying a Prerequisite

In some applications, it makes sense to save a certain type of record only if prerequisite records exist. For example, in a school or seminar registration application, the user might need to verify that the person enrolling has successfully completed prerequisite courses. In the Conrad Systems Contacts application, it doesn’t make sense to sell support for a product that the contact doesn’t own. It’s not possible to ask Access to perform this sort of test in a validation rule, so you must write code to enforce this business rule.

Figure 20–8 shows you the message the user sees when trying to sell support for a product that the contact doesn’t own. This message also appears if the user attempts to sell the special upgrade to multi-user product, and the contact doesn’t already own the prerequisite single user product.

Figure 20–8: Special business rule code won’t let you sell a product with a missing prerequisite.

The code that enforces this business rule is in the BeforeUpdate event procedure of the fsubContactProducts form.The code is as follows:

 Private Sub Form_BeforeUpdate(Cancel As Integer)  Dim lngPreReq As Long, strPreReqName As String    ' Check for prerequisite    If Not IsNothing(Me.cmbProductID.Column(6)) Then      ' Try to lookup the prerequisite for the contact      lngPreReq = CLng(Me.cmbProductID.Column(6))      If IsNull(DLookup("ProductID", "tblContactProducts", _        "ProductID = " & lngPreReq & " And ContactID = " & _        Me.Parent.ContactID)) Then        ' Get the name of the prerequisite        strPreReqName = DLookup("ProductName", "tblProducts", _          "ProductID = " & lngPreReq)        ' Display error        MsgBox "This contact must own prerequisite product " & strPreReqName & _          " before you can sell this product.", vbCritical, gstrAppTitle        ' Cancel the edit        Cancel = True      End If    End If  End Sub

Remember from Figure 20–2 that the query providing the row source for the cmbProductlD combo box includes any prerequisite product ID in its seventh column. When the code finds a prerequisite, it uses the DLookup function to verify that the current contact already owns the required product. If not, then the code looks up the name of the product, includes it in an error message displayed to the user, and disallows saving the product by setting the Cancel parameter to True. This enforces the business rule and makes it crystal clear to the user what corrective action is necessary.

Maintaining a Special Unique Value

When two subjects have a many-to-many relationship in your database, you must define a linking table to create the relationship. (See Article 1, “Designing Your Database Application,” on the companion CD for details about designing tables to support a many-to-many relationship.) You will often add fields in the linking table to further clarify the relationship between a row in one of the related tables and the matching row in another. Figure 20–9 shows you the table in the Conrad Systems Contacts application that defines the link between companies and contacts.

Figure 20–9: The tblCompanyContacts table defines the many-to-many relationship between companies and contacts.

Two special yes/no fields in this table identify which company is the default for a contact and which contact is the default for a company. However, a contact can’t have two or more default companies. Likewise, it doesn’t make sense for a company to have more than one default contact. To verify this type of special unique value constraint you must add business rules in code behind the forms you provide the user to edit this data.

You can find the code that ensures that there is only one default company for each contact in code behind the fsubContactCompanies form in the Conrad Systems Contacts sample application (Contacts.accdb). The code is in the BeforeUpdate event procedure for the DefaultForContact control on the form. The code is as follows:

 Private Sub DefaultForContact_BeforeUpdate(Cancel As Integer)    ' Disallow update if there's no Company ID yet    If IsNothing(Me.CompanyID) Then      MsgBox "You must select a Company / Organization before" & _        " you can set Default.", _        vbCritical, gstrAppTitle      Cancel = True      Exit Sub    End If    ' Make sure there's only one default    ' Check only if setting Default = True    If (Me.DefaultForContact = True) Then      ' Try to lookup another contact set Default      If Not IsNothing(DLookup("ContactID", "tblCompanyContacts", _        "ContactID = " & Me.Parent.ContactID & _        " AND CompanyID <> " & Me.CompanyID & _        " AND DefaultForContact = True")) Then        ' ooops...        MsgBox "You have designated another Company as the" & _          " Default for this Contact." & _          "  You must remove that designation before you" & _          " can mark this Company as the Default.", _          vbCritical, gstrAppTitle        Cancel = True      End If    End If  End Sub

First, the code verifies that the user has chosen a company for this record. (The Link Child Fields and Link Master Fields properties of the subform control provide the ContactID.) Next, if the user is attempting to mark this company as the default for the contact, the code uses the DLookup function to see if any other record exists (in the tblCompanyContacts table for the current contact) that is also marked as the default. If it finds such a duplicate record, it warns the user and sets the Cancel parameter to True to prevent saving the change to the control. You’ll find similar code in the fsubCompanyContacts form that makes sure only one contact is the primary for any company.

Checking for Overlapping Data

When you build an application that tracks the scheduling of events or reservations that can span a period of time, you most likely need to make sure that a new event or reservation doesn’t overlap with an existing one. This can be a bit tricky, especially when the records you’re checking have start and end dates or times.

Of course, the Housing Reservations application (Housing.accdb) must make sure that an employee doesn’t enter an overlapping reservation request. To see how this works, open the sample database and then open the frmSplash form to start the application. Choose any employee name you like from the combo box in the sign-on dialog box (Jack Richins is a good choice), type password as the password, and click the Sign On button. On the main switchboard, click the Reservation Requests button. If you see the Edit Reservation Requests dialog box (because you happened to sign on as a manager), click the Edit All button.

The Reservation Requests form won’t let you enter a reservation start date in the past. Click in the blank new row in the list of reservation requests, enter a reservation request for next week for a span of several days, and save the row. (Remember, you can click the Calendar buttons that appear next to the date fields when the focus is on the field to help you choose dates.) Enter another request that overlaps the reservation you just created either at the beginning, the end, or across the middle of the reservation you just entered. Try to save the row, and you should see a warning message similar to the one in Figure 20–10.

Figure 20–10: The Housing Reservations application displays a warning when you attempt to save an overlapping reservation request.

If you click No, the code cancels your save and returns you to the record to fix it. Notice that you can click Yes to save the duplicate-the application allows this because an employee might want to intentionally reserve two or more rooms on the same or overlapping dates. The code that performs this check in the BeforeUpdate event of the fsubReservationRequests form is as follows:

 Dim varNum As Variant    ' Check for overlap with existing request    ' Try to grab RequestID - will be Null on unsaved row    varNum = Me.RequestID    If IsNull(varNum) Then varNum = 0 ' Set dummy value    If Not IsNull(DLookup("RequestID", "tblReservationRequests", _      "(EmployeeNumber = " & _      Me.Parent.EmployeeNumber & ") AND (CheckInDate < #" & Me.CheckOutDate & _      "#) AND (CheckOutDate > #" & Me.CheckInDate & "#) AND (RequestID <> " & _      varNum & ")")) Then      If vbNo = MsgBox("You already have a room request " & _        "that overlaps the dates you have " & _        "requested.  Are you sure you want to make this request?", _        vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then        Cancel = True        Exit Sub      End If    End If

The code uses the DLookup function to see if another reservation exists (but a different request ID) for the same employee with dates that overlap. The criteria asks for any record that has a check-in date less than the requested checkout date (an employee can legitimately check out and then check back in on the same date) and a checkout date that is greater than the requested check-in date. You might be tempted to build more complex criteria that checks all combinations of reservations that overlap into the start of the requested period, overlap into the end of the requested period, span the entire requested period, or are contained wholly within the requested period, but the two simple tests are all you need. (Draw it out on a sheet of paper if you don’t believe us!)