Composite key.... i think (1 Viewer)

[mlopes1]

I have a form where I choose a company and then choose a product #. Each of these is a combo box. I am storing my choices in a table called "Selected". I want to make sure that the same Product-Company combination is not allowed to be entered. I know how to not allow duplicates for one field, but how do I do it for a combination?

In other words, a company can buy many products, and a product can be bought by many companies... but one company can not re-purchase the same product.

I am pretty much a new user but I think I need to somehow use the composite key, but how? Thanks for your help as always!

marco

 

[Travis]

In access you can use more then one field to make up the Primary Key. Simply by Clicking each field in the Design View using the Control Key, and then clicking on the Primary Key option.

This will place a "Key" next to the fields that you have selected and prevent an entry where both fields already match.

Example

If your table already had this information in it:

Company Product
=========================
XYZ Inc Widget Type A


A new record could not be XYZ Inc, Widget Type A.

It could however be XYZ Inc, Do-Dad Type A, or ABC Inc, Widget Type A.

 

[The_Doc_Man]

A composite primary key would certainly do what you wanted, though the error messages you would get might be ugly when someone attempts a violation of the validation rule.

To implement it this way, in table design mode, where it gives you a list of the fields, you select two rows (click the first row; hold the CONTROL key when you click the second row) and then click the little KEY symbol in the iconic tool bar. That makes the two selected rows members of your compound primary key.

Once you do that, you will be unable to enter the same combination twice. I would advise you to try it first before letting your customer see it because of the side effects it would have on the "look and feel" of your application.