Locking cus_num to cus_name

[revnice]

I want a specific number to be associated with a particular customer at all times. If a customer is deleted, the associated number is deleted forever as well.

I have a little (primary) key symbol against both num and name columns in the cus table (Design View) - which I thought would lock them together, but it doesn't.

If I sort either column, the wrong number ends up next to the customer name.

How do I lock them together?

Thanks - rev

[RuralGuy]

If they're in the same table then you can't at the table level. If the number is the primary key for the table (the little key symbol you mentioned) but not the Name field in that table, you will be allowed change the name should you need to but the number will go away when the record is deleted. If that field is an AutoNumber then the number will never be used again. It is the built in Referential Integrety enforcement (if turned on) that helps when there is more than one table involved.

[revnice]

So I should dump my Access-generated PK and use the cus_num as the PK instead - with data-type AutoNumber and referential integrity turned on?

Have I got that right?

BTW: Where do you turn on referential integrity?

[RuralGuy]

If you allowed Access to add a Primary key then it *is* an AutoNumber. That is all you need in that table for a PrimaryKey. RI is turned on as you begin defining your relationships, which is why I said more than one table.

[revnice]

Ok, understood all that but I'm still not clear on how to lock num to name.

Can't num and name be part of a composite key in the same table? If so, how do you do it?

[RuralGuy]

Of course, but I'm not real convinced that is what you want to do. That will keep you from changing the Company name which you can enforce in a form if that is what you want to do.

[revnice]

So how do I make a composite key?

I only see a button for Primary, nothing for 'secondary.'

I tried putting the little PK symbol against both columns but as noted, that didn't work.

[RuralGuy]

This link should help: http://www.btabdevelopment.com/ts/mfi