By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,369 Members | 950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,369 IT Pros & Developers. It's quick & easy.

Duplicate in particular row

P: 31
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
------------------------------------
Expand|Select|Wrap|Line Numbers
  1. Set rst = DB.OpenRecordset("Claim_Status", dbOpenDynaset)
  2.  
  3. myRow = ActiveCell.Row
  4.  
  5. If rst.RecordCount <> 0 Then
  6. rst.FindFirst "StrInward_No = '" & RANGE("A2") & "'"
  7. If Not rst.NoMatch Then
  8.  
  9. R = 2
  10. Do While Len(RANGE("A" & R).Formula) > 0
  11. R = R + 1 ' next row
  12. Loop
  13.  
  14. MsgBox "Duplicate records found in" & myRow
  15.  
  16. ActiveWorkbook.Close
  17. rst.Close
  18.  
  19. Set rst = Nothing
  20. Set DB = Nothing
  21.  
  22. Exit Sub
  23. End If
  24. End If
Sep 1 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 634
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??
Expand|Select|Wrap|Line Numbers
  1. Set rst = Db.OpenRecordset("Claim_Status", dbOpenDynaset)
  2.  
  3. myrow = 0 'set row value
  4.  
  5. If rst.RecordCount <> 0 Then
  6.     rst.FindFirst "StrInward_No = '" & Range("A2") & "'"
  7.     If Not rst.NoMatch Then
  8.  
  9.         R = 3 ' start on next line to original search !!
  10.         Do While Len(Range("A" & R).Formula) > 0
  11.             If Range("A" & R) = rst("StrInward_No") Then 'compare cell with record found ??
  12.                 myrow = R 'if found set row value
  13.                 Exit Do ' and exit do (job done)!
  14.             End If
  15.             R = R + 1  'next row
  16.         Loop
  17.  
  18.         If myrow <> 0 Then MsgBox "Duplicate records found in ROW " & R ' ouput row if found.
  19.  
  20.         ActiveWorkbook.Close
  21.         rst.Close
  22.  
  23.         Set rst = Nothing
  24.         Set Db = Nothing
  25.  
  26.         Exit Sub
  27.     End If
  28.  
  29.     Set rst = Nothing ' ??
  30.     Set Db = Nothing ' ??
  31. End If
HTH


MTB
Sep 1 '08 #2

P: 31
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??
Expand|Select|Wrap|Line Numbers
  1. Set rst = Db.OpenRecordset("Claim_Status", dbOpenDynaset)
  2.  
  3. myrow = 0 'set row value
  4.  
  5. If rst.RecordCount <> 0 Then
  6.     rst.FindFirst "StrInward_No = '" & Range("A2") & "'"
  7.     If Not rst.NoMatch Then
  8.  
  9.         R = 3 ' start on next line to original search !!
  10.         Do While Len(Range("A" & R).Formula) > 0
  11.             If Range("A" & R) = rst("StrInward_No") Then 'compare cell with record found ??
  12.                 myrow = R 'if found set row value
  13.                 Exit Do ' and exit do (job done)!
  14.             End If
  15.             R = R + 1  'next row
  16.         Loop
  17.  
  18.         If myrow <> 0 Then MsgBox "Duplicate records found in ROW " & R ' ouput row if found.
  19.  
  20.         ActiveWorkbook.Close
  21.         rst.Close
  22.  
  23.         Set rst = Nothing
  24.         Set Db = Nothing
  25.  
  26.         Exit Sub
  27.     End If
  28.  
  29.     Set rst = Nothing ' ??
  30.     Set Db = Nothing ' ??
  31. 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
Sep 2 '08 #3

Expert 100+
P: 634
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
Sep 2 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.