Preventing Duplicate Entries

[JPed]

Hi there,

I am attempting to make a system where the lifespan of baskets in a production process can be tracked. In order to do this I am looking to create a form to input when a new basket is introduced.

Each basket has a 'disc' associated with a number on (BasketNumber), however these discs are reused once the basket breaks which is making it more difficult.

I have a form (Add_New_Basket) which feeds into a table (Active_Basket); what I am trying to do is make sure that a new basket cannot be introduced with the same disc number as an active basket. The way I am trying to determine which baskets are active is that I have an end date, that is filled in once the basket has been scrapped.

Therefore I am trying to set my Add_New_Basket form to restrict me entering a new record with BasketNumber 999, if there is already a BasketNumber 999 in the table with 'EndDate' null. If there is BasketNumber 999 and EndDate is dd/mm/yyyy then it can create the entry.

My form Add_New_Basket has fields ID (which is hidden), BasketNumber, StartDate (=Date())

My table has fields: ID, BasketNumber, StartDate, EndDate

I hope this makes sense and any help I could solve this would be greatly appreciated.

 

[JPed]

Hey thanks for the reply. Not sure where I'd put all this? Pretty new at Access!

Think I have come up with something of a solution I just don't know how to do the code.

I have created a query based on the table (ListOfActiveBaskets), which only shows baskets are active (i.e. don't have an EndDate).

Therefore on my Add_New_Basket form, I want to be able to use a Dlookup (or something similar) to only allow an entry if it is NOT in the Active Basket query.

Something like this in the BeforeUpdate of the form?

If (Me.Crucible_Number = DLookup("[BasketNumber]", "[ListOfActiveBaskets]")) Then
Msg.Box "This basket disc is already in use. Please use form 'Active Baskets' and create an end date."
Me.Undo

I just know I'm missing something just not sure what or where this should go!