Dynamic Where Clause for Grid RecordSource

[dunc0029]

Has anyone come across a solution for this type of issue? I created a lookup/record select form that goes against a remote Oracle Database, basically listing members. I have the underlying cursor begin empty, then when they enter the criteria they are looking for, I dynamically build a WHERE clause I want to run against the back end. However, when you resubmit the query, it closes and reopens the cursor and the grid then loses it's RecordSource and goes completely blank. The cursor has the correct set of data, but simply resetting the RecordSource isn't good enough because I've lost all the formatting/captions/etc.

The only thing that immediately comes to mind is releasing and re-adding the grid object on the fly. Is there an easier way?

I can't get away with a parameterized query since the # of items in the WHERE clause varies.

 

[mspratt]

Set the RecordSource="" before submitting the query and put it back after the query has come back. This avoids Grid Reconstruction.

Regards,

Mike

 

[dunc0029]

Thanks - I've almost got it... however my columns now lose their controlsource ( the table is not in the order of the columns on the grid ). Do you know of an easy fix for that or should I loop through before and after and reset them?

Thanks again!

Jason

 

[Olaf Doschke]

Replace the recordsource with a cursor of identical structure, then also the controlsource remains.

Something like select * from view where .f. into cursor c_temp nofilter.

Bye, Olaf.

 

[MarciaAkins]

Hi Jason.

Thanks - I've almost got it... however my columns now lose their controlsource ( the table is not in the order of the columns on the grid ).

I reccommend setting the RecordSource of the grid to an updateable cursor defined in the form's Load method using the CREATE CURSOR command. You can then populate the cursor by ZAPping it, SELECTing the appropriate records into a temporary cursor and then appending the records from the temporary cursor into the one that is the grid's RecordSource. Here is some sample code ( My base class grid has a custom method called ResetGrid() so that it can be called whenever I need to execute this type of "safe select" )

ZAP IN ( This.RecordSource )
SELECT SomeFields FROM SomeTables ;
   WHERE SomeCondition ;
     INTO CURSOR Temp NOFILTER
SELECT ( This.RecordSource )
APPEND FROM DBF( [Temp] )
USE IN Temp
GO TOP IN ( This.RecordSource )
This.Refresh()

There are a few reasons for doing it like this. First, you can set the grid up visually in the form designer since its RecordSource exists prior to instantiation. More important is the fact that a grid does not like having its RecordSource ripped out from under it. If the grid's RecordSource were updated by SELECTing into it directly, it would appear as a blank grey blob on the screen. This is because the SELECT closes the cursor and effectively leaves the grid hanging in mid air, so to speak. ZAPping it, on the other hand, does not.

One way to avoid having the grid turn into a blank grey blob is to set its RecordSource to an empty string before running the SELECT and then resetting it afterward. Although this will work in the simplest of cases, it is not a solution that I recommend. While it will keep your grid from losing its mind, the grid's columns still lose their ControlSources and any embedded controls. So, this works if your grid uses base class headers, base class text boxes, and displays the fields from the cursor in exactly the same order as they are SELECTed. Otherwise, you have to write a lot more code to restore all the things that get lost when the grid is re-initialized.



Marcia G. Akins

http://www.TightlineComputers.com