Fox Pro Table Update....

[Pesteo]

Here's the deal, I am working with a Fox Pro Database that has seven tables, I wrote a program that takes that a comma delimited text file, puts it into a cursor, and then from that cursor updates the record in the table...

The update procedure is works as follows, One of the employees is in charge of maintaining sample numbers and information on that, collection date, etc. This is inserted into the database by hand when the samples come in. The samples are then set to a lab to be analyzed, results returned are in the form of a comma delimited text file...with each sample on a different line. This information has to be put back into the database matching sample num to sample num...no new records are added, just updated...

My program works great...All, the data is updated correctly...

I know this by going to the respective table, searching for the sample number(PK) and verifying the data....

My update routine also works wonderful, I start the manual update program, search for the sample_num, and get the same information that is currently in the database....everything matches up....

The problem comes with the serch routine...It is this routine that we let employees not associated with the data input use, basically lets them run a search based on particular criteria, and then output that information to a report....(Here's the Problem)the information returned from the search only includes data that is manually entered not the information that was updated using the program that I wrote. It reads the field and returns a blank field... For instance, Sample_Num, Collection_date, Collector, are all returned...Data input manually, carbon, magnesium, floride, ph, etc., data input by the program, doesn't show up, but is in the database....

Any suggestions,

Things I've checked...
My routines are all pointing at the right tables...In fact, before I start the Search/Input routines, I execute the command close all databases, run the routine, and browse the tables that are opened(by the program), search those tables for the sample_num, and the data is there....

I run the query at embedded in the program at the command prompt of the database, and all the data is returned....

I've reindexed all the tables after the updates were done....

I've rewritten the search routine from scratch...

Anyway, I hope someone else has an idea, because I am fresh out....

Thanks...

 

[Mike Gagnon]

What is the code for you search routine? Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Pesteo]

Main Program

with _screen
.visible = .f.
.caption = "South Dakota Geological Survey"
.windowstate = 2
.visible = .t.
endwith

set sysmenu off
set century on
set deleted on
close database all

*!* *!* * development database
open database V:\Lab_Transfer\db\wq shared
set default to V:\Lab_Transfer\search
* production database
*!*open database v:\wqdb\db\wq shared
*!*set default to c:\sdgs\wq_search

use wqhead alias wqhead in 0 shared
use wqfield alias wqfield in 0 shared
use wqinorg alias wqinorg in 0 shared
use wqorg alias wqorg in 0 shared
use wqpest alias wqpest in 0 shared
use wqiso alias wqiso in 0 shared

on shutdown do pgms\bye.prg
do form forms\frmQuery
read events

Form Query is an query form that asks for the field to be searched, and the criteria, when search is clicked it calls...rpt_selection

Code for Search User Procedure

Field1 = alltrim(upper(thisform.cboField1.value))
Text1 = upper(alltrim(thisform.txtField1.value))
Field2 = upper(alltrim(thisform.cboField2.value))
Text2 = alltrim(upper(thisform.txtField2.value))
SearchType = "" && SIMPLE, AND, OR, BETWEEN depending on option selected.

if(Field1 == "&quot
messagebox('No search criteria entered.', 48, 'Search Criteria Error')
this.parent.cboField1.setfocus
else
if(Text1 == "&quot
messagebox('Please enter a search term.', 48, 'Search Criteria Error')
this.parent.txtField1.setfocus
else
if (Field2 == "" AND (thisform.opgBoolean.optOr.value == 1 OR thisform.opgBoolean.optAnd.value == 1 OR ;
thisform.opgBoolean.optBetween.value == 1))
messagebox('Please select another field to search or change the search to SIMPLE.', 48, 'Search Criteria Error')
this.parent.cboField2.setfocus
else
if(Text2 == "" AND NOT Field2 == "&quot
messagebox('Please enter a second search term.', 48, 'Search Criteria Error')
this.parent.txtField2.setfocus
else
if(thisform.opgBoolean.optSimple.value == 1)
SearchType = "SIMPLE"
else
if(thisform.opgBoolean.optOr.value == 1)
SearchType = "OR"
else
if(thisform.opgBoolean.optAnd.value == 1)
SearchType = "AND"
else
if(thisform.opgBoolean.optBetween.value == 1)
SearchType = "BETWEEN"
endif
endif
endif
endif

thisform.visible = .f.

do pgms\reset_form.prg with thisform

do form forms\rpt_selection.scx with ;
Field1, Text1, Field2, Text2, SearchType
endif
endif
endif
endif

Code for rpt_selection - basically asks if edit version or final version....

Field1 = thisform.txtField1.value
Field2 = thisform.txtField2.value
Text1 = thisform.txtText1.value
Text2 = thisform.txtText2.value
SearchType = thisform.txtSearchType.value

* Field1 is the field used in the top portion of the query
* Text1 is the top search criteria
* Field2 is the field used in the bottom portion of the query
* Text2 is the bottom search criteria
* SearchType is type of search selected by user (SIMPLE, AND, OR, BETWEEN)

Table1 = ""
Table2 = ""
InorgRecCount = 0 && number of records returned from wqinorg
OrgRecCount = 0 && number of records returned from wqprg
IsoRecCount = 0 && number of records returned from wqiso
PestRecCount = 0 && number of records returned from wqpest

if(Field2 == "SECTION_LIST&quot
section1 = substr(Text2, 1, 2)
section2 = substr(Text2, 3, 2)
section3 = substr(Text2, 5, 2)
section4 = substr(Text2, 7, 2)
section5 = substr(Text2, 9, 2)
section6 = substr(Text2, 11, 2)
section7 = substr(Text2, 13, 2)
section8 = substr(Text2, 15, 2)
else
if(Field2 == "SECTION_RANGE&quot
sectionR1 = substr(Text2, 1, 2)
sectionR2 = substr(Text2, 3, 2)
endif
endif

if (Field1 == "COLLECTION_DATE" OR Field1 == "PROJECT_MANAGER" OR ;
Field1 == "PROJECT_NAME" OR Field1 == "SAMPLE_NUMBER&quot
Table1 = "WQFIELD"
else
Table1 = "WQHEAD"
endif

if (Field2 == "COLLECTION_DATE" OR Field2 == "ENTRY_DATE" OR ;
Field2 == "PROJECT_NAME" OR Field2 == "SAMPLE_NUMBER&quot
Table2 = "WQFIELD"
else
Table2 = "WQHEAD"
endif

* TOP QUERY
* perform queries for 'SIMPLE', 'AND', 'OR'
if(SearchType == "SIMPLE" OR SearchType == "AND" OR SearchType == "OR&quot
if(Field1 != "COLLECTION_DATE&quot
* inorganic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqinorg ;
where &Table1..&Field1 like "%" + Text1 + "%" ;
AND NOT lab_inorganic like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqinorg.sample_number ;
into cursor tmpTopInorg

* organic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqorg ;
where &Table1..&Field1 like "%" + Text1 + "%" ;
AND NOT lab_voc like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqorg.sample_number ;
into cursor tmpTopOrg

* pesticide
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqpest ;
where &Table1..&Field1 like "%" + Text1 + "%" ;
AND (NOT wqpest.primary_lab_pesticide like "" ;
OR NOT primary_lab_immunoassay like "&quot ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqpest.sample_number ;
into cursor tmpTopPest

* isotope
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqiso ;
where &Table1..&Field1 like "%" + Text1 + "%" ;
AND NOT radionuclide_lab like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqiso.sample_number ;
into cursor tmpTopIso
else && Field1 == "COLLECTION_DATE"
* inorganic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqinorg ;
where wqfield.collection_date == ctod(Text1) ;
AND NOT lab_inorganic like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqinorg.sample_number ;
into cursor tmpTopInorg

* organic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqorg ;
where wqfield.collection_date == ctod(Text1) ;
AND NOT lab_voc like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqorg.sample_number ;
into cursor tmpTopOrg

* pesticide
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqpest ;
where wqfield.collection_date == ctod(Text1) ;
AND (NOT wqpest.primary_lab_pesticide like "" ;
OR NOT primary_lab_immunoassay like "&quot ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqpest.sample_number ;
into cursor tmpTopPest

* isotope
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqiso ;
where wqfield.collection_date == ctod(Text1) ;
AND NOT radionuclide_lab like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqiso.sample_number ;
into cursor tmpTopIso
endif

else && perform queries for 'BETWEEN'
if(Field1 != "COLLECTION_DATE&quot
* inorganic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqinorg ;
where &Table1..&Field1 between Text1 AND Text2 ;
AND NOT lab_inorganic like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqinorg.sample_number ;
into cursor tmpTopInorg

* organic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqorg ;
where &Table1..&Field1 between Text1 AND Text2 ;
AND NOT lab_voc like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqorg.sample_number ;
into cursor tmpTopOrg

* pesticide
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqpest ;
where &Table1..&Field1 between Text1 AND Text2 ;
AND (NOT primary_lab_pesticide like "" ;
OR NOT primary_lab_immunoassay like "&quot ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqpest.sample_number ;
into cursor tmpTopPest

* isotope
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqiso ;
where &Table1..&Field1 between Text1 AND Text2 ;
AND NOT radionuclide_lab like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqiso.sample_number ;
into cursor tmpTopIso
else && Field1 == "COLLECTION_DATE"
* inorganic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqinorg ;
where wqfield.collection_date between ctod(Text1) AND ctod(Text2) ;
AND NOT lab_inorganic like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqinorg.sample_number ;
into cursor tmpTopInorg

* organic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqorg ;
where wqfield.collection_date between ctod(Text1) AND ctod(Text2) ;
AND NOT lab_voc like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqorg.sample_number ;
into cursor tmpTopOrg

* pesticide
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqpest ;
where wqfield.collection_date between ctod(Text1) AND ctod(Text2) ;
AND (NOT primary_lab_pesticide like "" ;
OR NOT primary_lab_immunoassay like "&quot ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqpest.sample_number ;
into cursor tmpTopPest

* isotope
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqiso ;
where wqfield.collection_date between ctod(Text1) AND ctod(Text2) ;
AND NOT radionuclide_lab like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqiso.sample_number ;
into cursor tmpTopIso
endif
endif

* BOTTOM QUERY
if(Field2 == "" OR SearchType == "BETWEEN&quot
* do nothing
else
if(Field2 != "COLLECTION_DATE" AND Field2 != "ENTRY_DATE" AND ;
Field2 != "SECTION_LIST" AND Field2 != "SECTION_RANGE&quot
* inorganic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqinorg ;
where &Table2..&Field2 like "%" + Text2 + "%" ;
AND NOT lab_inorganic like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqinorg.sample_number ;
into cursor tmpBottomInorg

* organic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqorg ;
where &Table2..&Field2 like "%" + Text2 + "%" ;
AND NOT lab_voc like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqorg.sample_number ;
into cursor tmpBottomOrg

* pesticide
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqpest ;
where &Table2..&Field2 like "%" + Text2 + "%" ;
AND (NOT wqpest.primary_lab_pesticide like "" ;
OR NOT wqpest.primary_lab_immunoassay like "&quot ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqpest.sample_number ;
into cursor tmpBottomPest

* isotope
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqiso ;
where &Table2..&Field2 like "%" + Text2 + "%" ;
AND NOT wqiso.radionuclide_lab like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqiso.sample_number ;
into cursor tmpBottomIso
else
if(Field2 == "ENTRY_DATE" OR Field2 == "COLLECTION_DATE&quot
* inorganic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqinorg ;
where &Table2..&Field2 == ctod(Text2) ;
AND NOT lab_inorganic like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqinorg.sample_number ;
into cursor tmpBottomInorg

* organic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqorg ;
where &Table2..&Field2 == ctod(Text2) ;
AND NOT lab_voc like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqorg.sample_number ;
into cursor tmpBottomOrg

* pesticide
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqpest ;
where &Table2..&Field2 == ctod(Text2) ;
AND (NOT wqpest.primary_lab_pesticide like "" ;
OR NOT wqpest.primary_lab_immunoassay like "&quot ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqpest.sample_number ;
into cursor tmpBottomPest

* isotope
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqiso ;
where &Table2..&Field2 == ctod(Text2) ;
AND NOT wqiso.radionuclide_lab like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqiso.sample_number ;
into cursor tmpBottomIso
else
if(Field2 == "SECTION_LIST&quot
* inorganic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqinorg ;
where (substr(alltrim(wqhead.location), 8, 2) == section1 ;
OR substr(alltrim(wqhead.location), 8, 2) == section2 ;
OR substr(alltrim(wqhead.location), 8, 2) == section3 ;
OR substr(alltrim(wqhead.location), 8, 2) == section4 ;
OR substr(alltrim(wqhead.location), 8, 2) == section5 ;
OR substr(alltrim(wqhead.location), 8, 2) == section6 ;
OR substr(alltrim(wqhead.location), 8, 2) == section7 ;
OR substr(alltrim(wqhead.location), 8, 2) == section8) ;
AND NOT lab_inorganic like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqinorg.sample_number ;
into cursor tmpBottomInorg

* organic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqorg ;
where (substr(alltrim(wqhead.location), 8, 2) == section1 ;
OR substr(alltrim(wqhead.location), 8, 2) == section2 ;
OR substr(alltrim(wqhead.location), 8, 2) == section3 ;
OR substr(alltrim(wqhead.location), 8, 2) == section4 ;
OR substr(alltrim(wqhead.location), 8, 2) == section5 ;
OR substr(alltrim(wqhead.location), 8, 2) == section6 ;
OR substr(alltrim(wqhead.location), 8, 2) == section7 ;
OR substr(alltrim(wqhead.location), 8, 2) == section8) ;
AND NOT lab_voc like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqorg.sample_number ;
into cursor tmpBottomOrg

* pesticide
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqpest ;
where (substr(alltrim(wqhead.location), 8, 2) == section1 ;
OR substr(alltrim(wqhead.location), 8, 2) == section2 ;
OR substr(alltrim(wqhead.location), 8, 2) == section3 ;
OR substr(alltrim(wqhead.location), 8, 2) == section4 ;
OR substr(alltrim(wqhead.location), 8, 2) == section5 ;
OR substr(alltrim(wqhead.location), 8, 2) == section6 ;
OR substr(alltrim(wqhead.location), 8, 2) == section7 ;
OR substr(alltrim(wqhead.location), 8, 2) == section8) ;
AND (NOT wqpest.primary_lab_pesticide like "" ;
OR NOT wqpest.primary_lab_immunoassay like "&quot ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqpest.sample_number ;
into cursor tmpBottomPest

* isotope
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqiso ;
where (substr(alltrim(wqhead.location), 8, 2) == section1 ;
OR substr(alltrim(wqhead.location), 8, 2) == section2 ;
OR substr(alltrim(wqhead.location), 8, 2) == section3 ;
OR substr(alltrim(wqhead.location), 8, 2) == section4 ;
OR substr(alltrim(wqhead.location), 8, 2) == section5 ;
OR substr(alltrim(wqhead.location), 8, 2) == section6 ;
OR substr(alltrim(wqhead.location), 8, 2) == section7 ;
OR substr(alltrim(wqhead.location), 8, 2) == section8) ;
AND NOT wqiso.radionuclide_lab like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqiso.sample_number ;
into cursor tmpBottomIso
else && Field2 == "SECTION_RANGE"
* inorganic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqinorg ;
where substr(alltrim(wqhead.location), 8, 2) between sectionR1 and sectionR2 ;
AND NOT lab_inorganic like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqinorg.sample_number ;
into cursor tmpBottomInorg

* organic
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqorg ;
where substr(alltrim(wqhead.location), 8, 2) between sectionR1 and sectionR2 ;
AND NOT lab_voc like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqorg.sample_number ;
into cursor tmpBottomOrg

* pesticide
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqpest ;
where substr(alltrim(wqhead.location), 8, 2) between sectionR1 and sectionR2 ;
AND (NOT wqpest.primary_lab_pesticide like "" ;
OR NOT wqpest.primary_lab_immunoassay like "&quot ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqpest.sample_number ;
into cursor tmpBottomPest

* isotope
select wqhead.location as loc, ;
wqfield.sample_number as sample__number, * ;
from wqhead, wqfield, wqiso ;
where substr(alltrim(wqhead.location), 8, 2) between sectionR1 and sectionR2 ;
AND NOT wqiso.radionuclide_lab like "" ;
AND wqhead.location = wqfield.location ;
AND wqfield.sample_number = wqiso.sample_number ;
into cursor tmpBottomIso
endif
endif
endif
endif

* Now, combine cursors tmpTopInorg, tmpBottomInorg, tmpTopPest,
* tmpBottomPest, tmpTopIso, tmpBottomIso and tmpTopOrg, tmpBottomOrg
* using 'SIMPLE, 'AND', 'OR' cases

do case
case (SearchType == "SIMPLE&quot
* wqinorg
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * ;
from tmpTopInorg ;
into cursor csrInorg ;
order by loc, sample__number
InorgRecCount = _tally

* wqorg
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * ;
from tmpTopOrg ;
into cursor csrOrg ;
order by loc, sample__number
OrgRecCount = _tally

* wqpest
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * ;
from tmpTopPest ;
into cursor csrPest ;
order by loc, sample__number
PestRecCount = _tally

* wqiso
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * ;
from tmpTopIso ;
into cursor csrIso ;
order by loc, sample__number
IsoRecCount = _tally

case (SearchType == "AND&quot
* wqinorg
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * from tmpTopInorg ;
where sample__number in ;
(select sample__number from tmpBottomInorg) ;
into cursor csrInorg ;
order by loc, sample__number
InorgRecCount = _tally

* wqorg
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * from tmpTopOrg ;
where sample__number in ;
(select sample__number from tmpBottomOrg) ;
into cursor csrOrg ;
order by loc, sample__number
OrgRecCount = _tally

* wqpest
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * from tmpTopPest ;
where sample__number in ;
(select sample__number from tmpBottomPest) ;
into cursor csrPest ;
order by loc, sample__number ASC
PestRecCount = _tally

* wqiso
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * from tmpTopIso ;
where sample__number in ;
(select sample__number from tmpBottomIso) ;
into cursor csrIso ;
order by loc, sample__number
IsoRecCount = _tally

case (SearchType == "OR&quot
* wqinorg
select * from tmpTopInorg ;
union ;
(select * from tmpBottomInorg) ;
into cursor tmpInorg
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * from tmpInorg ;
into cursor csrInorg ;
order by loc, sample__number
InorgRecCount = _tally

* wqorg
select * from tmpTopOrg ;
union ;
(select * from tmpBottomOrg) ;
into cursor tmpOrg
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * from tmpOrg ;
into cursor csrOrg ;
order by loc, sample__number
OrgRecCount = _tally

* wqpest
select * from tmpTopPest ;
union ;
(select * from tmpBottomPest) ;
into cursor tmpPest
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * from tmpPest ;
into cursor csrPest ;
order by loc, sample__number
PestRecCount = _tally

* wqiso
select * from tmpTopIso ;
union ;
(select * from tmpBottomIso) ;
into cursor tmpIso
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * from tmpIso ;
into cursor csrIso ;
order by loc, sample__number
IsoRecCount = _tally

case (SearchType == "BETWEEN&quot
* wqinorg
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * ;
from tmpTopInorg ;
into cursor csrInorg ;
order by loc, sample__number
InorgRecCount = _tally

* wqorg
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * ;
from tmpTopOrg ;
into cursor csrOrg ;
order by loc, sample__number
OrgRecCount = _tally

* wqpest
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * ;
from tmpTopPest ;
into cursor csrPest ;
order by loc, sample__number
PestRecCount = _tally

* wqiso
select recno() as RecNo1, recno() as RecNo2, ;
recno() as RecNo3, * ;
from tmpTopIso ;
into cursor csrIso ;
order by loc, sample__number
IsoRecCount = _tally
endcase

thisform.visible = .f.
thisform.release

if (InorgRecCount = 0 AND OrgRecCount = 0 AND IsoRecCount = 0 AND PestRecCount = 0)
messagebox('No records were found to match query.', 64, 'Query Results')
frmQuery.visible = .t.
else
do form forms\final_output_options.scx with ;
InorgRecCount, OrgRecCount, IsoRecCount, PestRecCount, ;
Field1, Text1, Field2, Text2, SearchType
endif

Which finaly calls the output version screen....

Which performs layout functionallity...
Browse the cursor, print preview, or print final....

 

[Mike Gagnon]

All, the data is updated correctly...

That doesn't seem the be the case. Sounds like you are using "actual" data, and also "fake" data. My suggestion is the first move the data that you are not using somewhere else (just so there is no confusion). Second if you are using a form and there are tables in the dataenvironment, make they are the right ones and if you plan to switch back and forth between actual and not, remove the tables from the dataenvironment are open them programmatically in the load of the form, making sure that you use the proper SET DEFAULT TO.
Are your tables bufferred? If so make sure that ALL tables are svaed using tableupdate(), if not it might appear that the data is being saved, but it might not be the case. Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Pesteo]

I'm still a little confused, first off, what is your differintiation between "actual" and "fake" data. When I run my updates from the text file, I can can close everything, shutdown my system and come back a half hour later, search for the record in the table, and all the data will be there, that which was input using the form, and that which was updated by the program.

Move a copy, I have a backup of it....

I close all of the tables, and open the right database, and all the tables when I run the form.

Thanks...

 

[Mike Gagnon]

I'm still a little confused, first off, what is your differintiation between "actual" and "fake" data.

I was refering to the lines below. You seem to at some point being using a different location for your database. And if you start developing in a certain location and you put tables in the Dataenvironment of the form, VFP rememebers the original location of the tables, the paths are hard-coded in the form. So no matter what database you open, it will only use the tables VFP remembers

open database V:\Lab_Transfer\db\wq shared
set default to V:\Lab_Transfer\search
* production database
*!*open database v:\wqdb\db\wq shared
*!*set default to c:\sdgs\wq_search


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Pesteo]

The reason for those lines of code is to distinguish between the live database, and the backup - play with it database....

We never make any changes to the live until everything is in working order....It is currently pointing at the database I can screw up, add random records, etc...

They are exact copies of eachother as far as structure...what is different, is the data they contain....

The problem I am having is I run the update program on the test database, open that base and search for the records that I just updated, the table reflects that the updates were completed correctly, data in the table reflects the data in the text file that I had just imported....

This is confirmed when I run the form to manually update the records, I search for the record, and the data that is displayed is valid....

When I run the form used to search, but not update the database, Only the information that existed prior to the update is dasplayed. Everything else is blank....

Thanks....

 

[Mike Gagnon]

When I run the form used to search, but not update the database, Only the information that existed prior to the update is dasplayed. Everything else is blank....

Hence the reason I would suspect you are looking at the wrong data. Try to input information and confirm that it has been updated, and run your search form, and locate the record that is suppose to have been updated and then open another instance of VFP and take a look to see which table has to updated data.
P.S. You also have not replied whether the tables are in the dataenvironment of the form or not. This is a key point that might be causing the problem. Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.