Detect Duplicate Entry as soon as its typed into the Userform

[cherias]

Dear All,

I have developed a user form with multiple text boxes and combo boxes. Request your assistance in writing the VBA code, which detects and pops message of DUPLICATE ENTRY, as soon as something (say ID number) is typed into the first text box or when tab is pressed to move to the next text box.

I am new to VBA coding, so I am referring and using codes published on different forums and websites. I have come across many codes, which requires a person to click on SUBMIT button to actually detect and pop out message of duplicate. But in case, there are several text boxes, then it becomes tedious to fill all the data and then be told that a duplicate entry exists. Its not a good user experience.

Kindly help me out. Thanks in advance.

 

[LloydFinancials]

What is the code for your user form? Where is the range that gets searched for duplicates?

 

[cherias]

Hello Lloyd,

The VBA code for adding new entry is --

Private Sub cmdaddnewentry_Click()

Dim nextrow As Range

'set the next row in the database
Set nextrow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)

'check for values in all the 25 controls
For X = 1 To 25
If Me.Controls("Reg" & X).Value = "" Then
MsgBox "You must add all data"
Exit Sub
End If
Next

'check for duplicate service request numbers
If WorksheetFunction.CountIf(Sheet2.Range("B:B"), Me.reg1.Value) > 0 Then
MsgBox "This service request already exists"
Exit Sub
End If

'number of controls to loop through
cNum = 25

'add the data to the database
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next

'clear the controls
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Next


Exit Sub

End Sub


The VBA code to search for data is as given below. The search result is displayed in a ListBox and then on double clicking, it gets filled in respective text and combo boxes.

Private Sub cmdsearchsr_Click()

Lookup

End Sub

Sub Lookup()

'declare the variables
Dim rngFind As Range
Dim strFirstFind As String

'clear the listbox
lbsearchsr.Clear

'look up based on service request number
With Sheet2.Range("B:B")
Set rngFind = .Find(txtsearch.Text, LookIn:=xlValues, lookat:=xlPart)

'if value found then set a variable for the address
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address

'add the values to the listbox
Do
If rngFind.Row > 1 Then
lbsearchsr.AddItem rngFind.Value
lbsearchsr.List(lbsearchsr.ListCount - 1, 1) = rngFind.Offset(0, 1)
lbsearchsr.List(lbsearchsr.ListCount - 1, 2) = rngFind.Offset(0, 2)
lbsearchsr.List(lbsearchsr.ListCount - 1, 3) = rngFind.Offset(0, 3)
lbsearchsr.List(lbsearchsr.ListCount - 1, 4) = rngFind.Offset(0, 4)
lbsearchsr.List(lbsearchsr.ListCount - 1, 5) = rngFind.Offset(0, 5)
End If

'find the next address to add
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
End If

End With

'disable data editing
Me.reg1.Enabled = False
Me.cmdeditentry.Enabled = False

Exit Sub


Private Sub lbsearchsr_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

'declare the variables
Dim cServiceRequestNumber As String
Dim I As Integer
Dim findvalue


'get the select value from the listbox
For I = 0 To lbsearchsr.ListCount - 1
If lbsearchsr.Selected(I) = True Then
cServiceRequestNumber = lbsearchsr.List(I, 0)
End If
Next I

'find the service request number
Set findvalue = Sheet2.Range("B:B").Find(What:=cServiceRequestNumber, LookIn:=xlValues).Offset(0, 0)

'add the database values to the userform
cNum = 25
For X = 1 To cNum
Me.Controls("Reg" & X).Value = findvalue
Set findvalue = findvalue.Offset(0, 1)
Next

'disable adding
Me.cmdaddnewentry.Enabled = False
Me.cmdeditentry.Enabled = True

Exit Sub

End Sub

Thanks for your support

 

[cherias]

Note - The source of these codes are from - Online PC Learning by Trevor Easton

 

[cherias]

Please find the screen shot of the user form, to help you better understand the requirement.

- User form

- Database Sheet