Prevent Duplicates

  • Thread starter Thread starter bw
  • Start date Start date
B

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
 
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...
 
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.
 
Back
Top