Prevent Duplicates

[bw]

The first three Field Names of my tblModules has the following information...
BMKey......Autonumber, Indexed (No Duplicates)
AddrID......Number, Indexed (Duplicates OK)
MonicsID...Number, Indexed No, Has a Lookup Table

While MonicsID is not indexed, I would like to prevent one specific value
from being duplicated.

Is this possible either in Table or Forms Design? If so, how?

Thanks,
Bernie

 

[Jeff Boyce]

Bernie

Are you saying that you have a table tlkpMonics, with MonicsID to allow
duplicates, except for one value? Or are you saying that you have a
"lookup" data type in your tblModules, and that field is named MonicsID?

If the latter, you may run into confusions and difficulties, as Access
stores an ID, but displays a "looked up" value when you display that field.
If so, you are better off converting that field to the appropriate data type
to hold the MonicsID as a foreign key.

Now to your question. Tables are "buckets-o-data", and aren't the best
mechanisms for controlling and displaying data. Forms offer a great event
environment, with much stronger control/display features. You can probably
add code behind a form displaying your tblModules records that prevents
duplication of your one specific MonicsID value.

However, if you are saying that your OTHER MonicsIDs could be duplicated, I
hope you mean that there could be more than one tblModule record with the
same MonicsID value (this would be a typical parent-to-child relationship).
I don't know how you could allow duplicate IDs within a tlkpMonics table, if
that's what you're describing...

 

[Robin Proctor]

If you're into VBA you could add a Check Constraint to the table using
Data Definition SQL, e.g.

CurrentProject.Connection.Execute _
"ALTER TABLE tblModules" & _
" ADD CONSTRAINT OnlyOne42" & _
" CHECK ((SELECT Count(*) FROM tblModules WHERE MonicsID=42)<= 1)"

Your business rule is now part of the table design.

You can get rid of the constraint with this SQL:
ALTER TABLE tblModules DROP CONSTRAINT OnlyOne42

You can only use execute this type of query from VBA.