MS-Access / Getting Started

---

Preventing Duplicate Records Across Multiple Fields

Primary keys and multiplefield indexes in client tables to create composite keys. You learned how you could enforce uniqueness across multiple fields in a record instead of on just one field. Web tables do not support primary keys other than the ID field, nor do they support multiple-field indexes. To work around this limitation for web tables, you can create a data macro attached to a Before Change event that checks for duplicates across multiple fields. (Note that you can also do the same for client tables; however, it's unnecessary because you can create multiple-field indexes in client tables.)

In the Back Office Software System sample web database data copy (BOSSDataCopy.accdb), several of the web tables use this technique to prevent duplicates across multiple fields. Open the tblVendorDeliveryDays in Datasheet view, click the Table contextual tab under Table Tools, and then click the Before Change button in the Before Events group to open the Logic Designer. We closed the Action Catalog so you could see more of the macro design surface.

Each vendor that delivers products to the restaurant using this database usually delivers more than once during any week, but each vendor delivers only once on the specific delivery day. We don't want users of the database to accidentally enter the same delivery day of the week more than once for the same vendor; if we did, we would have repeating records. We cannot enforce this restriction in web tables unless we use a data macro in the Before Change event to check the combination of both the VendorID and the WeekDayID fields for duplicates before committing new or changed data to the table. The data macro logic is as follows:

Comment Block: Check for composite key violation. We should not
have the same vendor recorded more than once for a specific weekday.
Check values going in against saved values.
LookUpRecord In tblVendorDeliveryDates
 Where Condition = [VendorID]=[tblVendorDeliveryDates].[VendorID] And
		   [WeekDayID]=[tblVendorDeliveryDates].[WeekDayID]
 Alias
 Comment Block: If we reached this point, we already have a record
 in the system for  that vendor and weekday. Raise an error, inform
 the user of the duplicate record, and then stop the update.
 RaiseError
    Error Number: -1
    Error Description: A record already exists in the system for
                       that weekday and this Vendor.

The LookupRecord data block takes three arguments:

• Look Up A Record In Required argument. The name of a table, query, or SQL statement to look up a record in.
• Where Condition Optional argument. The expression that Access uses to select records from the table, query, or SQL statement.
• Alias Optional argument. A substitute or shorter name for the table.

When Access enters the LookupRecord block, the default data context is the incoming record. The incoming record is either a new record or changes to an existing record. Access evaluates the Where condition of a data block with the same default context as when you are inside the data block. This means that if you do not use an alias as the table qualifier for field names in the Where condition argument, you are referring to a field within the new default data context that you just created by using the data block.

In the first part of our example Where condition argument, [VendorID]=[tb]VendorDeliveryDates].[ VendorID], VendorID refers to the VendorID of the new record being created or changed. We compare that value to a saved VendorID that already exists in the table. Similarly, we do the same type of comparison with the WeekDayID field. Access then tries to see if there is a record currently in the table with the same VendorID and WeekDayID as the new or changed record being committed to the table. If Access finds a match in the LookupRecord block, this means that a record exists for the combination of the two fields. Access then executes the RaiseError action, displays the custom error that we created, and then cancels the update.

If you want to see Access enforce this restriction, you can close the Logic Designer and return to the tblVendorDeliveryDates in Datasheet view. Next, create a new record in the table and select a vendor and weekday that matches an existing record. You'll notice that Access displays the custom error message that we defined in the RaiseError event. You'll see the same error message if you try to change an existing record with values that match a different record.

You can apply this technique of enforcing uniqueness across multiple fields to more than two fields by simply modifying the Where condition argument of the LookupRecord data block to include additional fields with the AND operator. Using data macro logic inside the Before Change event of web tables allows you to achieve the data integrity that multiple field indexes provide in client tables.

The Back Office Software System sample web database includes Before Change events attached to other web tables besides the two examples you've already seen. You can explore the data macros attached to these events for additional examples.

• tblCompanyInformation Prevents additional records from being added to the table. Uses IsInsert property to test for new records.
• tblLaborHours Prevents duplicates across multiple fields using the LookupRecord data block. Uses the SetLocalVar action to construct a custom message to user that pulls data from other tables.
• tblSettings Prevents additional records from being added to the table. Uses IsInsert property to test for new records.
• tblTimeLookups Prevents additional records from being added to the table. Also prevents changes to existing data as well. Uses IsInsert property to test for new records.
• tblVendorDelieveryDays Prevents duplicates across multiple fields using the LookupRecord data block.
• tblVendoOrderDays Prevents duplicates across multiple fields using the LookupRecord data block.
• tblWeekDays Prevents additional records from being added to the table and prevents changes to existing datal. Uses the IsInsert property to test for new records.

If you are working in a client database, you can nest LookupRecord blocks inside each other up to 10 levels deep. If you are using a web database, however, and intend to publish the database to a SharePoint server, you should not nest any LookupRecord data blocks in Before events-Before Change and Before Delete. If you nest LookupRecord data blocks in a Before event, the web table fails to compile on the server. Nesting LookupRecord blocks inside Before events are not supported on the server. To work around this limitation for web databases, you can separate the LookupRecord blocks and use local variables and If blocks to accomplish the same goal as nesting. If you examine the Before Change event in the tblLaborHours web table, you can see we could have simplified the logic by nesting the two LookupRecord blocks. Because of the server limitation on nesting in Before events, we had to use two separate LookupRecord blocks to test for duplicate records and then display the employee name of the duplicate record.