Visual FoxPro 9. Set Grid.RecordSource to the result of SELECT-CodePudding

I have 2 grids and 2 tables. I want the second one to display elements that are related to the selected enement of first grid. This can probably be done in 2 ways: either have a filer on grid2 that changes according to my selection in grid1, or change the recordSource of grid2. I've settled on option 2 for now. (But if you can tell me a better way i'd be very grateful)

Here is my current code

LOCAL ssid
LOCAL num
num = thisform.grid1.ActiveRow
SELECT ids FROM scenes WHERE RECNO()=num INTO ARRAY tmp1
ssid = tmp1[1]    

SELECT scenelink.amount,mesh.namem FROM scenelink,mesh;
where scenelink.ids=ssid AND scenelink.idm=mesh.idm INTO CURSOR workffs

thisform.grid2.RecordSource=workffs

This does not work, it says variable "workffs" is not found. I also tried using VIEW:

CREATE SQL VIEW workffs as ;
SELECT scenelink.amount,mesh.namem FROM scenelink,mesh;
where scenelink.ids=ssid AND scenelink.idm=mesh.idm

thisform.grid2.RecordSource=workffs

There it asks if i want to overwrite "workffs", yet it still says that such variable cannot be found

CodePudding user response：

You have to use quotes around the name of the cursor or view.

thisform.grid2.RecordSource="workffs"

CodePudding user response：

As @Herb pointed out, a Grid.RecordSource type always needs to be a String, so you'd put quotes around the name of your alias.

This can probably be done in 2 ways: either have a filter on grid2 that changes according to my selection in grid1, or change the recordSource of grid2. I've settled on option 2 for now. (But if you can tell me a better way i'd be very grateful)

Actually there are better ways: a classical one is using a "Parametrized SQL View", so that you can modify the content of a local variable, and then use the Requery("myView") function, which would prevent the infamous "Grid Reconstruction" issue that @Tamar described. Another one is using Grid.RecordSourceType = 4, and then put the SQL statement as a string directly into the RecordSource property. Example:

LOCAL oForm as Form
oForm = CREATEOBJECT('TestForm')
oForm.Show(1)
RETURN

DEFINE CLASS TestForm as Form
    AutoCenter = .T.
    DataSession = 2

    PROCEDURE Load
        CREATE CURSOR test (test I)
        LOCAL i
        FOR i = 1 TO 3
            INSERT INTO test VALUES (i)
        ENDFOR
        GO TOP IN test
    ENDPROC

    ADD OBJECT cmdInsert as CommandButton WITH ;
        Left = 10, Height = 24, Caption = "Insert"
    PROCEDURE cmdInsert.Click
        INSERT INTO test VALUES (RECCOUNT('test') 1)
    ENDPROC

    ADD OBJECT cmdRequery as CommandButton WITH ;
        Left = 150, Height = 24, Caption = "Requery"
    PROCEDURE cmdRequery.Click
        WITH Thisform.grdTest as Grid
            .RecordSource = .RecordSource
        ENDWITH
    ENDPROC

    ADD OBJECT cmdRefresh as CommandButton WITH ;
        Left = 290, Height = 24, Caption = "Refresh"
    PROCEDURE cmdRefresh.Click
        Thisform.grdTest.Refresh()
    ENDPROC

    ADD OBJECT grdTest as Grid WITH ;
        Top = 30, ;
        RecordSourceType = 4, ;
        RecordSource = "Select * From test Into Cursor (SYS(2015))"
ENDDEFINE