You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
A composite key consists of more than one field in an index. They are often used to ensure uniqueness across fields in a record. You can use unique multiple field indexes to define a set of columns that uniquely identify a row so as to prevent users from entering in the same record twice. Composite keys (multiple field indexes) are not supported on Access Services; however you can use Data Macros to get the same behavior. Let’s look at a common example for a school registration systems.
We have a Students and a Classes table:
The Enrollment table keeps track of which students are in which classes. The Student and Class fields are lookups to the Students and Classes tables respectively.
Using Indexes, we can prevent a Student from being in the same class multiple times by creating a new index for the Enrollment table, like below. This will throw an error every time you try to add a student to a class they are already in.
In Access 2010, we can get the same functionality using data macros. In the BeforeChange event, just set up a LookupRecord block to look through the Enrollment table to see if the student is already enrolled in the class. If so, it will throw an error to the user; otherwise the student will be added to the class.
Here is the user experience of the app running:
Hopefully, this article is helpful as you migrate your apps to the Web.
Enjoy!
Kerry Westphal
Comments: (12) Collapse
Nice and simple,yet effective. Now I understand what the "Alias" bit means in a macro too!
I have a question about the implementation here. In a normalized join table stored in a database that supports composite keys, the usual PK is the unique composite index of the two foreign keys -- there is not need for an additional surrogate Autonumber PK (though the unique constraint is required either way). Is the surrogate key required for compatility with Sharepoint, i.e., so that your Sharepoint list has a single PK field?
David @ SharePoint requires a surrogate key--particularly an autonumber as the PK.
Is there any way of using a data macro to enforce referential integrity with multi-value fields, please? When I set up a multi-value field in my main table it does not let me set referential integrity. I've tried playing with the Before Delete data macro to try and check whether a record in the lookup table is used in the main table before allowing the deletion of the record in the lookup table, but have not succeeded. Access tells me I can't use a multi-valued field (or an attachment, which is fair enough) in a data macro. I've even tried checking a query based on the main table where I've used the Value property of the multi-valued field to get at the individual values within that multi-valued field but had no joy. Thanks.
Let me explain that a bit better. I have a main table with a multi-valued field which gets its values from a lookup table. If I delete a record from the lookup table which is in use in the multi-valued field in the main table, this is allowed and am left with a "confused" record in the main table. My lookup table consists of an Autonumber field and a text field. When one of these records is deleted, the multi-valued field in the main table is left showing the old Autonumber value plus the text value of still existing records from the lookup table. Thus if my multi-valued field was initially showing "Jack, Jill" and I delete the "Jill" record from the lookup table, I get left with something like "Jack, 47", which is a bit of a mess.
As much as I agree this is a nice, simple solution it makes me feel a little dirty to read it. Really, "programming by exception" is bad application design. A better way is to perform a check to see if a record exists for the Student/Class Enrollment BEFORE you try to insert the record. Furthermore, shouldn't we be implementing the data access via a user interface (i.e. forms) rather than allowing direct access to the data? Then put the business rules (e.g. duplicate records are not allowed) behind the form. If a user is knowledgeable enough to directly manipulate table data then they shouldn't be replying on data constraints to ensure data integrity. Proper application design is the good fight we really need to push more because "quick and easy" only causes us more time, effort and pain later.
@Jason Snelders said:
>shouldn't we be implementing the data access via a user interface If you can centralize this type of logic and code into one single place, then it is a better design choice. When possible you want the user interface to give the user friendly message, but that’s a different issue here. When you publish the database to the web, then you can use a web browser, perhaps a smart phone or pda, or some program such as Access running on your desktop. In other words we have so many varied ways of asking for data and to modify it, it doesn’t make sense to try and redo the program logic over and over again in 5 different forms spread all over the place. If you have to change that code, then how do you know which forms in your application now need code to be changed? And if you forget to modify the code in JUST 1 of those 5 forms, then your application is broken. And, what about running in web browser or smart phone? Do we write the code there also? One big complaint of professional database developers about Access is how it lacks stored procedures and database triggers. Now, Access 2010 joins the ranks of big boy database systems like Oracle or SQL server. What is neat is we have these ttriggers and stored procedures in an easy to use desktop program. This is most certainly what I would call power to the desktop people! Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
@ Alan Cossey said: >Let me explain that a bit If you use multi-value lookups, you can’t setup cascade delete restrict. If you really need this and you are not able or unwilling to write code in the form to check this before user hits delete, then just create this using a classic junction table. You can use the relationships window to create this or if this is web only, then use the lookup + relationships wizard. In BOTH these cases you can then use the lookup “delete restrict” option. Lookups can restrict delete. However, for multi-value, I am afraid you are out of luck. I think most of us prefer creating the junction table anyway and your example is just shows another reason why. About the only downside here is we all love that cool multi-drop down combo box. If you not looking to use the cool combo check box, then use a junction table and you be just fine. However, this reminds me of something! Access team suggestion: how about you expose and give us that new fancy combo multi-value control as a new control in the palette of controls for use on any form without it being bound to a MV column. Albert D. Kallal (Access MVP)
Albert,
I agree with your statement. When I tried multi-valued fields in Access 2007 I hit a dead end (though I can't remember what it was!) and decided never to go that way again. However, I thought I'd give it another go in Access 2010 since there is so much good new stuff available. Unless someone comes up with a means of filtering and sorting, then I am going to have to give up on multi-value fields again. It is a real shame as the ability to create a delimited list from the multiple selection in a web database would have been really useful. Will have a play and see if I can recreate this using the normal junction table setup. Alan
In VBA, I would use the following code to raise a user-defined object error that would not conflict with system errors: Err.Raise vbObjectError + > Should I be adding vbObjectError when raising errors in macros and if so, is there a nice way to reference vbObjectError? (as I understand, the vbObjectError constant is not available in the macro editor)
If your table contains more fields than those shown above you will need to add an extra criterion into the existing Where Condition to handle changes when those extra fields get altered, i.e. And (EnrollmentLookup.ID <>Enrollment.ID) seems to work. Otherwise, if you have another field in the table, say, Class Description, any changes to Class Description in an existing record will fail. Alan
I don't seem to be able to have a Before* data macro (as needed in this method) and have a calculated field in the same table. Anyone else have the same problem? I get an error message about the table having been opened exclusively when I try to add a calculated field.
Comments: (loading) Collapse