Trouble with Fox Pro Indexes....

[Pesteo]

I am writing a database application that searces the database based on criteria given for one of three fields.

The user is first required to selected the field to search, and then enter the criteria to search for. When the criteria is entered, the user clicks on search. The runs a procedure that builds the SQL statment that grabs the correct information from the database. (Code Following)

SearchType = alltrim(UPPER(thisform.cboSearchType.value))
SearchValue = alltrim(thisform.txtcriteria.value)

open database R:\WORK\swap\db\SWAP.dbc shared


DO CASE
CASE (SearchType == "SYSTEMNAME&quot
thisform.cmdview.enabled = .t.
WITH thisform.gridbrowse
.recordsource = "SELECT sourcepaid, epaid, sourceid, sourcename, systemname FROM delineation WHERE systemname = SearchValue ORDER BY sourcepaid INTO CURSOR temp"
.visible = .t.
ENDWITH
CASE (SearchType == "SOURCEPAID&quot
thisform.cmdview.enabled = .t.
WITH thisform.gridbrowse
.recordsource = "SELECT sourcepaid, epaid, sourceid, sourcename, systemname FROM delineation WHERE sourcepaid = SearchValue ORDER BY sourcepaid INTO CURSOR temp"
.visible = .t.
ENDWITH
CASE (SearchType == "EPAID&quot
thisform.cmdview.enabled = .t.
WITH thisform.gridbrowse
.recordsource = "SELECT sourcepaid, epaid, sourceid, sourcename, systemname FROM delineation WHERE epaid = SearchValue ORDER BY sourcepaid INTO CURSOR temp"
.visible = .t.
ENDWITH
OTHERWISE
messagebox('A search field, and valid criteria must be entered in order to complete the search.', 64, 'No Criteria')
WITH thisform.txtcriteria
.value = ""
.setfocus
ENDWITH
ENDCASE

As you can see from the code, this sets the recordsource of the gridbrowse (located on the Search Form) to the SQL statement that is formed. From that grid, I should be able to select the specific record and view that record in detail on another form. This works fine for EPAID, and the SYSTEM NAME, but when I click on the grid for SOURCEPAID, It seems like the search routine is called again. I think is has something to do with the index, setting SOURCEPAID as the primary key, but have no idea what.... Does anyone else have any ideas?

I would be willing to send all the code if you need it...

Thanks,
Pesteo

 

[zhavic]

Have you set 'RecordSourceType' property to 4 ('SQL statement') ?

If you are using SQL statement as record source, you don't need include clause 'INTO CURSOR temp'

If you want to create 'temp cursor', set 'RecordSourceProperty' to 'Alias' and use something like this:

DO CASE
CASE (SearchType == "SYSTEMNAME&quot
thisform.cmdview.enabled = .t.
WITH thisform.gridbrowse
.recordsource = ""
SELECT sourcepaid, epaid, sourceid, sourcename, systemname FROM delineation WHERE systemname = SearchValue ORDER BY sourcepaid INTO CURSOR temp
.recordsource = "temp"
.visible = .t.
ENDWITH
CASE (SearchType == "SOURCEPAID&quot
thisform.cmdview.enabled = .t.
WITH thisform.gridbrowse
.recordsource = ""
SELECT sourcepaid, epaid, sourceid, sourcename, systemname FROM delineation WHERE sourcepaid = SearchValue ORDER BY sourcepaid INTO CURSOR temp
.recordsource = "temp"
.visible = .t.
ENDWITH
CASE (SearchType == "EPAID&quot
thisform.cmdview.enabled = .t.
WITH thisform.gridbrowse
.recordsource = ""
SELECT sourcepaid, epaid, sourceid, sourcename, systemname FROM delineation WHERE epaid = SearchValue ORDER BY sourcepaid INTO CURSOR temp
.recordsource = "temp"
.visible = .t.
ENDWITH
OTHERWISE
messagebox('A search field, and valid criteria must be entered in order to complete the search.', 64, 'No Criteria')
WITH thisform.txtcriteria
.recordsource = ""
.value = ""
.setfocus
ENDWITH
ENDCASE


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Pesteo]

Answer to the first question, yes, the record source type is set to 'SQL Statement"

I tried using your solution, but it did not solve the problems at hand. I am sending the query to a cursor, so that another search screen does not pop up.

When I did have that extraneuos screen pop up, (cursor statement being taken out) I was able to click on the records without problem. I thought that was the function of the grid (almost that of an imbedded table).

Still looking for any other hints...

Pesteo