Preventing duplicate entry

P

PWG

Registered User.
Local time
Today, 11:42
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
 
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
 
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
 
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.
 
I think using code should be more reliable. Come back if you have problems & someone will assist.
 
PWG said:
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.
Click to expand...
Hi. For data validation, using the BeforeUpdate event is preferable over using the AfterUpdate event.
 
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.
 
You must log in or register to reply here.

Similar threads

tmd63
How to use a combo box to filter a forms data
Replies
4
Views
184
Pat Hartman
P
B
Test if a compound. (two or more joined fields) exists
Replies
7
Views
453
Pat Hartman
P
J
Need Help with Form
Replies
15
Views
641
GaP42
G
E
Solved Restrict Data Entry
Replies
13
Views
789
Emma35
E
B
+
Replies
4
Views
275
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom