PDA

View Full Version : Checking for MULTIPLE duplicates on save_click



JaxVen
05-04-2018, 06:51 AM
Hello all

I haven't been working with VBA very long and have been searching for a way to check a table within a worksheet for duplicates on saving a form.

I have found some methods and tried them, but have been unable to figure out how to check multiple.

Basically, I need to check both text boxes within the form: Name1TEXT (first name) and Name2TEXT (surname) for duplicates, which are in columns A and B within my table (NewUserTable on Sheet7). I need to prevent the same user being entered twice and unfortunately the data must have the name separated into first name and surname (would make this a lot easier if I could have one text box for full name, but I have a LOT of data being pulled from this table elsewhere that needs it to be separate). Which of course means I have to check both name fields for duplicates, there are no other identifiers. I appreciate any help anyone can offer!

Here is my current code for the form;


Private Sub Save_Click()

'Registration details

Dim LastRow As Range
Dim UserTable As ListObject


'Add row to bottom of New user table
Sheet7.ListObjects("NewUserTable").ListRows.Add

'Enter data from form into new row at bottom
Set UserTable = Sheet7.ListObjects("NewUserTable")
Set LastRow = UserTable.ListRows(UserTable.ListRows.Count).Range

With LastRow

.Cells(1, 1) = Name1TEXT.Value
.Cells(1, 2) = Name2TEXT.Value
.Cells(1, 3) = GenderCOMBO.Value
.Cells(1, 4) = PostcodeTEXT.Value
.Cells(1, 8) = Contact1TEXT.Value
.Cells(1, 9) = Contact2TEXT.Value
.Cells(1, 10) = DateRegTEXT.Value
.Cells(1, 11) = GroupCOMBO.Value
.Cells(1, 13) = EmpCOMBO.Value
.Cells(1, 19) = MedicalDetailsTEXT.Value
.Cells(1, 20) = EthnicityCOMBO.Value
.Cells(1, 21) = ReligionCOMBO
.Cells(1, 22) = SexualityCOMBO
.Cells(1, 24) = AgeCOMBO

'result for needs 1 checkbox
If PhysDysCHECK.Value = True Then

.Cells(1, 14) = "Physical Disability"

Else

.Cells(1, 14) = "None"

End If

'result for needs 2 checkbox
If LearnDiffCHECK.Value = True Then

.Cells(1, 15) = "Learning Difficulty"

Else

.Cells(1, 15) = "None"

End If

'result for needs 3 checkbox
If MentalHCHECK.Value = True Then

.Cells(1, 16) = "Mental Health"

Else

.Cells(1, 16) = "None"

End If

'result for needs 4 checkbox
If PhysHCHECK.Value = True Then

.Cells(1, 17) = "Physical Health"

Else

.Cells(1, 17) = "None"

End If

'result for allergies checkbox
If AllergiesCHECK.Value = True Then

.Cells(1, 18) = "Yes"

Else

.Cells(1, 18) = "No"

End If

End With


End Sub

SamT
05-04-2018, 10:58 AM
I haven't worked with UserTables before, but this makes sense. Kinda.


Dim Rw As Range

For each Rw in UserTable.ListRows
If Rw .Cells(1) = Name1TEXT and .Cells(2) = Name2TEXT Then
MsgBox "Duplicate Name(s) Found in Row " & Rw.Row
End If
Next Rw

JaxVen
05-08-2018, 01:52 AM
I haven't worked with UserTables before, but this makes sense. Kinda.


Dim Rw As Range

For each Rw in UserTable.ListRows
If Rw .Cells(1) = Name1TEXT and .Cells(2) = Name2TEXT Then
MsgBox "Duplicate Name(s) Found in Row " & Rw.Row
End If
Next Rw

Thanks for reply - tried today and unfortunately doesnt work. After a little debugging I 'think' the correct code is as below but it wont work with current code. I have tried both before and after.




Dim Rw As Range


For Each Rw In NewUserTable.ListRows
If Rw.Cells(1) = Name1TEXT.Value And Rw.Cells(2) = Name2TEXT.Value Then
MsgBox "This user already exists, please use search to update their record" & Rw.Row
End If
Next Rw