Check for duplicate records

[Guest]

How can I check for a duplicate record from a userform? I have tried several
codes from the site, but couldn't find any that addressed multiple fields.

Example: A spreadsheet made to work/look like a form is called a transmittal
letter(DA_Form_200). More or less a cover sheet and a means to track what was
sent and when. A userform allows the user to input a short description of
each record (an Officer Evaluation Report) i.e. last, ssn, rank, startdate,
enddate. The macro in the userform places each short description onto the
spreadsheet (Page 2 of the 'DA_Form_200')in it's own row under the headers
referenced above, which also sorts by last and startdate. (makes it easier
for me and others to see who was mailed and when). At the same time the code
sends the same 'short decr records' to another spreadsheet (Date_Sent) which
just collects all the 'short descr records' so that I only have to look one
place if necessary.

When entering a 'short descr record' (from userform), how do I check for a
duplicate record (last, startdate and enddate fields) in both the 'Page 2' of
the worksheets labeled DA_Form_200 and the worksheet labeled Date_Sent and
have a message box informing me of the duplicate record ask me if I want to
proceed or not?

I hope I did not confuse anyone.

 

[Guest]

Something like this pseudo code:

Sub checkforDups()
Dim rng as Range, sAddr as String
Dim bDup as Boolean

With worksheets(2)
set rng = .columns(1).Find(Userform1.txtLast.Text, _
After:=.Range("A1"), _
Lookat:=xlWhole, _
Lookin:=xlvalues, _
SearchOrder:=xlbyRows
Searchdirection:=xlNext
MatchCase = False)
bDup = False
if not rng is nothing then
sAddr = rng.Address
do
if rng.offset(0,3) = cdate(userform1.txtStartDate) and _
rng.offset(0,4) = cdate(userform1.txtEndDate) then
bDup = true
exit do
end if
set rng = .columns(1).FindNext(rng)
Loop while rng.Address <> sAddr
if bDup then
msgbox "Dup found at " & rng.Address(external:=true)
end if
End With
end Sub

 

[Guest]

Hi Tom,

Thankyou very much for your speedy reply.
I tried using the code, and when I put it at the end of my statement, it
worked. But the end of my statement enters and stores 'short descr' records,
so I needed to put it in before the code entered and sorted the data from the
userform. After I put it before that, I kept getting this error: Object
variable or with block variable not set. I tried several different ideas but
was unsuccessful. Please help. Here is a copy of the code:

Dim rng As Range, sAddr As String
Dim bDup As Boolean
Dim lastn As String
lastn = TextBox7.Text

With Worksheets("DA_Form_200")

Set rng = .Columns(1).Find(lastn, After:=.Range("A64"), LookIn:=xlValues,
lookAt:=xlWhole, searchorder:=xlByRows, Searchdirection:=xlNext)

bDup = False
If Not rng Is Nothing Then
sAddr = rng.Address
End If

Do
If rng.Offset(0, 10) = CVar(UserForm1.TextBox10.Text) And _
rng.Offset(0, 13) = CVar(UserForm1.TextBox11.Text) Then
bDup = True
Exit Do
End If
Set rng = .Columns(1).FindNext(rng)
Loop While rng.Address <> sAddr
If bDup Then
MsgBox "Duplicate entry Found, at " & rng.Address(external:=True)
End If

End With
Exit Sub

 

[Tom Ogilvy]

You had your IF statement set up incorrectly:

Dim rng As Range, sAddr As String
Dim bDup As Boolean
Dim lastn As String
lastn = TextBox7.Text

With Worksheets("DA_Form_200")

Set rng = .Columns(1).Find(lastn, _
After:=.Range("A64"), _
LookIn:=xlValues, _
lookAt:=xlWhole, _
searchorder:=xlByRows, _
Searchdirection:=xlNext)

bDup = False
If Not rng Is Nothing Then
sAddr = rng.Address

Do
If rng.Offset(0, 10) = CVar(UserForm1.TextBox10.Text) And _
rng.Offset(0, 13) = CVar(UserForm1.TextBox11.Text) Then
bDup = True
Exit Do
End If
Set rng = .Columns(1).FindNext(rng)
Loop While rng.Address <> sAddr
end if
If bDup Then
MsgBox "Duplicate entry Found, at " & rng.Address(external:=True)
End If

End With

' this exit sub doesn't appear to be in the correct place, but I can't see
all your code
'Exit Sub