Duplicate in particular row

[Tempalli]

Iam using the below code to search the duplicate records in Ms Excel before uploading to Ms access.

It is working fine but i want the msgbox to display that there is a duplicate record in perticular row.

Kindly help how to mention the same

Ananth
------------------------------------

Set rst = DB.OpenRecordset("Claim_Status", dbOpenDynaset)
 
 myRow = ActiveCell.Row
 
If rst.RecordCount <> 0 Then
rst.FindFirst "StrInward_No = '" & RANGE("A2") & "'"
If Not rst.NoMatch Then
 
R = 2
Do While Len(RANGE("A" & R).Formula) > 0
R = R + 1 ' next row
Loop
 
MsgBox "Duplicate records found in" & myRow
 
ActiveWorkbook.Close
rst.Close
 
Set rst = Nothing
Set DB = Nothing
 
Exit Sub
End If
End If

[MikeTheBike]

Hi

There seems to be an anomily in the code as there is no way you can find a duplicate as it stands. Without knowing anything about what you are trying to do, I suggest this, pehaps, may be what you want??

Set rst = Db.OpenRecordset("Claim_Status", dbOpenDynaset)
 
myrow = 0 'set row value
 
If rst.RecordCount <> 0 Then
    rst.FindFirst "StrInward_No = '" & Range("A2") & "'"
    If Not rst.NoMatch Then
 
        R = 3 ' start on next line to original search !!
        Do While Len(Range("A" & R).Formula) > 0
            If Range("A" & R) = rst("StrInward_No") Then 'compare cell with record found ??
                myrow = R 'if found set row value
                Exit Do ' and exit do (job done)!
            End If
            R = R + 1  'next row
        Loop
 
        If myrow <> 0 Then MsgBox "Duplicate records found in ROW " & R ' ouput row if found.
 
        ActiveWorkbook.Close
        rst.Close
 
        Set rst = Nothing
        Set Db = Nothing
 
        Exit Sub
    End If
 
    Set rst = Nothing ' ??
    Set Db = Nothing ' ??
End If

HTH


MTB

[Tempalli]

Hi

There seems to be an anomily in the code as there is no way you can find a duplicate as it stands. Without knowing anything about what you are trying to do, I suggest this, pehaps, may be what you want??

Set rst = Db.OpenRecordset("Claim_Status", dbOpenDynaset)
 
myrow = 0 'set row value
 
If rst.RecordCount <> 0 Then
    rst.FindFirst "StrInward_No = '" & Range("A2") & "'"
    If Not rst.NoMatch Then
 
        R = 3 ' start on next line to original search !!
        Do While Len(Range("A" & R).Formula) > 0
            If Range("A" & R) = rst("StrInward_No") Then 'compare cell with record found ??
                myrow = R 'if found set row value
                Exit Do ' and exit do (job done)!
            End If
            R = R + 1  'next row
        Loop
 
        If myrow <> 0 Then MsgBox "Duplicate records found in ROW " & R ' ouput row if found.
 
        ActiveWorkbook.Close
        rst.Close
 
        Set rst = Nothing
        Set Db = Nothing
 
        Exit Sub
    End If
 
    Set rst = Nothing ' ??
    Set Db = Nothing ' ??
End If

HTH


MTB

I am a beginer in MS Access. As per ur code as below it is showing blank row as duplicate. i am uploading a excel sheet contains StrInward_No's as 1 2 3 4 in 4rows but the code is showing as row 5 contains duplicate. kindly help.

Set rst = DB.OpenRecordset("Claim_Status", dbOpenDynaset)
myrow = ActiveCell.Row

If rst.RecordCount <> 0 Then
R = 2 ' start on next line to original search !!
Do While Len(RANGE("A" & R).Formula) > 0
If RANGE("A" & R) = rst("StrInward_No") Then 'compare cell with record found ??
myrow = R 'if found set row value
Exit Do ' and exit do (job done)!
End If
R = R + 1 'next row
Loop

If myrow <> 0 Then MsgBox "Duplicate records found in ROW " & R ' ouput row if found.

'MsgBox "Duplicate records found in" & myrow

ActiveWorkbook.Close
rst.Close

Set rst = Nothing
Set DB = Nothing

Exit Sub
End If

[MikeTheBike]

I am a beginer in MS Access. As per ur code as below it is showing blank row as duplicate. i am uploading a excel sheet contains StrInward_No's as 1 2 3 4 in 4rows but the code is showing as row 5 contains duplicate. kindly help.

Set rst = DB.OpenRecordset("Claim_Status", dbOpenDynaset)
myrow = ActiveCell.Row

If rst.RecordCount <> 0 Then
R = 2 ' start on next line to original search !!
Do While Len(RANGE("A" & R).Formula) > 0
If RANGE("A" & R) = rst("StrInward_No") Then 'compare cell with record found ??
myrow = R 'if found set row value
Exit Do ' and exit do (job done)!
End If
R = R + 1 'next row
Loop

If myrow <> 0 Then MsgBox "Duplicate records found in ROW " & R ' ouput row if found.

'MsgBox "Duplicate records found in" & myrow

ActiveWorkbook.Close
rst.Close

Set rst = Nothing
Set DB = Nothing

Exit Sub
End If

Hi

Small floor in my code this

If myrow <> 0 Then MsgBox "Duplicate records found in ROW " & R

should have been this

If myrow <> 0 Then MsgBox "Duplicate records found in ROW " & myrow

That is the penalty of air code !

But apart from that

1) I cannot down load files (IT policy prevents this).

2) You seem to have missed the lines

rst.FindFirst "StrInward_No = '" & RANGE("A1") & "'"
If not rst.NoMatch Then


End If

etc

3) lines with 1, 2, 3, 4 in do not duplicate any data ??

4) I still think you should initialise myrow = 0 at the start of the code (what is the Active Cell at this time) !!??

BTW if the field StrInward_No is numeric then this

rst.FindFirst "StrInward_No = '" & RANGE("A1") & "'"

should be

rst.FindFirst "StrInward_No = " & RANGE("A1")


MTB