not duplicate on text field

hfsitumo2001

Member
Local time
Yesterday, 17:55
Joined
Jan 17, 2021
Messages
394
In my inventory table, I have location-bin field. How can I make validation rule, to say not duplicate

Thank you
 
Have you tried using a unique index?
 
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
 
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'.
 
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
 
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.
 
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.
 
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.
 
Well then make the location-bin required.
Then put a unique index on the field.

No duplicates
 

Users who are viewing this thread

Back
Top Bottom