Access 2010 FAQ
Can you use data macros to simulate a multi field index?
Web tables in Access 2010 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.)
Consider an example with vendors delivering to a restaurant. 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 accidently 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 BeforeChange 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
LookUpRecord In tblVendorDeliveryDates
Where Condition = [VendorID]=[tblVendorDeliveryDates].[VendorID] And
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.
Error Number: -1
Error Description: A record already exists in the system for that weekday
and this Vendor.
And here is a screenshot of the Logic Designer showing the above data macro logic.
If a user creates a new record that includes the combination of a vendor and a weekday that already exists in the table, Access prevents the new record from being committed.
See more tips and tricks like this in my book: Microsoft Access 2010 Inside Out
Back to FAQ main page