Duplicates

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Duplicates

Post by adam »

Hi anyone,

I'm trying to use this code to check for duplicates with date and the value.

The code doesnt give me any error. But still it does give me the message duplicates found even though there are no duplicates in the database.


What am I doing wrong in here?

Code: Select all

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
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Duplicates

Post by HansV »

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.

Code: Select all

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
Best wishes,
Hans

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Duplicates

Post by rory »

The second field name appears to be missing from the WHERE clause too?
Regards,
Rory

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Duplicates

Post by HansV »

Ah yes - thanks, Rory!

Code: Select all

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
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Duplicates

Post by adam »

Thankyou so much for the help. I missed the second field name.
Best Regards,
Adam