Q: Avoid Duplicate Records with BeforeChange Table Event I have a situation in MS Access database that I must prevent duplicate records based on combination of three attributes:


*

*StudentNumber

*ColleagueID

*TypeOfAttending
So, for one combination (StudentNumber & ColleagueID) I have three types of attending: A, B and C.
Here is an example:
+---------------+-------------+---------------+
| StudentNumber | ColleagueID | AttendingType |
+---------------+-------------+---------------+
|           100 |          10 | A             |
|           100 |          10 | B             |
|           100 |          10 | C             |
|           100 |          11 | A             |
|           100 |          11 | B             |
|           100 |          11 | C             |
|           100 |          11 | C             |
+---------------+-------------+---------------+

So last row would not be acceptable.
Does anyone have any idea?

A: As noted, you could choose  all 3 as a PK. Or you can even create a unique index on all 3 columns. These two ideas are thus code free.
Last but least, you could use a Before change  macro,and do a search (lookup) in  the table to check if the existing record exists. So far, given your information, likely a unique index is the least effort, and does not require you to change the PK to all 3 columns (which as noted is a another solution).
So, you could consider a before change  macro. And use this:
Lookup a Record in MyTable
   Where Condition = [z].[Field1]=[MyTable].[Field1] And 
                     [z].[Field2]=[MyTable].[Field2] And 
                     [z].[ID]<>[MyTable].[ID]
   Alias Z
   RaiseError   -123
   Error Description: There are other rows with this data

So, you can use a data macro, use the before change table macro. Make sure you have the raise error code indented "inside" of the look up code. And note how we use a alias for the look up, since the table name (MyTable) is already in context, and is already the current row of data, so we lookup using "z" as a alias to distinguish between the current row, and that of lookup record.
So, from a learning point of view, the above table macro can be used, but it likely less work and effort to simply setup a uniquie index  on all 3 columns. 
