Searching through a table for a certain value

[woodyinoz]

Hi all,

I'm looking to search for a value within a table and if it it is located I want to delete the row/rows it occurs in and replace it with some queried info PRIV:Routecheck.db).

I am trying to use the following code but am having various problems. One being that each record found with the value brings up the message question (I only want it to show once).


var

tcCompare tCursor
pltnum string

endvar

pltnum = plateid.value

tcCompare.open("FullFinalRoute.db&quot
tcCompare.edit()

scan tcCompare:

if tcCompare."PlateID" = pltnum then

if msgQuestion("Saving Route",
"You are overwriting an existing route " + pltnum + ". Are you sure?&quot = "Yes" then

setMouseShape(MOUSEWAIT, TRUE)

qVar = Query

FullFinalRoute.db | plateid |
Delete | ~pltnum |

endquery

if not qVar.executeQBE() then
errorshow()
endif

dt.SetSource ( "RIV:RouteCheck.db" )
if dt.getSourceType ( ) = DTASCIIFixed Then
dt.loadDestSpec ( "SpecTable" )
endIf
dt.setDest ( "FullFinalRoute.db" )
if not dt.getAppend () then
dt.setAppend ( True )
endif
dt.transferData ( )

setMouseShape(MOUSEARROW, TRUE)

else

setMouseShape(MOUSEARROW, TRUE)

endif

else

dt.SetSource ( "RIV:RouteCheck.db" )
if dt.getSourceType ( ) = DTASCIIFixed Then
dt.loadDestSpec ( "SpecTable" )
endIf
dt.setDest ( "FullFinalRoute.db" )
if not dt.getAppend () then
dt.setAppend ( True )
endif
dt.transferData ( )

endscan

tcCompare.endEdit()
tcCompare.close()

endif

Can anyone help?

Thanks,

Woody.

 

[footpad]

Woody,

You should avoid inserting or deleting records in a scan loop, for that throws off the counting of the loop itself.

The reason you're getting prompted for each match in your table is because your prompt is inside the scan loop. If you only want to appear once, you need to move the msgQuestion outside (or above) the scan loop.

I'm not sure you need to mix a query with a scan loop, for the query implies an internal scan loop; that is, it will review all records by default.

You might find it easier to import the data into a temporary table and then use a CHANGETO query to replace the values you want to overwrite, instead of deleting then adding new records.

Hope this helps...

-- Lance