Prevent adding if conditions not met

[IKZOUHETNIETWETEN]

I have 3 unique unit numbers. They can only go IN and OUT on a date. Right now I can add a new line through the Form: Unit name: 423 In date: for ex. 18-05-2015 and click on Save. It updates in my table. Perfect. BUT the unit was already IN on 31-01-2014 according to my table, so basically I dont want it to be IN again! The table just updates to the newest data. Is there a way to prevent this? It can be blocked in Excel through data Validation, how about Access? Thanks!

[June7]

Yes, Access has data validation. Options:

1. set field or combinations of fields in table as Index (No Duplicates)

2. VBA code behind form, possibly the BeforeUpdate event, to look for existing data and cancel the new record if data found

3. structure a combobox to only list units that are not associated with the specified data (known as dependent or cascading combobox)

[IKZOUHETNIETWETEN]

Thanks. I attached my easy form and 2 unitnumbers database.
Im quite a newbee, started Access this week. Could you help me out? So prevent adding a movement of unitnumber H0168 with an IN date. Adding an out date should be working.
Database11.accdb

The VBA I use in Excel is the following regarding data validation: (Column A = unit number, B = In date, C = Out date, D = start Date, F = Project number)

   Dim cell As Range, bErr As Boolean     
    For Each cell In ActiveSheet.UsedRange.Columns("B").Cells
        If Not cell.Validation.Value Then
        
                    bErr = True
            MsgBox cell.Address(False, False) & " Unit is al in!"
            
            cell.Select
    Range("a:i").End(xlDown).Select
    Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).EntireRow.Delete






        End If
    Next
        
        For Each cell In ActiveSheet.UsedRange.Columns("A").Cells
        If Not cell.Validation.Value Then
        
                    bErr = True
            MsgBox cell.Address(False, False) & " Unit bestaat niet!"
            
            cell.Select
    Range("a:i").End(xlDown).Select
    Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).EntireRow.Delete




        End If
    Next


    
    
    
    
    
    
    For Each cell In ActiveSheet.UsedRange.Columns("C").Cells
        If Not cell.Validation.Value Then
        
                    bErr = True
            MsgBox cell.Address(False, False) & " Unit is al uit!"
            
            cell.Select
    Range("a:i").End(xlDown).Select
    Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).EntireRow.Delete






        End If
    Next


    
    
        
    For Each cell In ActiveSheet.UsedRange.Columns("F").Cells
        If Not cell.Validation.Value Then
        
                    bErr = True
            MsgBox cell.Address(False, False) & " Verkeerd project!"
            
            cell.Select
    Range("a:i").End(xlDown).Select
    Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).EntireRow.Delete






        End If
    Next
    If Not bErr Then MsgBox "Unit toevoeging succesvol!"
    
End Sub

[June7]

Sorry for late response, missed your post. Do you still need help? Did you try any of the 3 options?

[IKZOUHETNIETWETEN]

No problem, I think I solved itback then as I have no running issues! I will open a new thread if Im encountering any problems. Thanks for asking, Ill mark it as solved!