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

Question

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

Be aware that the way you're doing this, any customization you've done grid2 will be lost. Changing the RecordSource of a grid loses all column widths, headers, etc. So does recreating the cursor under a grid. You need to create the cursor once and set it as the RecordSource. Then when you change to change the contents, you SELECT into a different cursor, ZAP the original cursor, and the APPEND FROM the other cursor into the original cursor. — Tamar E. Granor, Apr 12 '22 at 20:17

Stefan Wuebbe · Answer 1 · 2022-04-12T23:53:52.720

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

score 1 · Answer 2 · answered Apr 12 '22 at 14:54

1

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

thisform.grid2.RecordSource="workffs"

answered Apr 12 '22 at 14:54

Herb

636
1
15
29

score 1 · Answer 3 · answered May 11 '22 at 13:47

Your simple solution would be to quote the workffs, it needs to be a string as @Herb pointed out. Also you could use a RecordSourcetype of 4 and have your SQL as the recordsource as @Stefan Wuebbe pointed out.

In either case, you need to be careful about what is called a grid 'reconstruction'. If you simply select into that cursor again, you would lose your grid's formatting. However, as long as the resulting structure is the same as the original one, you could simply work it around by setting your recordsource to nothing temporarily. ie (based on your code):

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

thisform.grid2.RecordSource=''

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

thisform.grid2.RecordSource='workffs'

Or with SQL recordsource:

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

thisform.grid2.RecordSource=thisform.grid2.RecordSource

You can download FoxyClasses and check grid samples. One of them exactly does what you are asking for. Samples was compiled back in VFP6 times as I remember, you need to recompile the project to run as an app or exe. Or you could simply run the sample forms one by one with "do form". Check its help file (Foxyclasses is in public domain for a long time now and you could use its classes, code as you see fit).

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

3 Answers3