Quote:
Originally Posted by Tempalli
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
