not duplicate on text field

[hfsitumo2001]

In my inventory table, I have location-bin field. How can I make validation rule, to say not duplicate

Thank you

 

[theDBguy]

Have you tried using a unique index?

 

[hfsitumo2001]

Have you tried using a unique index?

Not Yet DbGuy, but let's just make it simple.On the table property, for this loc-fin field, on the validation rule, what should we say

 

[theDBguy]

Not Yet DbGuy, but let's just make it simple.On the table property, for this loc-fin field, on the validation rule, what should we say

Not validation rule; but in index, select 'no duplicates'.

 

[hfsitumo2001]

Not validation rule; but in index, select 'no duplicates'.

But will it jeopardize my queries?, because right now I have not filled yet the location bin, this is for future plan. I have query consist of inventory table linked to supplier, Purchase Order and Order Details table.

Thank you

 

[theDBguy]

But will it jeopardize my queries?, because right now I have not filled yet the location bin, this is for future plan. I have query consist of inventory table linked to supplier, Purchase Order and Order Details table.

Thank you

Maybe, maybe not. I can't tell since I can't see your database. Give it a try and if doesn't work, you can turn it back off.

 

[Pat Hartman]

You can index a field even if it is null but you can't change it to required unless any existing rows are populated.
@hfsitumo2001, it is always better to use RI to enforce rules than to use code. RI works no matter what, code only works if you run the form that includes the code. So, if you set a unique index, you can't add a duplicate with an append query. And if your BE is shared by multiple apps, the rule is enforced in every app that links to the table.

 

[gemma-the-husky]

Are all your bins one item only, and is there never a case that an item may be in more than location? If not , what if you really have a duplicate.

 

[hfsitumo2001]

Are all your bins one item only, and is there never a case that an item may be in more than location? If not , what if you really have a duplicate.

One item is in one bin

 

[gemma-the-husky]

Well then make the location-bin required.
Then put a unique index on the field.

No duplicates

 

[Pat Hartman]

What I said??