Thank you, Jeff i am sorry i did not communicate well. There are 3 table and
MB-Link forms the retionship between the merit badge and the councilor but
both are one to many relationships so i neede3d a way to store only those
that are unique combinations and John's suggestion worked perfectly. I just
wish i could trap the error and give a used friendly message like "this
record already exists".

"Jeff Boyce" wrote:

Dick

I'm not sure I understand your situation, what it is that you are trying to
use Access to do.

One possible scenario for what you've described might be:

* You have people
* You have merit badges
* You have people-with-meritbadge(s)

If that's your situation, I think you need to be looking at three tables
instead of two.

More information, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Dick Patton" wrote in message
...
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!

Help

Dick


.