How do you loop through a recordset to find duplicates 1

[Rousseau10]

I am a newbee. I need to loop through the a recordset to find duplicates value in a field.
So I would need a nested loop correct. 1 loop to go through records set to get the control field value and a nested loop to go through recordset to compare the fields value to the control field value.
If this is true do I need to dimension to recordsets rs1 and rs2. My thinking for needing 2 recordsets is becuase the the first recordset needs to go through the records sequentially and the seconds(compare) recordset needs to alway start at the first record each time it loops.

Is my logic correct here?

And now is a good time to ask, how do I get the record position and tell a loop to start at that position?

thanks

 

[RoyVidar]

In the query tab, select the New button, then "Find duplicates query wizard". It will quide you through the process. Now, if you'd like programatic access to that query afterwards, just open a recordset based on it (if you've saved, that is).

Roy-Vidar

 

[Rousseau10]

thank you! I will do this and also though I am curious about my nested loop logic for future looping for recordset needs!!!!

 

[RoyVidar]

He he - recordsets are fascinating - just keep in mind that whatever you can perform through a query, will in almost every case outperform recordset approaches.

In a case of searching a recordset for dupes, I'd sort the recordset on the field in question, I think, then counted how many consecutive records are equal

[tt]dim rs as dao.recordset
dim strstring as string
dim lngcount as long
set rs=currentdb.openrecordset("select somefield " & _
"from sometable order by somefield")
strstring=rs.fields("somefield").value
rs.movenext
lngcount=1
do while not rs.eof
if strstring=rs.fields("somefield").value then
lngcount=lngcount+1
else
debug.print strstring & " occurred " & lngcount & " times"
lngcount=1
strstring=rs.fields("somefield").value
end if
rs.movenext
loop[/tt]

- hit ctrl+g to view results, air code, no testing, just typing, no errortesting...

Roy-Vidar

 

[Rousseau10]

thanks!!! I will try!