not duplicate on text field (1 Viewer)

hfsitumo2001

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

Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:05
Joined
Oct 29, 2018
Messages
21,358
Have you tried using a unique index?
 

hfsitumo2001

Member
Local time
Today, 01:05
Joined
Jan 17, 2021
Messages
365
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

I’m here to help
Staff member
Local time
Today, 01:05
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 01:05
Joined
Jan 17, 2021
Messages
365
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

I’m here to help
Staff member
Local time
Today, 01:05
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 04:05
Joined
Feb 19, 2002
Messages
42,981
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

Super Moderator
Staff member
Local time
Today, 08:05
Joined
Sep 12, 2006
Messages
15,614
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.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:05
Joined
Sep 12, 2006
Messages
15,614
Well then make the location-bin required.
Then put a unique index on the field.

No duplicates
 

Users who are viewing this thread

Top Bottom