Private Sub btnDuplicate_Click()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim bFound As Boolean
Application.ScreenUpdating = False
Application.EnableEvents = False
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
"Data Source=\\Files\Database.accdb;"
Set rst = New ADODB.Recordset
strSQL = "SELECT [ID ],[InfDate] FROM tbl WHERE [ID]=" & Me.ID.Value & " AND #" & Me.InfDate.Value & "#"
rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
bFound = Not rst.EOF
rst.Close
If bFound Then
MsgBox "duplicates found. Please enter a new value!", vbExclamation,
Me.ID.Value = ""
Me.InfDate.Value = ""
Exit Sub
End If
Me.TextBox1.Value = "" & Me.TExtBox2.Value & ". " Thankyou.
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Unless you are absolutely sure that the date in InfDate has been entered in US date format, you should format the date in the code.
I also corrected a few typos.
Private Sub btnDuplicate_Click()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim bFound As Boolean
Application.ScreenUpdating = False
Application.EnableEvents = False
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
"Data Source=\\Files\Database.accdb;"
Set rst = New ADODB.Recordset
strSQL = "SELECT [ID ],[InfDate] FROM tbl WHERE [ID]=" & Me.ID.Value & _
" AND #" & Format(Me.InfDate.Value, "mm/dd/yyyy") & "#"
rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
bFound = Not rst.EOF
rst.Close
If bFound Then
MsgBox "duplicates found. Please enter a new value!", vbExclamation
Me.ID.Value = ""
Me.InfDate.Value = ""
Exit Sub
End If
Me.TextBox1.Value = Me.TextBox2.Value & ". Thank you."
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub btnDuplicate_Click()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim bFound As Boolean
Application.ScreenUpdating = False
Application.EnableEvents = False
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
"Data Source=\\Files\Database.accdb;"
Set rst = New ADODB.Recordset
strSQL = "SELECT [ID ],[InfDate] FROM tbl WHERE [ID]=" & Me.ID.Value & _
" AND [InfDate]=#" & Format(Me.InfDate.Value, "mm/dd/yyyy") & "#"
rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
bFound = Not rst.EOF
rst.Close
If bFound Then
MsgBox "duplicates found. Please enter a new value!", vbExclamation
Me.ID.Value = ""
Me.InfDate.Value = ""
Exit Sub
End If
Me.TextBox1.Value = Me.TextBox2.Value & ". Thank you."
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub