Skip to main content

Access Junkie - MVP Alumnus

Home
Access Inside Out Books
Access 2010 FAQ
Access 2007 Training
Access 2007 FAQ
Access 2007 KB Articles
Technical Articles
Links
MSDN Articles
Blogs
Forums
Contact Us
About Us
Member Login
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
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.

 

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