Delete Error when looping through and deleting recordsets

[JulieS]

Hello,

I am looping through my recordsets with two recordset pointers, and every time i come to a duplicate, i rst.delete it.
however, when the outer pointer comes upon a deleted record, i get an error saying "record has been deleted". I've tried the refresh function to just remove these records, but that does not work.

(I've also tried flagging duplicate records, but have come against too many brick walls, so i'm trying to revisit this idea).

Thank you so much from a disgruntled VBAer.

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmmm.

Just HOW closely are you related to J. Heller (Catch-22)?

I see no real way out of the circular reference w/ your approach. What I have done (and would suggest) is to let Ms. A do the hard part (finding the dups) and just use ONE loop. It goes SOMETHING like:

A find dups query (this is the 'First" recordset) Now make it Unique Records (based on Your criteria for the dups).

A simple loop. Since you KNOW the First rec set has only DUPs, use it the criteria source to locate "Dups". Depending on your data access model (RDO, DAO, ADO ... ), findfirst (DAO). Then FindNext/Delete, until FindNext fails to find one. Get next rec from dupset ...


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JulieS]

Hmmmmmmmmmmmmmmmmmmmmmmmmmmmm,

I think him and I may be cousins.

So this query business you're talking about - how would I go about coding it in VBA? (it's been awhile since i was friends with SQL).

Working with the string criteria for find first is driving me nuts. I assigned the current recordset (the one i'm comparing all the data to) to a variable, but can't seem to get the syntax right when calling find first (this always evaluates to NoMatch, and thus i end up deleting my whole table). Fantastic.

here is my syntax:

strCriteria = Me![Project Sector] = projSec And Me![Company Name] = coName

where
projSec = CurrentRST("Project Sector&quot.Value
and
coName = CurrentRST("Company Name&quot.Value

(and every time rst.MoveNext, these values are reassigned to the current record).

If this is confusing hogwash, i'll do my best to clarify.

Thanks,
Julie

 

[MichaelRed]

The SIMPLE way to make the find dupes is to use the supplied "wizzard". It WILL list all occurances of the duplicated record, at least as noted by the generated sql/query. You should make (modify) this to include ONLY the fields which you deen to denote the duplication. Then, set another query to be the UNIQUE records from the dups query. It is just "select * from dupquery" with the query property set to unique records. Try that first, it should be a list of the dups (as you defined them) - but only a single entry for each dup set.

Next, make a 'recordset of the WHOLE table - containing the dups and the unique records.

Now, build the loop. - It is going to loop through the up - unique recordset. Set the criteria from THIS recordset. Do a FindFIRST with the criteria, then do an inner 'loop' through the WHOLE table record set (Do While rstWholetable.NoMatch = False --- or your selected cvariation). It is simply a FindNEXT, Delete. When your code falls out of this, the criteria SHOULD be reset tot he next recordset values from the dupunique recordset.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over