Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

using the same table twice 1

Status
Not open for further replies.
HowardHammerman

HowardHammerman

Instructor
Oct 11, 2001
640
US
Hello,

I am trying to get the following code to work:

Sele a
Use tablea index indexa
sele b
Use tablea index indexb again

Unfortunately, while I can open the table twice in different workspaces, once I open it the second time the first workspace loses its index.

Any help would be apprerciated.

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com
800-783-2269
 
Sort by date Sort by votes
Mike,

That did not work. I keep the record pointer but I lose the index. I am using VFP 6.0.

The only work around I can see is to use the table again with the index and then seek the record.

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com
800-783-2269
 
Upvote 0 Downvote
try this

Select 0
Use tablea in 0 again index indexa alias TABLEMAIN
Use tablea in 0 again index indexb alias TABLELOOK

Just because you use the "again" the first time around nothing will happen.



David W. Grewe Dave
 
Upvote 0 Downvote
That did not work. Here is my code:

CLOSE DATA
CLEAR
SELE 0
USE IN_RES INDEX IN-RNUM ALIAS RES
SELE 0
USE IN_RES AGAIN INDEX IN-UNIT ALIAS UNIT
DISP STAT
SELE RES
SEEK "1P008V" // a valid value
* got the message "no active index"

SET INDEX TO IN-RNUM
SEEK "1P008V"
* found the record
SELE UNIT
SEEK "GD303"
* got the message no active index
SET INDEX TO IN-UNIT
SEEK "GD303"
* found the record


It seems that I cannot have two workspaces with the same table with different active indexes open at the same time.

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com
800-783-2269
 
Upvote 0 Downvote
Hi Howard,

How about using a cursor?

Code: 
SELECT * FROM IN_RES INTO CURSOR Res
INDEX ON In-Rnum TAG In-Rnum

pamela
 
Upvote 0 Downvote
Tried David Grewe's suggestion with the following code and results:

close data
sele 0
use in_res in 0 again index in-rnum alias res
use in_res in 0 again index in-unit alias unit
sele res
seek "1P008V"
* Table has no index value
sele unit
seek "GD303"
* FOUND, no problem
SELE RES
DISP STAT
* the alais RES was unit the in-unit.idx index

So the second workspace had the second index attached.
The first workspace had the second index attached.

Thanks for all your help. But I still can't get it to work unless I re-set the index each time I select the workspace.



Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com
800-783-2269
 
Upvote 0 Downvote
did you try opening both indexes in both work areas ?


David W. Grewe Dave
 
Upvote 0 Downvote
The real problem is that you're using IDX indexes. If memory serves, you cannot have the same table open with two different IDXs in charge. If you switch to a structural CDX, you'll be able to do exactly what you want (and, as a side benefit, you won't have to worry about making sure indexes are properly updated anymore).

Tamar
 
Upvote 0 Downvote
Alas, TamarGranor has the right and the final answer. However, I am the tail of a very large dog that I have no control over the indices. This is a legacy application.

So I will simply re-set the index as I move from each work area.

I would like to thank everyone who has contributed to this problem.

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com
800-783-2269
 
Upvote 0 Downvote
myearwood,

I am using VFP 6.0. Because I did not write the application and I am simply writing a small reporting module that will hang off of it I cannot change the indices to *.CDX. So I am stuck with *.IDX.

I can get it to work, sort of, with the following code:

Sele a
use tablea index indexa
Sele b
use tableb
.......
* in the do loop ***
sele a
set index to indexa
rec = recno()
sele b
set index to indexb
seek a.field
if found()
.....
endif
sele a
set index to indexa
go rec
skip
enddo

It is certainly slower, but it works.

Thanks,

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com
800-783-2269
 
Upvote 0 Downvote
Mike,

I guess I am showing my age. What is more modern way?

hh

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com
800-783-2269
 
Upvote 0 Downvote
Use the alias for the table rather than the work area letter or number. In your code, that means you'd use:

SELECT TableA

or

SELECT TableB

This approach has the advantage that you don't care what work area a table is in. It results in much more maintainable code.

Along with this approach, you should never open tables in a specific work area. Instead, let VFP choose a work area for you like this:

SELECT 0
USE TableA

or:

USE TableA IN 0

The SELECT 0 and the IN 0 both tell FoxPro to find the lowest-numbered unused work area and use it. The difference between the two versions is that the former leaves you in that work area while the latter does not.

In general, for any command that accepts an IN clause (which is almost every Xbase command), it's a good idea to use it so that there's no question what work area you're referring to.

Tamar
 
Upvote 0 Downvote
Try the following, I tested it and works.
*
code
Sele a
use tablea index indexa, indexb && New
Sele b
use tablea index indexa, indexb again && New

sele a
set order to 1 && New
sele b
set order to 2 && new

sele a

do while condition
sele a
rec = recno()
sele b
seek a.field
if found()
.....
endif
sele a
go rec
skip
enddo

* Notes
* You need to open both indexes in both work areas
* and then issue
* set order to n
* n = 1, 2
* Also use Tamars suggestion for selecting alias. I left the code as is so you can see it in your way.


try it

Nasib Kalsi

 
Upvote 0 Downvote
That did the trick!
Thank you!

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com
800-783-2269
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Filip Brnic
  • Locked
  • Question
Using 1 table in 2 different forms
2
Replies
31
Views
2K
Chris Miller
Chris Miller
EinTerraner
  • Locked
  • Question
index does not match the table
Replies
15
Views
1K
EinTerraner
EinTerraner
bharons
  • Locked
  • Question
Find the location of the column in the VFP table 2
2
Replies
25
Views
2K
ggreen61
ggreen61
bharons
  • Locked
  • Question
update data table sql server using form vfp 2
Replies
13
Views
586
bharons
bharons
ravicoder
  • Locked
  • Question
Using the Union Method of excel automation
Replies
3
Views
285
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top