Preventing duplicate entry (1 Viewer)

PWG

Registered User.
Local time
Today, 05:13
Joined
Jun 13, 2019
Messages
56
I have a form that is used to add new parts to my products table.
The fields are
Part number
Description
Location
Supplier
Cost
When I open the form it goes to a new record that I can add a new part.
all good
The problem is that I can type a duplicate part number that is already in my products table.
The Part Number field in my table is set to no duplicates so I do not know that it is a duplicate until I go to safe the record.
I have tried refresh the form after entering this value but i get a new blank form to enter date.I think I need to use a validation rule is this correct. If so what would it look like
 

isladogs

MVP / VIP
Local time
Today, 12:13
Joined
Jan 14, 2017
Messages
18,186
Just to clarify is Part Number your primary key field?
If so it will indeed prevent duplicates being added so what exactly is your issue?
Are you entering data directly in an unbound form or, as you should be, in a bound form
 

PWG

Registered User.
Local time
Today, 05:13
Joined
Jun 13, 2019
Messages
56
Part Number is not my Primary key field but i have set it to no duplicates.
The form is created from a quire that links my supplier table and other.
My issue is that I do not get told that I have entered a duplicate part number until i have completed the form and the save the record. i would like to know as soon as I entre just the part number that it is a duplicate
 

PWG

Registered User.
Local time
Today, 05:13
Joined
Jun 13, 2019
Messages
56
Thanks for your help. I have found that I can do what I want with using the macro Refresh on the after update.
The problem now is that the result is the standard warning boxes 3 of them
I will see if i can find out if I can do the same thing with writing code and point it to the strings table for the text.
 

isladogs

MVP / VIP
Local time
Today, 12:13
Joined
Jan 14, 2017
Messages
18,186
I think using code should be more reliable. Come back if you have problems & someone will assist.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:13
Joined
Oct 29, 2018
Messages
21,357
Thanks for your help. I have found that I can do what I want with using the macro Refresh on the after update.
The problem now is that the result is the standard warning boxes 3 of them
I will see if i can find out if I can do the same thing with writing code and point it to the strings table for the text.
Hi. For data validation, using the BeforeUpdate event is preferable over using the AfterUpdate event.
 

Mark_

Longboard on the internet
Local time
Today, 05:13
Joined
Sep 12, 2017
Messages
2,111
One item you may want to look at; you are saving "Cost" with the part.

Normally I would see this is a child table that has, at least,
Parent ID (from the Part table)
DateEffective
Cost

This way when you find out the cost of the part changes, you can simply add a child record reflecting WHEN the cost changed and the new cost amount. This avoids a LOT of headaches down the road when calculating profit margin based off of sales price VS piece cost.
 

Users who are viewing this thread

Top Bottom