Duplicate validation stopping Modify

[Babynod]

Hi All,
on my Login Register my modify was working fine, until i got my duplicate validation working. now when i try to modify (edit) its registering it as a duplicate and it wont allow me to do it.

Username is my unique column which the validation looks at.
e.g employee "marpol" currently has a login with a status of "Enabled" (status is a combobox)
employee no longer needs login so the status needs to be changed to "disabled"

this is my validate code

Function ValidateEntries() As Boolean

ValidateEntries = True

Dim iUsername As Variant

Dim Sh As Worksheet

Set Sh = ThisWorkbook.Sheets("DATABASE")

iUsername = frmFORM.txtUser.Value

With frmFORM

'Default Color

.txtLast.BackColor = vbWhite
.txtFirst.BackColor = vbWhite
.txtUser.BackColor = vbWhite
.cmbStatus.BackColor = vbWhite
.cmbZone.BackColor = vbWhite
.cmbRole.BackColor = vbWhite
.txtPhone.BackColor = vbWhite
.txtPrimaryEmail.BackColor = vbWhite
.txtLocation.BackColor = vbWhite
.cmbRequestedBy.BackColor = vbWhite
'--------------------------------

If Trim(.txtLast.Value) = "" Then

MsgBox "Please enter Last Name.", vbOKOnly + vbInformation, "Last Name"
ValidateEntries = False
.txtLast.BackColor = vbRed
.txtLast.SetFocus
Exit Function

End If

'Validating Duplicate Entries

If Not Sh.Range("D:D").Find(what:=iUsername, lookat:=xlWhole) Is Nothing Then

MsgBox "Duplicate Username found.", vbOKOnly + vbInformation, "Userame"
ValidateEntries = False
.txtUser.BackColor = vbRed
.txtUser.SetFocus
Exit Function

End If

If Trim(.txtFirst.Value) = "" Then

MsgBox "Please enter First Name.", vbOKOnly + vbInformation, "First Name"
ValidateEntries = False
.txtFirst.BackColor = vbRed
.txtFirst.SetFocus
Exit Function

End If

If Trim(.txtUser.Value) = "" Then

MsgBox "Please enter Username.", vbOKOnly + vbInformation, "Username"
ValidateEntries = False
.txtUser.BackColor = vbRed
.txtUser.SetFocus
Exit Function

End If


If Trim(.cmbStatus.Value) = "" Then

MsgBox "Please select Status from drop-down.", vbOKOnly + vbInformation, "Status"
ValidateEntries = False
.cmbStatus.BackColor = vbRed
.cmbStatus.SetFocus
Exit Function

End If

If Trim(.cmbZone.Value) = "" Then

MsgBox "Please select Zone from drop-down.", vbOKOnly + vbInformation, "Zone"
ValidateEntries = False
.cmbZone.BackColor = vbRed
.cmbZone.SetFocus
Exit Function

End If

If Trim(.cmbRole.Value) = "" Then

MsgBox "Please select Role from drop-down.", vbOKOnly + vbInformation, "Role"
ValidateEntries = False
.cmbRole.BackColor = vbRed
.cmbRole.SetFocus
Exit Function

End If


If Trim(.txtPhone.Value) = "" Then

MsgBox "Please enter a Phone Name.", vbOKOnly + vbInformation, "Phone"
ValidateEntries = False
.txtPhone.BackColor = vbRed
.txtPhone.SetFocus
Exit Function

End If

If Trim(.txtPrimaryEmail.Value) = "" Then

MsgBox "Please enter a Primary Email Address.", vbOKOnly + vbInformation, "PrimaryEmail"
ValidateEntries = False
.txtPrimaryEmail.BackColor = vbRed
.txtPrimaryEmail.SetFocus
Exit Function

End If

If Trim(.txtLocation.Value) = "" Then

MsgBox "Please enter a Location.", vbOKOnly + vbInformation, "Location"
ValidateEntries = False
.txtLocation.BackColor = vbRed
.txtLocation.SetFocus
Exit Function

End If

If Trim(.cmbRequestedBy.Value) = "" Then

MsgBox "Please select Requested By from drop-down.", vbOKOnly + vbInformation, "RequestedBy"
ValidateEntries = False
.cmbRequestedBy.BackColor = vbRed
.cmbRequestedBy.SetFocus
Exit Function

End If



End With



End Function

 

[HaHoBe]

Hi,

maybe try and change codeline

If Not Sh.Range("D:D").Find(what:=iUsername, lookat:=xlWhole) Is Nothing Then

to

If WorksheetFunction.CountIf(Sh.Range("D:D"), iUsername) > 0 Then

From my point I would change the boolean ValidateEntries to False at the start, eliminate all lines which do this for a failing check and add ValidateEntries = True after all checks have been made.

Just being curious: why do you declare iUsername as Variant? Coming from a Textbox it should be a String from my understanding.

Ciao,
Holger

 

[Babynod]

Hi,

maybe try and change codeline

If Not Sh.Range("D:D").Find(what:=iUsername, lookat:=xlWhole) Is Nothing Then

to

If WorksheetFunction.CountIf(Sh.Range("D:D"), iUsername) > 0 Then

From my point I would change the boolean ValidateEntries to False at the start, eliminate all lines which do this for a failing check and add ValidateEntries = True after all checks have been made.

Just being curious: why do you declare iUsername as Variant? Coming from a Textbox it should be a String from my understanding.

Ciao,
Holger

Hi HaHoBe,
ive been learning VBA for about a week using thedatalabs videos, some im super new.
if you have a better way to do it im all ears.

 

[Babynod]

Hi,

maybe try and change codeline

If Not Sh.Range("D:D").Find(what:=iUsername, lookat:=xlWhole) Is Nothing Then

to

If WorksheetFunction.CountIf(Sh.Range("D:D"), iUsername) > 0 Then

From my point I would change the boolean ValidateEntries to False at the start, eliminate all lines which do this for a failing check and add ValidateEntries = True after all checks have been made.

Just being curious: why do you declare iUsername as Variant? Coming from a Textbox it should be a String from my understanding.

Ciao,
Holger

tried
If WorksheetFunction.CountIf(Sh.Range("D:D"), iUsername) > 0 Then

but still no difference

 

[HaHoBe]

Hi,

where and how do you apply the function? The line by itself should work flawleslly (at least it does in my sample all by itself).

A different apporach may be using Arrays. When you loop through the array you could save a lot code lines which differ only from the name of object checked and the message (except for the check for duplicate).

Ciao,
Holger

 

[Babynod]

Hi,

where and how do you apply the function? The line by itself should work flawleslly (at least it does in my sample all by itself).

A different apporach may be using Arrays. When you loop through the array you could save a lot code lines which differ only from the name of object checked and the message (except for the check for duplicate).

Ciao,
Holger

Would it be easier if you had the file?

 

[Babynod]

Hi,

where and how do you apply the function? The line by itself should work flawleslly (at least it does in my sample all by itself).

A different apporach may be using Arrays. When you loop through the array you could save a lot code lines which differ only from the name of object checked and the message (except for the check for duplicate).

Ciao,
Holger

It in the module. I have a userform with a list listbox. Select the record from the listbox and press "edit" and it puts the text into the fields. Then you make your changes and hit "submit" to save the changes and update the listbox/database sheet.

I'm not sure what arrays are, they weren't in the video I was learning from

 

[HaHoBe]

Hi,

Would it be easier if you had the file?

It seems I´m unable to spot what´s wrong, so yes, it should help unless somebody else comes up with the solution.

Ciao,
Holger

 

[Babynod]

Hi,



It seems I´m unable to spot what´s wrong, so yes, it should help unless somebody else comes up with the solution.

Ciao,
Holger

 

[Babynod]

WMS Login Register copy.xlsm.xlsx

Microsoft Excel Workbook

1drv.ms

does this link work? Its on onedrive