Avoid Duplicate Records with BeforeChange Table Event

Question

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?

Answer 1

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.