Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ilya Rabyy
  Where is Ilya Rabyy?
 Fountain Valley
 California - United States
 Ilya Rabyy
 To: Paul Gibson
  Where is Paul Gibson?
 Glasgow
 United Kingdom
 Paul Gibson
 Tags
Subject: RE: Filter, Key or something stranger?
Thread ID: 288751 Message ID: 289007 # Views: 34 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Grids
Date: Monday, December 27, 2010 6:34:14 PM         
   


> I have a couple of tables in our collections system, one header and one details table (c_head & c_trans), the c_head table has one record per one or more records in c_trans with a key field of c_ollect. There is a third table involved in this, o_perat which is just a lookup table for collection agents, it's not very important in the context of this issue though.
>
> I have a form with a grid where the recordsource is c_head.
> The form has a print button which calls a VFP report that is based on the records in c_trans and has a main group on the key field, c_ollect.
>
> Before executing the report I select the required data out of c_trans into a cursor and I set a relation between the new cursor and c_head so that when the report is displaying the main details in the details section of the report the information from the correct header record in c_head can be shown in the group header of the report. This report is intended to print any number of collections from the collections system.
>
> When I have finished printing or previewing the report I clear the relationships and get back to the main form where the grid is supposed to be showing all the relevant records in c_head but the grid is now only showing one record, I can't scroll up or down to see any of the other hundreds of records in c_head.
>
> It appears that the grid or the table is restricted such as you would see if using a SET FILTER or SET KEY to only give access to one record. I have tried commands such as SET FILTER TO and SET KEY TO to see if I can allow the other records in c_head to be seen by the grid all in vain. If I suspend execution of the program and take a look at c_head all the records are accessible just not by the grid.
>
> I wrote a simple routine to output the properties of the grid to a text file both before and after the report is called and all the properties are identical so it looks like something is wrong with the table-state or the grid's link to the table.
>
> The simplest solution may be to restructure the report so that it is not dependant on c_head and therefore the relationship being set. That way I could extract all the data from both c_trans and c_head into one cursor and make the report work off that single cursor. Sadly that solution would require a huge amount of work for me as our users have their own report layouts so I would need to dial into hundreds of user's machines to alter the report if I have to change it in any way.
>
> I tried running this without the report aspect but still doing most of the same work by removing the call to the report and replacing it with a call to an nearly-empty modal form with only a close button on it and I still get the same behaviour so it is looking more to do with setting and clearing the relationship on c_head. I have pasted the main parts of my code below:
>
>
>
> 	* Extract the required data out of c_trans to report on that rather than c_trans directly
> 	* Should speed things up regarding hiding the completed notes as it means we can do it without
> 	* using a slow filter
> 	Select ct.* From c_trans ct Where ct.c_ollect In (Select Distinct cn.c_ollect From chosen_notes cn) ;
> 		Into Cursor MyReport nofilter ;
> 		Order By ct.c_ollect
> 
> 	* Ensure the correct order has been set before putting a relation on the table
> 	Select o_perat
> 	Set Order To g_ennum
> 
> 	Select c_head
> 	Set Relation To pick_up Into o_perat
> 	Select MyReport
> 	Set Relation To c_ollect Into c_head
> 	Go Top
> 
> 	Thisform.Visible = .F.
> 
> 	* Put the call to the report here, currently a call to an empty form with a close button for testing
> 	Do Form test_modal
> 
> 	* Remove the relationships and close the temporary cursor
> 	Select MyReport
> 	Set Relation To
> 	Use
> 	Select c_head
> 	Set Relation To
> 	* Try to get the Grid back to showing all the relevant records instead of just one
> 	Set Key To
> 	Go Top
> 

>
>
> Does anyone know what is happening to the table, the grid or the grid's link to the table that means when I return to the form after setting a relationship and then removing it the grid only shows one record?
>
> Any help is greatly appreciated.

I also used to observe this kind of behavior, colleague Paul, albeit when I was setting up relation between two tables in VFP's IDE using two BROWSE windows. This is why I never use it in a program. What you may try to do, after you break the relation and SET ORDER TO 0 (in addition, or instead of, SET KEY TO), is to disconnect the table from the grid and re-connect it again, i.e.

LOCAL lcRecSrc

WITH frmMyForm.grdMyGrid
   lcRecSrc = .RecordSource
   .RecordSource = ""
   .Refresh
   .RecordSource = lcRecSrc
   .Refresh
ENDWITH

that is if you fill out the grid by assigning the name of your table (or cursor) to the grid's RecordSource property. Of course, you may also need to take care of the ColumnCount, and Column.Header.Caption, and whatnot, to make your grid look like nothing's happened, but that's the idea.

HTH.

Regards,

Ilya

ENTIRE THREAD

Filter, Key or something stranger? Posted by Paul Gibson @ 12/23/2010 4:48:17 PM
RE: Filter, Key or something stranger? Posted by Ilya Rabyy @ 12/27/2010 6:34:14 PM