Validation for Product Id = Product Name

[Ramnik]

hello i am developing a data entry system for production in a factory . i have developed the tables as :

masterproducts
productid(primary key) product name
12345 first product
23423 second product
43543 third product

these are one to one i.e. productid=productname

Inputproduct (to be entered by user)
date productid(foreignkey) productname
2-09-09 12345 second product ???? how to validate this entry ??
if user enters a product id and by mistake he enters wrong name what should he do ? i confirm that i cant miss this field as it can be done with a query instead but i need the user to enter it strictly. how to relate the master table or how to validate the productid=productname
thanks ..

 

[John Big Booty]

Why not have the user select the product from a Combo Box? In that way you eliminate the possibility of the user entering incorrect details.

 

[Ramnik]

thanks for the suggestion but the combo box is just a utility , it will help the user to choose exactly from the available values but the question is if the user chooses wrong name which dont match with id how do we validate that ???????????? remember there is only one id to one name ....

 

[John Big Booty]

If you set the Combo's Limit To List property to True there will be no need to validate the input as the user can only select from the available values.

 

[Ramnik]

I have done that already my dear friend .. kindly read the problem again or may be m missing something ...
my point is that when i choose id from a combo box which is reference to master table nd then i choose name from combo box which also from master table but the validation of same product id = same product name as defined by the master table .... suppose i choose 12345 but instead of choosing first product i chose second product in product name .. how do i validate that ?

 

[SteveH2508]

You should not store the product name in the InputProduct table. You only store the ProductID as a foreign key - this leads you back to all of the product details in the Masterproduct table. Normalisation 101.

 

[John Big Booty]

I suspect that your Combo box are not set up properly. In fact I'm not entirely sure why you have two combo's given that One ID seems to represent only One product.

If you are doing this so that one combo shows the ID and the other the Product, then you are going about things in a rather difficult manner.

Have a look at the attached sample

 

[Ramnik]

Suppose i do want user to enter both ... is there any way of this validation ???? i want the user to be easily enter the name ...... moreover i cant make the name field primary key in master table as it is the calculated field with cancatenation ......

 

[Ramnik]

okkkk i have got ur point .... this would help ..... but is it possible to validate the two fields ????????????????????????????????????

 

[John Big Booty]

If the Combo is limited to list why do you need to validate anything as there is no way for your user to enter an invalid part.

 

[Ramnik]

leave everything just a simple question ....
is there any way of validating about field1 = field2 on entry as specified from other table (mastertable) .. field1 and field2 are unique nd one to one.

 

[John Big Booty]

Have a look at the DLookup() function. However I suspect you are making a rod for your own back

 

[ButtonMoon]

leave everything just a simple question ....
is there any way of validating about field1 = field2 on entry as specified from other table (mastertable) .. field1 and field2 are unique nd one to one.

You can put a foreign key constraint on the the pair of columns to ensure they remain consistent with the other table. First you'll need to add a uniqueness constraint on the same two columns in the table you are referencing ("mastertable" in your example).

Where this creates a non-key dependency it is probably not a good idea to have both columns in the second table. You should be cautious about that kind of denormalization. However, if you do use a constraint to ensure consistency then the redundancy is kept under control - the data will at least remain consistent between the two tables.