Here is the code, without the coomented executable lines, it is as it was when I ran it from the dataenvironment. When run as a prg, or one section at a time (without the WHERE for all but the first table) I get prompted whether or not I wish to overright the existing view, except for the ones that count, from them I get "Server returned no result set" Comments with *-* are explanitory notes regarding this problem. For other forms, I would need a different data environment, but with the where cluases this is the evironment I want for this form (Military.scx)

****************************************************
PRIVATE lSuccess


open database data/data1
&& Parent table to all others, filtered to military only for form military.scx
CREATE SQL VIEW master REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Master.SEQUENCE_, Master.SERNUM, Master.INITIALS, Master.L_NAME,;
Master.MOC_SEQ, Master.RANK_SEQ, Master.HOME_PHONE1, Master.LOCAL,;
Master.STREET, Master.CITY, Master.POSTALCODE, Master.L_UPDATE,;
Master.SECTION_SEQ, Master.MARITAL_SEQ, Master.FORCE_SEQ,;
Master.SECURITY_SEQ, Master.SECURITY_NUM, Master.SECURITY_EXPDATE,;
Master.COS_DATE, Master.CLEAR_DATE, Master.ENLIST_DATE, Master.TXP_DATE,;
Master.GENDER, Master.DOB, Master.PAGER, Master.POSITION_SEQ,;
Master.UIC_SEQ, Master.PSID, Master.F_NAME, Master.HOME_PHONE2;
FROM persdat.MASTER Master;
WHERE master.FORCE_SEQ < 3 ;
ORDER BY Master.SEQUENCE_

&&USE master IN 0 ALIAS master
&&go top

&&lSuccess=CURSORSETPROP("Buffering", 5, "master")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"master")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"Master")

*-* These have been tried with and without the WHERE clause to no availe
*-* This and the next two tables are the ones that VFP claims have been changed
&& Military table, 1:1 to master
CREATE SQL VIEW military REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Military.SEQUENCE_, Military.PMAIN_SEQ, Military.ELECT_SEQ,;
Military.OLANG1, Military.OLANG2, Military.PLANG, Military.SLANG, Military.REL_SEQ;
FROM persdat.MILITARY Military;
WHERE Military.PMAIN_SEQ = Master.Sequence_

&&USE military IN 1 ALIAS military

&&lSuccess=CURSORSETPROP("Buffering", 5, "military")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"military")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"Military")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"MIlitary")

&& Meds table, 1:1 to master
CREATE SQL VIEW meds REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Meds.SEQUENCE_, Meds.PERS_SEQ, Meds.MED_DATE, Meds.VISION,;
Meds.COLOUR, Meds.HEARING, Meds.GEOGRAPHICAL, Meds.AIR_FACTOR,;
Meds.LUPDATE, Meds.RTEST_DATE, Meds.OCCUPATIONAL;
FROM persdat.MEDS Meds ;
WHERE Meds.pers_seq = master.sequence_

&&USE meds IN 2 ALIAS meds

&&lSuccess=CURSORSETPROP("Buffering", 5, "meds")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"meds")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"Meds")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"Meds")

&& CFExpress, 1:1 to master
CREATE SQL VIEW cfexpress REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Cfexpress.SEQUENCE, Cfexpress.PERS_SEQ, Cfexpress.TEST_DATE, Cfexpress.STATUS;
FROM persdat.CFEXPRESS Cfexpress ;
WHERE Cfexpress.pers_seq = master.sequence_

&&USE cfexpress IN 3 ALIAS cfexpress

&&lSuccess=CURSORSETPROP("Buffering", 5, "cfexpress")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"cfexpress")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"cfexpress")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"cfexpress")

&& The next four cursors have data processing handled within form code
&& Primary language, 1:1 to military
CREATE SQL VIEW perslang REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Perslang.SEQUENCE, Perslang.LANG_SEQ, Perslang.ABILITY, perslang.TESTDATE;
FROM persdat.PERSLANG Perslang ;
WHERE perslang.sequence = military.PLANG

&&USE perslang IN 4 ALIAS perslang

&&lSuccess=CURSORSETPROP("Buffering", 5, "perslang")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"perslang")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"perslang")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"perslang")

&& Secondary language, 1: 0/1 to military
CREATE SQL VIEW perslang1 REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Perslang.SEQUENCE, Perslang.LANG_SEQ, Perslang.ABILITY, perslang.TESTDATE;
FROM persdat.PERSLANG Perslang ;
WHERE perslang.sequence = military.SLANG

&&use perslang1 IN 5 ALIAS perslang1

&&lSuccess=CURSORSETPROP("Buffering", 5, "perslang1")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"perslang1")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"perslang1")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"perslang1")

&& Dependants, 1:M from Master also used by civilians

CREATE SQL VIEW Dependant_view REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Dependant_view.SEQUENCE_, Dependant_view.PERS_KEY,;
Dependant_view.SPF_NAME, Dependant_view.SPS_SIN_NUM,;
Dependant_view.YOB, Dependant_view.RELATE_SEQ,;
Dependant_view.SP_LANAME, Dependant_view.STREET, Dependant_view.CITY,;
Dependant_view.P_CODE, Dependant_view.PHONE, Dependant_view.REL_CODE,;
Dependant_view.REL_NAME, Dependant_view.SEX;
FROM persdat.DEPENDANT_VIEW Dependant_view ;
WHERE Dependant_view.PERS_KEY = master.SEQUENCE_

&&use dependant_view IN 6 ALIAS dependant_view

&&lSuccess=CURSORSETPROP("Buffering", 5, "Dependant_view")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"Dependant_view")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"Dependant_view")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"Dependant_view")

&& Vehicles, 1:M from Master, also used by civilians


CREATE SQL VIEW Vehicles REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Vehicles.SEQUENCE_, Vehicles.LIC_NUM, Vehicles.MODEL,;
Vehicles.VEH_COLOUR, Vehicles.PROV_REG, Vehicles.PERS_SEQ, Vehicles.MAKE;
FROM persdat.VEHICLES Vehicles ;
WHERE Vehicles.PERS_SEQ = Master.SEQUENCE_

&&USE vehicles IN 7 ALIAS vehicles

&&lSuccess=CURSORSETPROP("Buffering", 5, "Vehicles")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"Vehicles")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"Vehicles")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"Vehicles")

&& The rest are read only tables used to populate dropdown boxes

&& The two strictly military ones first

&& Religion, M:1 from military
CREATE SQL VIEW Religion REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Religion.SEQUENCE_, Religion.RELIGION, Religion.REL_ABR;
FROM persdat.RELIGION Religion
&&use Religion IN 8 ALIAS Religion
&&go top
&& Electoral districts, M:1 from military
CREATE SQL VIEW Elctoral REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Elctoral.SEQUENCE, Elctoral.DIST_NAME, Elctoral.ELCT_NUM;
FROM persdat.ELCTORAL Elctoral

&&USE Electoral IN 9 ALIAS electorl
&& Now the common ones

&& Rank first, M:1, table also holds civilian titles
CREATE SQL VIEW Rank REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Rank.sequence_, Rank.Rank_Name ;
FROM persdat.RANK Rank
&&use rank IN 10 ALIAS rank
&&go top
&& Forcetype, M:1, could be an array, but s$%t happens, so in table

CREATE SQL VIEW forcetype REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT forcetype.sequence, forc_type ;
FROM persdat.forcetype forcetype
&&use forcetype IN 11 ALIAS forcetype
&&go top

&& Security clearences. M:1
CREATE SQL VIEW Security REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT security.secure_key, security.clearence ;
FROM persdat.security Security

&&use security IN 12 ALIAS security
&&go top

&& Languages table M:1 FROM MASTER

CREATE SQL VIEW languages REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT languages.sequence, languages.lang_abbr ;
from persdat.languages Languages
&&use languages IN 13 ALIAS languages
&&go top

&& positions table, M:1 from master
CREATE SQL VIEW positions REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT positions.sequence,positions.position,positions.title ;
positions.position_num ;
FROM persdat.positions positions
&&use positions IN 14 ALIAS positions
&&go top

&& MOC (military occupation codes) table M:1 from Master
CREATE SQL VIEW moc REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT moc.sequence, moc.moc_code, moc.moc_name ;
from persdat.moc moc
&& use moc IN 15 ALIAS moc
&& go top

&& Units table, M:1 from master

CREATE SQL VIEW units REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT units.sequence_, units.uic_code, units.unit ;
FROM persdat.units units
&& use units IN 16 ALIAS units
&& go top

&& Sections table, M:1 from master
CREATE SQL VIEW sections REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT sections.sequence_, sections.sec_name, sections.sec_code ;
FROM persdat.sections sections
&& use sections IN 17 ALIAS sections
&& go top

&& Marital table (marital status, may change ) M:1 from master
CREATE SQL VIEW marital REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT marital.sequence, marital.mar_code, marital.mar_name ;
FROM persdat.marital marital
&&use marital IN 18 ALIAS marital
&&go top

&&select master