Avoid duplicate entry

[DharmeshKP]

I have userform for patient data entry. I wants to avoid duplicate entry with a condition that it should not be duplicate on current date. Patient may come after few days and needs to be reentered but it should not happen on same day means on curren date. I tried following code but it shows error. Please help me to debug it.

Private Sub CommandButton2_Click()
Dim x As Long
Dim y As Worksheet
Dim c As Range
Set y = Sheets("Daily")
x = y.Range("A" & Rows.Count).End(xlUp).Row
lrow = y.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
FindDate = Format(Date, "dd/mm/yyyy")
' find first row containing current date
[B]frow = y.Cells.Find(What:=FindDate, SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
'error line in above[/B]

With y.Range(y.Cells(frow, 1), y.Cells(lrow, 1))
Set c = .Find(Me.TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
End With

If c Is Nothing Then
With y
.Cells(x + 1, "A").Value = TextBox1.Text
.Cells(x + 1, "B").Value = TextBox2.Text
.Cells(x + 1, "C").Value = TextBox3.Text
.Cells(x + 1, "D").Value = TextBox4.Text
.Cells(x + 1, "E").Value = TextBox5.Text
.Cells(x + 1, "F").Value = TextBox6.Text
.Cells(x + 1, "G").Value = TextBox7.Text
.Cells(x + 1, "H").Value = TextBox8.Text
.Cells(x + 1, "I").Value = [now()]
.Cells(x + 1, "J").Value = Application.UserName
End With
'clear the data
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
ActiveWorkbook.Save
Else: MsgBox TextBox1.Value & " is already registered "
Exit Sub
End If
End Sub

 

[DharmeshKP]

Wonderfull
Now it worked fine
No error
I am very thankfull to you yinkajewole

 

[DharmeshKP]

wonderfull
That worked perfectly
Thanks a lot

 

[yinkajewole]

You are most welcome!

 

[DharmeshKP]

Now I have another similar problem
cmd3 is to modify record
Here I wants to find today's entry
If it is present I wish to modify it
Below code show erro "next without for"
Please help me to sort out

Private Sub CommandButton3_Click()
Dim password As Variant

password = Application.InputBox("Please Enter Password", "Password Protected Macro")

Select Case password

Case Is = False

    'do nothing

Case Is = "1"
Dim x As Long
Dim y As Worksheet
Dim c As Range
Dim TDate As Long

Set y = Sheets("Daily")
x = y.Range("A" & Rows.Count).End(xlUp).Row
lrow = y.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
For i = 1 To lrow
'search for today's date
If InStr(Format(y.Cells(i, "I"), "dd/mm/yyyy"), Format(Date, "dd/mm/yyyy")) Then
TDate = i:
Exit For
Next i
'error in above line
If TDate <> 0 Then
For j = TDate To lrow
'search name starting from today's date row
If Me.TextBox1.Value = y.Cells(j, 1) Then
Next j

With y
y.Cells(y, 5).Value = TextBox5.Text
y.Cells(y, 6).Value = TextBox7.Text
y.Cells(y, 7).Value = TextBox8.Text
y.Cells(y, 8).Value = TextBox9.Text
End With
'clear the data
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
ActiveWorkbook.Save

End If

Case Else

MsgBox "The password you entered was incorrect"

End Select
End Sub

 

[yinkajewole]

Private Sub CommandButton3_Click()
Dim password As Variant

password = Application.InputBox("Please Enter Password", "Password Protected Macro")

Select Case password

Case Is = False

    'do nothing

Case Is = "1"
Dim x As Long
Dim y As Worksheet
Dim c As Range
Dim TDate As Long
Dim zN As Long

Set y = Sheets("Daily")
x = y.Range("A" & Rows.Count).End(xlUp).Row
lrow = y.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
For i = 1 To lrow
'search for today's date
If InStr(Format(y.Cells(i, "I"), "dd/mm/yyyy"), Format(Date, "dd/mm/yyyy")) Then TDate = i: Exit For
Next i
'error in above line
If TDate > 0 Then
For j = TDate To lrow
'search name starting from today's date row
If Me.TextBox1.Value = y.Cells(j, 1) Then zN = j: Exit For
Next j
End If
If zN > 0 Then 'if name found then
With y
y.Cells(zN, 5).Value = TextBox5.Text
y.Cells(zN, 6).Value = TextBox7.Text
y.Cells(zN, 7).Value = TextBox8.Text
y.Cells(zN, 8).Value = TextBox9.Text
End With
'clear the data
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
ActiveWorkbook.Save
Else
MsgBox TextBox1.Value & " was not registered "
End If

Case Else

MsgBox "The password you entered was incorrect"

End Select
End Sub

 

[mole999]

Now I have another similar problem
cmd3 is to modify record
Here I wants to find today's entry
If it is present I wish to modify it
Below code show erro "next without for"
Please help me to sort out

Private Sub CommandButton3_Click()
Dim password As Variant

password = Application.InputBox("Please Enter Password", "Password Protected Macro")

Select Case password

Case Is = False

    'do nothing

Case Is = "1"
Dim x As Long
Dim y As Worksheet
Dim c As Range
Dim TDate As Long

Set y = Sheets("Daily")
x = y.Range("A" & Rows.Count).End(xlUp).Row
lrow = y.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
For i = 1 To lrow
'search for today's date
If InStr(Format(y.Cells(i, "I"), "dd/mm/yyyy"), Format(Date, "dd/mm/yyyy")) Then
TDate = i:
Exit For
Next i
'error in above line
If TDate <> 0 Then
For j = TDate To lrow
'search name starting from today's date row
If Me.TextBox1.Value = y.Cells(j, 1) Then
Next j

With y
y.Cells(y, 5).Value = TextBox5.Text
y.Cells(y, 6).Value = TextBox7.Text
y.Cells(y, 7).Value = TextBox8.Text
y.Cells(y, 8).Value = TextBox9.Text
End With
'clear the data
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
ActiveWorkbook.Save

End If

Case Else

MsgBox "The password you entered was incorrect"

End Select
End Sub

@DharmeshKP We discourage keep using the same thread for new issues, as each issue is resolved, new items should be then started in a new thread. Your first being the avoid duplicates on the same day, and now record modification. Please reacquaint yourself with the forum rules