Matching 3 Fields For a Record To Avoid Duplicate Records

[Happy2]

My Balance table has an BalanceID field as Autonumber type. This Autonumber field is not a primary key.
It's just an identifier for a composite key (as primary key) that includes 3 fields. These fields are:

1. Social security number
2. Type
3. SubType

I cannot use the social security number field as a primary key because a person can have more than 1 balances with different types. There are also more than 1 balances with the same type.

I need to avoid users from entering duplicate records. I need help with matching all existing records in the
Balance table, then let users know the record already exists and stop them from entering duplicate records.

Please help. Thank you so much.

 

[LittleSmudge]

It sounds to me like you are in despirate need of Normalising your data structure.

Have you formally Normalised your design ?
Or are you stuck with what you've got because of history ?


If you are stuuck with the design you have, why cann't you just set the three fields as a composite Primary Key ?
That will fix your problem won't it?


G LS

 

[Happy2]

Hi LittleSmudge and everyone,

My table is fine as I posted that I had a composit key as a primary key, which included the 3 fields: Social security number, Type, and SubType. However, I have to let my users to be able to search an agent at anytime as required. This search combo box is supposed to save a new added record then requery it so the new added record will be shown on the search combo box. I am having a trouble that when I am adding a new record and by an accident I am having a duplicate of primary key. If I decide to search back that agent to find out what is the primary key that I should assign for the new record, the SQL Server would stop me and show a debug of my SAVE code. I want a message instead of the debug of my SAVE code. I am not supposed to let the debug shown on my program. I need help please, everyone. I greatly appreciate your help.

 

[Rolliee]

Use an unbound form and you can check as much as you wish before you 'turn your data loose.'

rollie @bwsys.net