Hello.

I would like to know how to restrict the user from using identical values but only for a specific record type. Let me explain.

A project is set up with a MasterProjectID. This is an indexed value and is a primary key so each project has a unique MasterProjectID.

Each project has several value types. For example, each project has many door numbers. This is stored in a separate table, tblOpeningTakeoff which has a one-to-many relationship with the table, tblProjectInfo containing the MasterProjectID (among other pproject specific information). I want to limit the user from duplicating values such as door numbers within a specific project.

For example, Project 1 may have the following door numbers:
100
101
101A
102
.
.
.

Project 2 may have the following door numbers:
100
105
110
.
.
.
These are both valid scenarios.

What I need to do is the following. If the user enters the following door numbers:
100
101
102
101
An error message would appear informing the user that door number 101 has already been used and that this is an duplicate value (not OK) etc.

How can I do this?

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=27757&highlight=dcount

Thanks!

Why isn't door number part of the primary key?

When you say "part of" my primary key, I'm not sure I understand. The reason is that every project may have the same door number, door number 100, for example, but each project can have only one of each door number.

If you are implying that I combine fields, for example ProjectID and DoorNumber together to make a single primary key, this would work but I do not know how I would set this up.

If this is not what you mean, I do not understand what you are asking.

That is what Pat means. In order to do that, go into the table in Design mode. Put the two fields next to each other in the table design (I'm not sure if this is necessary, but it can't hurt). Highlight both fields with the cursor, and then hold down Shift and Right-click on them. If both stay selected, choose "Primary Key". You'll now see Key symbols on both of them at once.

This is a multi-part Primary Key, and it will unfailingly prevent duplicates. However to catch the duplicate before you finish the record (for example, when you enter a Door for a Project), go with the Dcount suggestion above in the Door_field BeforeUpdate event as well.

Good luck,
David R

Here is the problem that I now encounter:

In my table, tblProjectLockOptions, I only have the following fields:

ProjectLockOptionID
MasterProjectID
OptionID


The ProjectLockOptionID is the PK and the other two are foreign keys. The table, tblOptions, has the following fields:

OptionID
OptionDescription
OptionType


So now I am left to do 1 of 2 things. Add the OptionType to my tblProjectLockOptions thereby increasing the redundancy of my program, or using some other method that uses a query to bring the fields together.

Any ideas?

What's wrong with using a query that connects tblProjectLockOptions to tblOptions via OptionID, and show OptionType in the query?

Your question is a little vague as to what is 'wrong'...

Here is the problem:

Each project has a specific lock type with one or more options. Each option has a class assigned to it to prevent the user from creating combinations of options that are not possible.

For example, a lock contains one cylinder (Class:cylinder) option, one strike (Class:Strike) option, one latch(Class:Latch) option etc.

The foreign key, OptionID, is an integer that is saved in the table tblProjectLockOptions but the CLASS is not saved in that table. Pat suggested that I make two fields part of the primary key. In this case, the second required value is not in the table tblProjectLockOptions.

I therefore, do not see how I can join the ProjectID and the LockClass as primary keys to prevent a project from having duplicate cylinder options, or duplicate strike options, etc.