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.

 

[Uncle Gizmo]

Feed the following function with a select SQL statement that identifies "BasketNumber 999 in the table with 'EndDate' null"

It will return:
'0 = No Matching Record
'1 = One Matching Record

Use that info in your logic....

Public Function fProcessSQL(strSQL As String) As Byte
'Input a "SELECT" String that needs counting
'Three Possible Values Returned to fProcessSQL
'0 = No Matching Record
'1 = One Matching Record
'2 = Two or More Matching Records

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.Open strSQL

    Select Case rs.RecordCount
        Case Is = 0 '0 = No Matching Records
            fProcessSQL = 0
            rs.Close
            Exit Function
        Case Is = 1 '1 = One Matching Record
            fProcessSQL = 1
            rs.Close
            Exit Function
        Case Is > 1 '2 = Two or more Matching Records
        
'////////// Record This "CASE" (>1) to Error Table, UNLESS "No Tag" or Similar
'////////// Will Need to Pass the Value Checked
            
            fProcessSQL = 2
            rs.Close
            Exit Function
            
        Case Else   'A value other than an Interger was returned by the rs.RecordCount (Should Never happen)
            MsgBox "From Case Else"    'If you suspect a problem then activate this message box
            fProcessSQL = 0 '0 = No matching record
            rs.Close
            Exit Function
    End Select
End Function      'fProcessSQL

 

[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!

 

[Uncle Gizmo]

1st Step create a query that returns

BasketNumber 999 in the table with 'EndDate' null