Avoiding Duplicate Insert

[Sprinks]

For a manufacturing operation, a many-to-many relationship exists between
Products and Ingredients. Formulas for making 1 unit quantity of each
product are stored in a linking table, ProductIngredients:

ProductIngredients
---------------------
ProductIngredientID AutoNumber (PK)
ProductID Integer (Foreign Key to Products)
IngredientID Integer (Foreign Key to Ingredients)
Fraction Single

Sum of [Fraction] equals 1 for each product.

When a new batch is made, it is desirable to record the batch number of each
ingredient. The approach has been, on initializing a new batch, to enter the
ProductID, and the quantity, and then press a command button to copy the
associated ProductIngredients to a table BatchIngredients:

BatchIngredients
-------------------
BatchIngID AutoNumber (PK)
BatchID Integer (Foreign Key to Batch)
RawMaterialID Integer (Foreign Key to RawMaterials)
BatchNumber Integer or Text

These records appear on a subform based on a query linking BatchIngredients
to ProductIngredients (to pick up the Fraction field) on a main form based on
Batches. The operator may then fill in the Batch Number of each raw material.

However, the user could conceivably press this button more than once,
inserting duplicate records. I would like help in identifying the best way
to prevent this. I've thought of these strategies:

1. Disable the command button after it is pressed.
2. Add Boolean a field to Batches and toggling it on when the button is
pressed, and modifying the command button code.
3. Restructuring the table to avoid having to copy these records in the
first place, although I haven't been able to figure out how.

Thanks for all thoughtful advice.
Sprinks

 

[Jim Shaw]

Your issue stems from using Autonumber fields for your PK. No matter how
you try to protect against duplicates, users will find a way to defeat your
system. For example, if you disable the button, they will reload the form
and enter duplicates that way.

Your BatchIngredients table is an associative table and is necessary in 3rd
normal form, so keep that.

My though is to get rid of the autonumber PK field and create a compound
primary key using the two foreign keys: BatchID & RawMaterialID. Then
Access will catch the duplication attempt and generate an error which you
can trap and handle any way you want.
Be sure to index both key fields with duplicates ok. That only allows
duplicates in that single field and disallows duplicates having the same
combination of the data in both fields for the PK value.

Hope this helps.
Jim

 

[Sprinks]

Thanks for your timely and on target response. Best regards.

Your issue stems from using Autonumber fields for your PK. No matter how
you try to protect against duplicates, users will find a way to defeat your
system. For example, if you disable the button, they will reload the form
and enter duplicates that way.

Your BatchIngredients table is an associative table and is necessary in 3rd
normal form, so keep that.

My though is to get rid of the autonumber PK field and create a compound
primary key using the two foreign keys: BatchID & RawMaterialID. Then
Access will catch the duplication attempt and generate an error which you
can trap and handle any way you want.
Be sure to index both key fields with duplicates ok. That only allows
duplicates in that single field and disallows duplicates having the same
combination of the data in both fields for the PK value.

Hope this helps.
Jim

For a manufacturing operation, a many-to-many relationship exists between
Products and Ingredients. Formulas for making 1 unit quantity of each
product are stored in a linking table, ProductIngredients:

ProductIngredients
---------------------
ProductIngredientID AutoNumber (PK)
ProductID Integer (Foreign Key to Products)
IngredientID Integer (Foreign Key to Ingredients)
Fraction Single

Sum of [Fraction] equals 1 for each product.

When a new batch is made, it is desirable to record the batch number of each
ingredient. The approach has been, on initializing a new batch, to enter the
ProductID, and the quantity, and then press a command button to copy the
associated ProductIngredients to a table BatchIngredients:

BatchIngredients
-------------------
BatchIngID AutoNumber (PK)
BatchID Integer (Foreign Key to Batch)
RawMaterialID Integer (Foreign Key to RawMaterials)
BatchNumber Integer or Text

These records appear on a subform based on a query linking BatchIngredients
to ProductIngredients (to pick up the Fraction field) on a main form based on
Batches. The operator may then fill in the Batch Number of each raw material.

However, the user could conceivably press this button more than once,
inserting duplicate records. I would like help in identifying the best way
to prevent this. I've thought of these strategies:

1. Disable the command button after it is pressed.
2. Add Boolean a field to Batches and toggling it on when the button is
pressed, and modifying the command button code.
3. Restructuring the table to avoid having to copy these records in the
first place, although I haven't been able to figure out how.

Thanks for all thoughtful advice.
Sprinks