John,

Yes, Yes, that worked perfectly. It was simpler than i thought.

Rule: Use the DBMS to do the work when ever you can!

Thank loads....

"John W. Vinson" wrote:

On Fri, 26 Mar 2010 13:40:01 -0700, Dick Patton
wrote:

Hi all,

I have two tables each is unique. One is the "Merit Badge" tabel the other
"person Id" (name address info) tabel, there is a link tabel "MB-ID_link".
This intent is to connect mert badges with the IDs of Councelors. That works
great, however, i find that i can add duplicate records (same Person, same
Merit badge many times) this is not good.

I created a "check_key" field in the Link tabel which i defined as primary
key. This will stop duplicates from being entered without a lot of code.
The problem is i am not sure how to populate the new key. I know of no way
of concatination in access or where to place the code, but it must be built
before update.

So - I tried: Check_key = trim([MB_code) & [Person_ID]) in the event calle
"before UPdate" that did not work!

It seems like such a simple thig to do!

It is simpler than you're making it, and you certainly do not need to
construct a new field to do it.

Instead, open the link table in design view. ctrl-click the Person ID and the
Merit Bage fields so they're both selected (darkened). Click the key icon to
make these two fields a joint, two-field Primary Key; this will require that
every record be unique for the combination, even though each field by itself
can have duplicates.

If you already have a primary key, you can instead use the Indexes tool on the
toolbar to create a unique twofield index. Put some distinctive index name in
the left column, and the person ID next to it; put the MB Code in the second
column just below the person ID, and check the "unique" checkbox.
--

John W. Vinson [MVP]
.