Scan table proc fails after adding a table

Thanks Olaf.

It seems NOW that the problem is not the table and the code that I added.   And sometimes the error occurs either way.

I moved the command SELECT PRACTICES out of the last IF block to just before the ENDSCAN.  That seems to be working NOW.  

LOCATE for (descriptn = "-" and not descriptn = "--  Cur") or ;
 .........
 ...........

IF FOUND()

   blah blah
   blah
    **SELECT PRACTICES ... moved

 ENDIF
 
SELECT   && moved to here
 
endscan

But if this is the fix, I don't know why this program is currently working in production, unless records are always returned from that LOCATE and it falls into the IF block every time and hitting that SELECT PRACTICES command every time is keeping it from falling out.

That could very well be the case ... (I did not code this mess, but I'm having to run it. Just trying modify it so it doesn't require constant monitoring and intervention).

I am still testing to see if it breaks moving the SELECT PRACTICES command ... and I am still looking for the Reader's Digest Version on how to use the debugger!

Moving the SELECT PRACTICES command seems to have fixed it.  

Acclimating myself to the debugger helped as well.

Thanks Olaf!

Thanks Olaf.

 You gave me a lot to digest.  I'll try to work thru your suggestions.

I realize now that SCAN does not need to SELECT PRACTICES the second time.  

I commented out "USE PRACTICES" at the end of the SCAN loop and the code is running fine AGAIN NOW without it.  So I am totally baffled now.

My background is in SQL, C# and VB.net so it doesn't take much to baffle me when it comes to how Foxpro Implements database functionality.

How do you close a single table without closing ALL OF THEM, if the use does close all of them?

The original code that I am modifying is written my someone else.  My modifications include only what pertains to the ckBreedsScrub table.

There are several stand-alone USE statements within the SCAN loop, that if I understand your post correctly, should close all tables, including the Practices table.  But it seems if does not.

-----------------------------------------------------------------------------------------------------------------------------
---------- You can disregard the following part if you want.  I will post another question ---------------
-----------------------------------------------------------------------------------------------------------------------------
On another note, related to this program,  I am appending into the ckBreedsScrub table into the lcPullFileNm table.  The PullFile table does not have a field or combination of fields that uniquely identify the record.  

I will later need to go back and update lcPullFileTableNm from the ckBreedsScrub table.  So I need to generate  a field to add to lcPullfileNme to uniquely identify each row.  

The only thing I can come up with is to use a counter in the scan loop and insert that value into both tables.  What is a better way to do this?
---------------------------------------------------------------------------------------------------------------

The code in entirety:
***  CkBreedsReminders.prg
*//lparameter lnWeekNum
close datab all

local lcWeekPullFile, ;
      lcWeekPullFileNam, ;
      ldReportDate, ;
      lnWeekNum, ;
      lcMailProof, ;
      lcMailProofNam, ;
      lnKtr_read ;
      lnctr;
      lcScrub;
      
lnKtr_read = 0

set safety off
set date to YMD

PrintToTextFile = "Y"
if PrintToTextFile = "Y"
      set printer to logprint
         SET EXCLUSIVE OFF
        SET PRINTER TO c:\vetdata\_utility_reports\CkbreedsReminders.txt
    SET PRINTER ON
endif

ldReportDate = date() - 0

if empty(lnWeekNum)
      lnWeekNum = 15
endif

lcScrub = "Y"
if empty(lcScrub)
      lcScrub = "N"
endif

lcWeekNum = "9" + alltrim(right(str(100 + lnWeekNum),2))

*!* Log **********************************************************************
fpath = "c:\Vetdata\_Utility_Reports\WK" + lcWeekNum + "\"

if directory(fpath)=.F.
mkdir &fpath
endif

lnCnt = 1
lcDte = DTOS(date())
lcPrg = alltrim(Program())
lcFullPath = fpath + lcprg + "_" + lcDte + "_" + alltrim(Str(lnCnt)) + ".txt"

if file(lcFullPath)
do while file(lcFullPath)
lnCnt = lnCnt + 1
lcFullPath = fpath + lcprg + "_" + lcDte + "_" + alltrim(Str(lnCnt)) + ".txt"
loop
enddo
endif

set default to "c:\vetdata"

lnHandle = FCREATE(lcFullPath)

FPUTS(lnHandle, "PracticeID,Pracabbrev,Cnt,DefaultDir,RecNo,StartTime,EndTime")
*!*******************************************************************************

      USE c:\vetdata\templates\ckBreedScrub.dbf
      select ckBreedScrub
      COPY STRUCTURE TO c:\vetdata\ckBreedScrub.dbf
      use
      USE c:\vetdata\ckBreedScrub.dbf
      select ckBreedScrub

use c:\vetdata\breedlist.dbf in 0 shared
USE c:\vetdata\breedlist.dbf IN 0 AGAIN ALIAS Breedlist_a SHARED
use c:\vetdata\remindlabels.dbf in 0 shared
use c:\vetdata\remindlabels.dbf IN 0 AGAIN ALIAS Remindlabels_a SHARED
use c:\vetdata\practices.dbf in 0 exclu
use c:\vetdata\breedmaster2.dbf in 0 exclu
use c:\vetdata\breedmaster2.dbf in 0 AGAIN ALIAS Breedmaster2_a SHARED
select breedlist_a
set order to tag ubreedd
brow last for len(alltrim(breed1))>3 nowait
select breedmaster2
set order to tag ubreedd
brow last nowait
select practices
****************     SET PARAMETERS TWICE, once for CkBreeds, once for Reminder labels

?
? "Check Breeds - Week#", lcWeekNum, datetime()
?

lnctr = 0
scan

      lnctr = lnctr + 1

*!*      *!*       All Practice IDs

      if inlist(prod,"P1","P2","P0","P5") and ;
      (not upper(loop) = "C1") AND (not inlist(pracabbrev,"KEL","VIT","JBR"))
      

      ldStrtPrac = datetime()
      lcPracID = alltrim(practices.practiceid)
      lcAbbrev = alltrim(practices.pracabbrev)
      lcDir = alltrim(practices.defaultdir)
      
      lcWeekPullFile = "pull\wk" + lcWeekNum + "_pull.dbf"
      lcWeekPullFileNam = "wk" + lcWeekNum + "_pull"
      lcMailProof = "pull\mailproof.dbf"
      lcMailProofNam = "mailproof"

      lcWeekPullFile = "pull\wk" + lcWeekNum + "_pull.dbf"
      lcWeekPullFileNam = "wk" + lcWeekNum + "_pull"

      set default to "c:\vetdata\" + practices.defaultdir
            
      lnKtr_read = lnKtr_read + 1
      ? lcAbbrev, lnKtr_read, lcDir, ;
     "Prod/Loop/Rot/Sync/FPDate: ", ;
      practices.prod, ;
      practices.loop, ;
      practices.rotation, ;
      practices.fpdate  
 
      set deleted on
      USE (lcWeekPullFile) IN 0 EXCLUSIVE
      select breedmaster2
      set order to tag ubreed3
      select (lcWeekPullFileNam)
      

        Replace all breed1 with "D-MIX" for empty(breed) and empty(breedd) and ;
           (inlist(upper(species),"CANIN","DOG","K9","K-9") or ;
            inlist(upper(speciesd),"CANIN","DOG","K9","K-9"))
        Replace all breed1 with "C-MIX" for empty(breed) and empty(breedd) and ;
           (inlist(upper(species),"FELIN","CAT") or ;
            inlist(upper(speciesd),"FELIN","CAT"))
      
      index on upper(breedd) tag ubreedd
      index on upper(substr(breedd,1,3)) tag ubreed3
      index on upper(substr(breed,1,3)) tag ubr3
      set order to
      set relation to upper(substr(breedd,1,3)) into breedmaster2 additive
      set relation to upper(substr(breed,1,3)) into breedlist_a additive
      select (lcWeekPullFileNam)
          && ERIC
          replace all breed1 with "D-MIX",image with "indivD-MIX.jpg",breeimgcod with "D-MIX-ADLT-H.jpg" ;
          for empty(breed1) and empty(Breedd)and Speciesd = "Canine"
            replace all breed1 with "C-MIX",image with "indivC-MIX.jpg",breeimgcod with "C-MIX-ADLT-H.jpg" ;
            for empty(breed1) and empty(Breedd)and Speciesd = "Feline"
            && ERIC
      
      locate  for isdigit(substr(breed1,3,1)) or isdigit(substr(breed1,4,1)) or ;
                isdigit(substr(breed1,5,1)) or empty(breed1)
      if found()
          index on breedd tag breedd
          
            *** BREEDS THAT NEED TO BE FIXED
          replace all verifyimg with "a" for isdigit(substr(breed1,3,1)) or isdigit(substr(breed1,4,1)) or ;
                isdigit(substr(breed1,5,1)) or empty(breed1)
                
          if lcScrub = "N"      
                browse for verifyimg = "a" fields species,speciesd, breed, breedd, breed1 nowait
                  browse last for verifyimg = "a" fields species,speciesd, breed, breedd, breed1 nowait
            
                  errchk   *** BREEDS THAT NEED TO BE FIXED
            else
                  select ckBreedScrub
                  APPEND FROM (lcWeekPullFile) for verifyimg = "a"
            
            endif
            
            Select (lcWeekPullFileNam)
            
            replace all breeimgcod with alltrim(breed1) + "-ADLT-H.jpg" for not len(alltrim(breed1))<4
            replace all image with "indiv" + alltrim(breed1) + ".jpg" for image = "indiv" or empty(image)
          select species,speciesd,breed,breedd,breed1,breeimgcod as cubreedimg from (lcWeekPullFileNam);
             where verifyimg = "a" group by breedd into table pull\addedbreeds.dbf
            
          use
          
          select breedmaster2
          append from pull\addedbreeds.dbf
      endif

      select (lcWeekPullFileNam)
      
      use
            
  endif
 

  *select practices
 
 
endscan      



lnKtr_read = 0
?
? "Reminder Labels - Week#", lcWeekNum, datetime()


scan  
*
if inlist(prod,"P1","P2","P0","P5") and ;
      (not upper(loop) = "C1") and (not inlist(pracabbrev,"VIT"))

      lcAbbrev = practices.pracabbrev

      lcWeekPullFile = "pull\wk" + lcWeekNum + "_pull.dbf"
      lcWeekPullFileNam = "wk" + lcWeekNum + "_pull"
      lcMailProof = "pull\mailproof.dbf"
      lcMailProofNam = "mailproof"


      set default to "c:\vetdata\" + practices.defaultdir
      *//set default to "c:\vetdata\" + "practices"

      lnKtr_read = lnKtr_read + 1
      ? lcAbbrev, lnKtr_read, practices.defaultdir, ;
     "Prod/Loop/Rot/Sync/FPDate: ", ;
      practices.prod, ;
      practices.loop, ;
      practices.rotation, ;
      practices.sync, ;
      practices.fpdate  
            
      USE (lcWeekPullFile) IN 0 EXCLUSIVE
      select (lcWeekPullFileNam)


      if practices.loop <> "C"

            use pull\mailproof.dbf in 0 exclu

      endif

    select (lcWeekPullFileNam)
   
    locate for (descriptn = "XXX") or ;
    (descrip2 = "XXX") or ;
    (descrip3 = "XXX") or ;
    (descrip4 = "XXX") or ;
    (descrip5 = "XXX") or ;
    (descrip6 = "XXX") or ;
    (descrip7 = "XXX") or ;
    (descrip8 = "XXX")
   
      if found()
      
                 replace all verifyimg with "dx" ;
                 for (descriptn = "XXX") or ;
                (descrip2 = "XXX") or ;
                (descrip3 = "XXX") or ;
                (descrip4 = "XXX") or ;
                (descrip5 = "XXX") or ;
                (descrip6 = "XXX") or ;
                (descrip7 = "XXX") or ;
                (descrip8 = "XXX")
*!*                      nowait
*!*                        errchk

            select ckBreedScrub
                  APPEND FROM (lcWeekPullFile) for verifyimg = "dx"
                select ckBreedScrub
            
                  select (lcWeekPullFileNam)
                              
             
      endif
      
     
    locate for (descriptn = "-" and not descriptn = "--  Cur") or ;
    (descrip2 = "--" and not descrip2 = "--  Cur") or ;
    (descrip3 = "--" and not descrip3 = "--  Cur") or ;
    (descrip4 = "--" and not descrip4 = "--  Cur") or ;
    (descrip5 = "--" and not descrip5 = "--  Cur") or ;
    (descrip6 = "--" and not descrip6 = "--  Cur") or ;
    (descrip7 = "--" and not descrip7 = "--  Cur") or ;
    (descrip8 = "--" and not descrip8 = "--  Cur")
   
   
      if found()
                  
            ***Eric
            select (lcWeekPullFileNam)
            SET ORDER TO TAG Descriptn OF c:\vetdata\remindlabels.cdx IN Remindlabels
            SET RELATION TO substr(descriptn,3,len(descriptn)-2) INTO Remindlabels ADDITIVE
            
            *!*Eric
            USE c:\vetdata\remindlabels_um.dbf IN 0 EXCLUSIV
            select remindlabels_um
            set order to tag descriptn
            select (lcWeekPullFileNam)
            SET RELATION TO descriptn INTO Remindlabels_um ADDITIVE

            **lcDir = "work" &&alltrim(practices.DefaultDir)
            lcDir = alltrim(practices.DefaultDir)

            select Practiceid, Pracabbrev,practices.DefaultDir as Defaultdir, lcWeekNum as Wknum, ppmsid,;
            Descriptn,Descrip2,Descrip3,Descrip4,Descrip5,Descrip6,Descrip7,Descrip8 from (lcWeekPullFileNam) ;
            into table "c:\vetdata\" + lcDir + "\pull\remindlabels_um" + lcWeekNum + ".dbf" ;
            where (descriptn = "-" and not descriptn = "--  Cur") or ;
                (descrip2 = "--" and not descrip2 = "--  Cur") or ;
                (descrip3 = "--" and not descrip3 = "--  Cur") or ;
                (descrip4 = "--" and not descrip4 = "--  Cur") or ;
                (descrip5 = "--" and not descrip5 = "--  Cur") or ;
                (descrip6 = "--" and not descrip6 = "--  Cur") or ;
                (descrip7 = "--" and not descrip7 = "--  Cur") or ;
                (descrip8 = "--" and not descrip8 = "--  Cur")

               
            select "remindlabels_um" + lcWeekNum + ".dbf"

            lcPracID = practices.practiceid


            select lcPracID as Practiceid, lcAbbrev as Pracabbrev,lcDir as Defaultdir,  lcWeekNum as Wknum, ppmsid, ;
            Descriptn,"" as Descrip, "" as Label, "" as datecreate,datetime() as Insertstamp ;
            from "c:\vetdata\" + lcDir + "\pull\remindlabels_um" + lcWeekNum + ".dbf" ;
            into table "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf" ;
            where (descriptn = "-" and not descriptn = "--  Cur")
            select remindlabels_um
            append from "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf"


            select lcPracID as Practiceid, lcAbbrev as Pracabbrev,lcDir as Defaultdir,  lcWeekNum as Wknum, ppmsid, ;
            Descrip2 as Descriptn, "" as Descrip, "" as Label, "" as datecreate,datetime() as Insertstamp ;
            from "c:\vetdata\" + lcDir + "\pull\remindlabels_um" + lcWeekNum + ".dbf" ;
            into table "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf" ;
            where (descrip2 = "-" and not descrip2 = "--  Cur")
            select remindlabels_um
            append from "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf"

            select lcPracID as Practiceid, lcAbbrev as Pracabbrev,lcDir as Defaultdir,  lcWeekNum as Wknum, ppmsid, ;
            Descrip3 as Descriptn, "" as Descrip, "" as Label, "" as datecreate,datetime() as Insertstamp ;
            from "c:\vetdata\" + lcDir + "\pull\remindlabels_um" + lcWeekNum + ".dbf" ;
            into table "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf" ;
            where (descrip3 = "-" and not descrip3 = "--  Cur")
            select remindlabels_um
            append from "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf"

            select lcPracID as Practiceid, lcAbbrev as Pracabbrev,lcDir as Defaultdir,  lcWeekNum as Wknum, ppmsid, ;
            Descrip4 as Descriptn, "" as Descrip, "" as Label, "" as datecreate,datetime() as Insertstamp ;
            from "c:\vetdata\" + lcDir + "\pull\remindlabels_um" + lcWeekNum + ".dbf" ;
            into table "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf" ;
            where (descrip4 = "-" and not descrip4 = "--  Cur")
            select remindlabels_um
            append from "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf"

            select lcPracID as Practiceid, lcAbbrev as Pracabbrev,lcDir as Defaultdir,  lcWeekNum as Wknum, ppmsid, ;
            Descrip5 as Descriptn, "" as Descrip, "" as Label, "" as datecreate,datetime() as Insertstamp ;
            from "c:\vetdata\" + lcDir + "\pull\remindlabels_um" + lcWeekNum + ".dbf" ;
            into table "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf" ;
            where (descrip5= "-" and not descrip5 = "--  Cur")
            select remindlabels_um
            append from "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf"

            select lcPracID as Practiceid, lcAbbrev as Pracabbrev,lcDir as Defaultdir,  lcWeekNum as Wknum, ppmsid, ;
            Descrip6 as Descriptn, "" as Descrip, "" as Label, "" as datecreate,datetime() as Insertstamp ;
            from "c:\vetdata\" + lcDir + "\pull\remindlabels_um" + lcWeekNum + ".dbf" ;
            into table "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf" ;
            where (descrip6 = "-" and not descrip6 = "--  Cur")
            select remindlabels_um
            append from "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf"

            select lcPracID as Practiceid, lcAbbrev as Pracabbrev,lcDir as Defaultdir,  lcWeekNum as Wknum, ppmsid, ;
            Descrip7 as Descriptn, "" as Descrip, "" as Label, "" as datecreate,datetime() as Insertstamp ;
            from "c:\vetdata\" + lcDir + "\pull\remindlabels_um" + lcWeekNum + ".dbf" ;
            into table "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf" ;
            where (descrip7 = "-" and not descrip7 = "--  Cur")
            select remindlabels_um
            append from "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf"

            select lcPracID as Practiceid, lcAbbrev as Pracabbrev,lcDir as Defaultdir,  lcWeekNum as Wknum, ppmsid, ;
            Descrip8 as Descriptn, "" as Descrip, "" as Label, "" as datecreate,datetime() as Insertstamp ;
            from "c:\vetdata\" + lcDir + "\pull\remindlabels_um" + lcWeekNum + ".dbf" ;
            into table "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf" ;
            where (descrip8 = "-" and not descrip8 = "--  Cur")

            select remindlabels_um
            append from "c:\vetdata\" + lcDir + "\pull\remindlabels_temp.dbf"

            select pracabbrev,defaultdir,descriptn,count(*)as cnt from remindlabels_um where wknum = lcWeekNum;
            and defaultdir = lcDir group by pracabbrev,defaultdir,descriptn order by pracabbrev,cnt desc nowait

            if not file("c:\vetdata\" + lcDir + "\Pull\Wk" + lcWeekNum +"_Unmapped.xls")
            copy to "c:\vetdata\" + lcDir + "\Pull\Wk" + lcWeekNum +"_Unmapped.xls" type xls
            endif

            select query
            use
            select remindlabels_um
            use
            select remindlabels_temp
            use
            select "remindlabels_um" + lcWeekNum
            use

            select (lcWeekPullFileNam)

           replace all verifyimg with "d-" ;                       
                               for (descrip2 = "--" and not descrip2 = "--  Cur") or ;
                                (descrip3 = "--" and not descrip3 = "--  Cur") or ;
                                (descrip4 = "--" and not descrip4 = "--  Cur") or ;
                                (descrip5 = "--" and not descrip5 = "--  Cur") or ;
                                (descrip6 = "--" and not descrip6 = "--  Cur") or ;
                                (descrip7 = "--" and not descrip7 = "--  Cur") or ;
                                (descrip8 = "--" and not descrip8 = "--  Cur")
                               
                                     select ckBreedScrub
                                    APPEND FROM (lcWeekPullFile) for verifyimg = "d-"
                                  
                              
                                    select (lcWeekPullFileNam)

      endif
                  
            if practices.loop <> "C"      
                  select mailproof
                  use
            endif
            
            select (lcWeekPullFileNam)
            use
                  
            **select practices
      
      
      
endif

select practices
endscan