How to stop duplicate entries when inputting to a database through a userform.

[mageeg]

I have a Userform full of textboxes which allows users to enter information into a database.

the code below allows me to do this, however if i want to make e.g. the txtID and txtName unique, so that no other fields in the database can contain these. How would i go about doing this?

Sub Submit()

'code for submitting data to database

    Dim sh As Worksheet
    Dim iRow As Long
    
    Set sh = ThisWorkbook.Sheets("Database")
    
    If frmForm.txtRowNumber.Value = "" Then
    
        iRow = [Counta(Database!A:A)] + 1
    Else
    
        iRow = frmForm.txtRowNumber.Value
        
    End If
    
    
    With sh
    
    'adding each row to database
    
        .Cells(iRow, 1) = "=Row()-1" 'Dynamic Serial Number
        
        .Cells(iRow, 2) = frmForm.ComboBox1.Value
        
        .Cells(iRow, 3) = frmForm.txtName.Value
        
        .Cells(iRow, 4) = frmForm.txtID.Value
        
        .Cells(iRow, 5) = frmForm.txtCity.Value
        
        .Cells(iRow, 6) = frmForm.TextBox1.Value
        
        .Cells(iRow, 7) = frmForm.TextBox4.Value
        
        .Cells(iRow, 8) = frmForm.TextBox5.Value

 

[dmt32]

Hi,
untested but try following update to your code & see if does what you want

Sub Submit()
    
    'code for submitting data to database
    
    Dim sh          As Worksheet
    Dim iRow        As Long
    Dim m           As Variant
    
    Set sh = ThisWorkbook.Sheets("Database")

    m = Application.Match(Me.txtID, sh.Columns(4), 0)
    
    'check for match in range & if match found, you can update existing record
    If Not IsError(m) And sh.Cells(CLng(m), 3).Value = Me.txtName.Value Then

        'inform user & exit sub
        MsgBox Me.txtID.Value & " " & Me.txtName.Value & Chr(10) & "Record Exists", 48, "Record Exists"
        Exit Sub
    End If
    
'rest of code

Alternatively, rather than exit if match found, You could if wanted, update the matched record

Note use of Me keyword as I have assumed code is on your forms code page

Dave