>Hi all,
>
>I'm trying to search a table for 'missing' records. I want to scan through my 'agency' table for agency i.d. codes, then search another table (newinit in this case) to see if there is a record for agency code (field=acode) = the value found in the scan of the agency table.
>
>I'm using the following code to do this (VFP5.0), but I'm coming up with an endless loop in the DO WHILE section in which I'm getting 'millions' of records with acode=myscanvalue, but a new ntid (my key field formy newinit table) and I have to cancel the program.
>
>Any clue what I'm doing wrong here?
>
>*****
>USE newinit
>USE agency in 0
>SELECT agency
>SET ORDER TO acode
>GO TOP
>SCAN
> myscanvalue=alltrim(acode)
> SELECT newinit
> LOCATE FOR alltrim(acode) = myscanvalue
> DO WHILE !FOUND( )
> SET ORDER TO ntid
> GO BOTTOM
> x=ntid
> x='00'+alltrim(str(val(x)+1))
> APPEND BLANK
> REPLACE acode with myscanvalue
> REPLACE ntid with x
> ENDDO
> SELECT agency
>ENDSCAN
>********
>
>MTIA!!
>
>Mandy

Let me show you another aproach rather than look for the bug here.
try this on your own risk:

USE newinit
SET ORDER TO NTID
GO BOTTOM
LASTNTID = NTID
SET ORDER TO ACODE
*Well, index on acode if not yet done
USE agency in 0
SELECT agency
SET ORDER TO acode
SET RELATION TO ACODE INTO NEWINIT
GO TOP
SCAN FOR EOF("NEWINIT")
x=VAL(LASTNTID)
x='00'+alltrim(str(val(x)+1))
APPEND BLANK
REPLACE acode with AGENCY.ACODE
REPLACE ntid with x
LASTNTID = VAL(LASTNTID) + 1
SELECT AGENCY
ENDSCAN

The trick here is that once the relation is made, the child table goes to eof() when the parent table is on an unmatched record.

Hope this work.