Anything Affect the Set Filter To Command?

[wanderaround]

I have a form with a Command Button which going to collect data from varous tables and append to a temp table. The problem is the Set filter To command not work in the form of Command Button. I have another form with the same command but it work fine. Anyone can tell me what setting will affect the issue of Set Filter To Command? Following is part of the program inside the Command Button.
.......
.......
SELECT dep_wd && Add Deposit and Withdraw to Temp Table.
SET FILTER TO ((MONTH(dw_date)=MONTH(ws_date) .and. YEAR(dw_date)=YEAR(ws_date)) .and. TRIM(dw_client)=TRIM(ws_client))
GO TOP
DO WHILE .not. EOF()
STORE VAL(dw_code) TO ws_cn
STORE dw_type TO ws_type
STORE dw_client TO ws_client
STORE dw_stock TO ws_stock
STORE dw_qty TO ws_qty
SELECT temp_cn
APPEND BLANK
replace tr_cn WITH ws_cn
replace tr_type WITH ws_type
replace tr_client WITH ws_client
replace tr_stock WITH ws_stock
replace tr_qty WITH ws_qty
replace tr_date with ws_date
SELECT dep_wd
SKIP
ENDDO
SET FILTER TO
.......
.......

These commands are inside the loop and the loop will be repeat when the ws_client is changed until EOF.(*content of ws_client will be update from another table*)

Some more information: There are another table with Set filter to command and doesn't work in the same form. But I had copy those command to another program and use procedure call then it work fine. Why?
VFP 7 with sp1.
Thanks for any effort.

Wanderaround

 

[ramani]

HI

Why dont you use SQL select. It will be very fast.

SELECT VAL(dw_code) AS tr_cn, dw_type AS tr_type, ;
ws_client AS tr_client, dw_stock AS tr_stock, ;
dw_qty AS tr_qty, dw_date AS tr_date ;
FROM dep_wd INTO DBF temp_cn ;
WHERE ((MONTH(dw_date)=MONTH(ws_date) .and. ;
YEAR(dw_date)=YEAR(ws_date)) .and. ;
TRIM(dw_client)=TRIM(ws_client))

If ws_date etc.. are fields of another tabe.. suitable use the alias of that table. This will avoid the use of FILTERS and make the whole working much faster.


ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[wanderaround]

Ramani,

Thanks for your fast response, I did try your suggestion. But it run with error. The error message is something like 'temp_cn.dbf is already a table of database.' Sorry for this stupid question because I am not familiar with SQL command.

Wanderaround

 

[csr]

Well, here is one thing I noticed.

Your filter involves dw_client being equal to ws_client and you are assigning ws_client with the value of dw_client for each record in your main file.

That seems odd.

May be working but does not make any sense to me.

It also may be causing your problem.

Don
dond@csrinc.com

 

[wanderaround]

Don,

Although I store the dw_client to ws_client again, it is after the statement of Set Filter command. Therefore, it won't change the content of the ws_client. Anyway, I remove the line but with the same result.

Wanderaround

 

[csr]

well, the next thing I would do is debug it ...

put

DEBUG
SUSPEND

just after you assign the filter

then WATCH ...dbfilter()

and step through the code to see what happens ...

Don
dond@csrinc.com

 

[csr]

Another thing to be aware of ...

If this code is in the CLICK method of a command button, then this code will not have access to the variables WS_DATE and WS_CLIENT unless they are public variables or are defined within the CLICK method of the command button.

Don
dond@csrinc.com

 

[wanderaround]

Don,

Those variables were set to public variable in Init procedure of the form. FYI.

Wanderaround.

 

[csr]

Well, then ... debugging is your only means of dealing with this.



BTW ...

As a matter of technique ...

I would suggest you have only one place where you declare public variables. The best place, in my experience, is to declare them in the MAIN.PRG of your application.

Then you know exactly what your public variables are.

Don
dond@csrinc.com

 

[wgcs]

I think that csr has a valid point with the assignments:

You have to understand that the SET FILTER TO command is setting an expression (that should evaluate to a logical) to re-evaluate for each record.

A variable that is referenced in the filter expression must remain in scope as long as that filter is set on that alias.

Also, if you change the value of a variable that is referenced in the filter expression, that new value is used when evaluating the expression.

Try this:

SELECT dep_wd   &&  Add Deposit and Withdraw to Temp Table.
lnMon = MONTH(ws_date) && only call function once
lnYr  = YEAR(ws_date)  && only call function once
SET FILTER TO MONTH(dw_date)=lnMon and YEAR(dw_date)=lnYr ;
              and ALLTRIM(dw_client)=ALLTRIM(ws_client))
SCAN
  INSERT INTO temp_cn ;
   (tr_cn,     tr_type, ;
    tr_client, tr_stock, ;
    tr_qty,    tr_date );
   Values ;
   (VAL(dep_wd.dw_code), dep_wd.dw_type, ;
    dep_wd.dw_client,    dep_wd.dw_stock, ;
    dep_wd.dw_qty,       m.ws_date)
    SKIP
ENDSCAN
SET FILTER TO

 

[wanderaround]

Don, wqcs, Ramani,

Thanks for all of your information. The problem have already solved. Now, I am interesting in the SQL command where Ramani had post for me at the begining. Do anyone know how I can avoid the error? The information from help of VFP also say that if the table is open, it will have problem to update those information to the Table.

Thanks for your great effort.

Wanderaround.