When accesing another table in a DO WHILE LOOP, loosing currency

[smsmail]

Hello,

I am new at FOXPRO, Please help!

Within in my DO WHILE loop, I access other tables, and loose the currency of my primary table that I am looping thru.

It appears that after the first record in my primary table is processed, the EOF() condition is prematurely met, because I am accessing other tables.

How do I maintain currency of my primary table?

I would truly appreciate the HELP!!!!!

Below are snipets of my code:

use email3_input

do while .not. eof()

(do some processing)

select max(vin_pos) ;
from eVin_Pos_tbl ;
where email = h_email ;
and branch = h_br

(do some more processing)


select vin_pos ;
from eVin_Pos_tbl ;
where email = h_email ;
and branch = h_br ;
and vin = h_vin


(do some more processing)

insert new record into a table


SKIP

enddo

 

[Mike Lewis]

The easiest solution is to use a SCAN/ENDSCAN loop rather than a DO WHILE/ENDO.

Using your example, it would look like this:

use email3_input

[b]SCAN[/b]
  
  (do some processing)

  select max(vin_pos) ;
    from eVin_Pos_tbl ;
   where email = h_email ;
     and branch = h_br  

  (do some more processing)


   select vin_pos ;
       from eVin_Pos_tbl ;
      where email = h_email ;
        and branch = h_br ;
        and vin = h_vin   

   
  (do some more processing)

   insert new record into a table

[b]ENDSCAN[/b]

With this approach, you don't need the SKIP, and you don't need to test for EOF. More importantly, VFP will keep track of your current table, and make sure it is re-selected at the bottom of the loop.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[smsmail]

Thank you Mike, for the explanation of the "SCAN"

It is working great!

I have another question. How do I prevent values from displaying. The select statement values (i.e. max(vin_pos), vin_pos) are displaying in a window as each record is read.

I tried SET TALK OFF, but not working.

Any suggestions?

Ave'

 

[JRB-Bldr]

As a new VFP user, welcome to the forum

I agree with Mike above in that using a SCAN/ENDSCAN would be a good alternative since SCAN's inherently stay working with the initial table. BUT there is nothing inherent to keep its record pointer in the same place.

However, lets see why your own code is not working....

use email3_input 
[B]SELECT Email3_Input  && [U]Make sure[/U] that you start with the intended table[/B]
DO WHILE !EOF()
  [B]* -- Since I don't know what you may or may not be doing to the record pointer of the primary table -- *
  * -- Save a Return record pointer --
  nRetRec = RECNO()[/B]

  (do some processing)
  (some query from another table)
  (do some more processing)
  (yet another query from another table)
  (do some more processing)
  (insert into yet another table)

   [B]* -- [U]Make sure[/U] that you return to the intended correct table --  
   SELECT Email3_Input[/B]
   [B]* -- If uncertain, go back to intended record BEFORE you do your SKIP ---
   GO nRetRec[/B]
   SKIP
ENDDO

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Smsmail,

I'm glad my suggestion worked.

Regarding your second question, I answered that in the other thread that you just started. In general, it's best to keep each question separate, as it helps other forum members when searching for answers.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[smsmail]

Also, thank you JRBBLDR

As a newbie FOXPRO programmer, I truly appreciate everyone's help. I am glad I found this forum! Really nice!

 

[JRB-Bldr]

"As a newbie FOXPRO programmer"

Just a note -
If you are programming in one of the 'flavors' of Visual Foxpro, this is your forum.

However if you should happen to be programming in one of the older versions of non-Visual Foxpro your questions would be more specifically answered in the other forum:
Microsoft: FoxPro (old versions 1 to 2.6) Forum
forum182

Good Luck,
JRB-Bldr