I have a multiple to multiple relationship which means that I have a third cross reference table, made up of the keys (id's) of both tables involved in the relationship.

All three tables are table buffered.

I have made a list control to display the multiple child records of the current master. It finds the current child records by running a SQL statement like :

select * from ChildTable where ChildTable_id in (select ChildTable_id from CrossRefTable where CrossRefTable.MasterTable_id = MasterTable.MasterTable_id) into cursor curSelected

I have made a look up screen to be able to pick the multiple child records. It adds the new records into the buffered CrossRefTable.

When the SQL runs it appears to run against the 'disk' copy of the CrossRefTable, not the buffered version. I have confirmed this by not buffering the CrossRefTable. In this case everything works as required.

Of course I want to buffer the cross table data so the changes can be reverted if required.

Can I force the SQL to run against the 'buffered' data. Would I have this problem if I used local views? Do I have to revert to foxpro 2.5 scan ... endscan to build the current ChildTable_id cursor?