>>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.

Thanks much for your suggestion. I did just find a way to get my idea to work. I changed the "DO WHILE !FOUND( )" line to "DO WHILE EOF()" and it worked just fine. I guess VFP wasn't too fond of my attempt to use !FOUND.

Thanks again,

Mandy