Repeating Query until no Records Updated

[cdevoy]

I have a query that compares two files and makes sure that the Customer ID in my temporary import file doesn't already exist in the Master file. If it finds a duplicate, it increments the customer number by one. Obviously, this query needs to be run until Access finds no more duplicates. How can I tell the following procedure to stop once it says "about to update 0 records"?

Sub CorrectPTdups()
  Dim ReturnValue As Integer
    ' this variable means nothing '
  Do
    DoCmd.OpenQuery "IMPA8_correct_dup_customer_numbers"
  Loop Until ReturnValue = 0
     'loop until means nothing- hunting for the variable that will tell me how many records the query updated.'

End Sub

 

[PHV]

Have you tried to play with RecordSets ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gol4]

Record sets as PHV suggests would be the best method. If you don't know how to do recordsets something to consider

perhaps you can look at the dcount() function and test before you run the query

Since it sounds like this is probably an action query take a look at the recordsaffected property under access help and see if you can utilize that.

Returns the number of records affected by the most recently invoked Execute method.

Return Values

The return value is a Long from 0 to the number of records affected by the most recently invoked Execute method on either a Database or QueryDef object.

Remarks

When you use the Execute method to run an action query from a QueryDef object, the RecordsAffected property will contain the number of records deleted, updated, or inserted.

good luck

 

[cdevoy]

I will definitely look into Recordsets and the Recordsaffected property. I knew Access has to have that info stored somewhere. I'll let you know what works.