Filter By Selection & Total Issue

  • Thread starter Thread starter damjanu
  • Start date Start date
D

damjanu

Hi All;

I need little help.

I have a datasheet form.
I allow user to do 'filter by selection'.
My form contains a column with values.
As user changes selections, I want to
calculate totals. I can do this the
first time the form loads.
But when user does filter
by selection, and basically narrows the
data, the sum remains the same. It seems
that the calculation is performed, as it
goes blank and then the same amount shows
up again. I think that it does not know
which data was 'cut' by user, so it
thinks all the data is there. Same thing
happens when I go to print the filter
selected form, it ignores the filter.

Does anyone know how to get arround this
problem.

Thanks a lot in advance,
Damjan
 
Your code cannot see a filter that's being done in an outside procedure. The
only work around is to not allow the users to use filter by selection and
create your own filters. You can then direct your code to use your filters.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
I am not sure I follow.

The filter I am talking about is when
user clicks on a datasheet form's field,
and then clicks Access button "Filter By Selection".
Only the results with that value stay in the form.
At this point I want my total to re-load with the
new amount?

Do we understand each other?

Cheers
Damjan
 
I am not sure I follow.

The filter I am talking about is when
user clicks on a datasheet form's field,
and then clicks Access button "Filter By Selection".
Only the results with that value stay in the form.
At this point I want my total to re-load with the
new amount?

Do we understand each other?
Click to expand...

I don't understand why a =Sum([SomeField]) in the Form's footer doesn't work for
you. I just tested this and the sum tracked any changes to the Form's filter
perfectly. Why don't you try that on a test table and form and see if you can
get it to work.

Just create a table with a single numeric field and populate it with a handful
of records. Then build a continuous-view form bound to that table and place a
control in the form footer to do the summing. You should see that the total
correctly tracks any changes you make to the filter. Compare that to your form
and see if you can determine what is going wrong.
 
I am not sure I follow.

The filter I am talking about is when
user clicks on a datasheet form's field,
and then clicks Access button "Filter By Selection".
Only the results with that value stay in the form.
At this point I want my total to re-load with the
new amount?

Do we understand each other?
Click to expand...

I guess not. If the only filter being applied is the Filter By Selection,
there must be a Where clause in your form's recordsource (the outside
procedure). I have to assume that the recordsource is in code (a SQL
statement) rather than as a saved query. If you change the recordsource to a
saved query, it should requery when you apply the filter, otherwise, you'll
need to direct your users to use only filters which you write as a new
recordsource.

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Hi
I'll describe my test:

1. I set up an Access PROJECT. Only one simple table.
2. I have access XP, and MS SQL Server.
3. I use Windows NT authentication on SQL Server
4. I have a continious form, with a sum field.

When I start it it works, when I use Filter By Selection,
it doesnt re-sum the value, leaving it to be same as it was!!!

Now interesting part is that I've done the same thing
with Access db (.mdb file) and it worked.
So the problem is somewhere in Acces Project support,
or the fact in how my authentication is setup or or or...

Any bells ringing (in my head not!)?

Thanks a lot everyone,
Damjan

Rick Brandt said:
I am not sure I follow.

The filter I am talking about is when
user clicks on a datasheet form's field,
and then clicks Access button "Filter By Selection".
Only the results with that value stay in the form.
At this point I want my total to re-load with the
new amount?

Do we understand each other?
Click to expand...

I don't understand why a =Sum([SomeField]) in the Form's footer doesn't work for
you. I just tested this and the sum tracked any changes to the Form's filter
perfectly. Why don't you try that on a test table and form and see if you can
get it to work.

Just create a table with a single numeric field and populate it with a handful
of records. Then build a continuous-view form bound to that table and place a
control in the form footer to do the summing. You should see that the total
correctly tracks any changes you make to the filter. Compare that to your form
and see if you can determine what is going wrong.
Click to expand...
 
Like I said. The query is an outside procedure. All SQL-Server tables,
views, and stored procedures are outside of Access. They occur in
SQL-Server, not in Access. Filter by selection occurs in Access. It is
filtering on the form's recordsetclone. You need to requery the original SQL
source with the new criteria. It works in an mdb because the form's
recordsetclone and the source are syncronous. That is not the case with the
server based engine.

You cannot do that with filter by selection, at least I have never found a
way. In your form, your total is remaining the same. In my applications I am
sending multiple criteria and if I filter in the form, I get "#error" in the
textbox. With Access project on SQL-Server there is no other way that I know
of than to send your criterial back to the server and requery.

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Hi
I'll describe my test:

1. I set up an Access PROJECT. Only one simple table.
2. I have access XP, and MS SQL Server.
3. I use Windows NT authentication on SQL Server
4. I have a continious form, with a sum field.

When I start it it works, when I use Filter By Selection,
it doesnt re-sum the value, leaving it to be same as it was!!!

Now interesting part is that I've done the same thing
with Access db (.mdb file) and it worked.
So the problem is somewhere in Acces Project support,
or the fact in how my authentication is setup or or or...

Any bells ringing (in my head not!)?

Thanks a lot everyone,
Damjan

"Rick Brandt" <[email protected]> wrote in message
I am not sure I follow.

The filter I am talking about is when
user clicks on a datasheet form's field,
and then clicks Access button "Filter By Selection".
Only the results with that value stay in the form.
At this point I want my total to re-load with the
new amount?

Do we understand each other?
Click to expand...

I don't understand why a =Sum([SomeField]) in the Form's footer doesn't work for
you. I just tested this and the sum tracked any changes to the Form's filter
perfectly. Why don't you try that on a test table and form and see if you can
get it to work.

Just create a table with a single numeric field and populate it with a handful
of records. Then build a continuous-view form bound to that table and place a
control in the form footer to do the summing. You should see that the total
correctly tracks any changes you make to the filter. Compare that to your form
and see if you can determine what is going wrong.
Click to expand...
Click to expand...
 
Hi

Thanks so much.

Just to finish this story I want to make sure I get you correctly:
You cannot use Filter By Selection because sp is being run on SQL Server.
I do not know what is "recordsetclone" but .... ah it is not clear to me.
I mean in mdb Filter By Selection (FBS) queries clone of recordset the
form returned.
Somehow this cannot be done when sp returns recordsetclone? But
still I can query it, just not perform actions on it?


My options are:
(1) to create a filter and as you say:
"send your criterial back to the server and requery."
But can I create my own filter and make it work as
as Filter By selection?

(2) "If you change the recordsource to a saved query, it
should requery when you apply the filter". So change my
stored proc to save query?



Cheers
Damjan

You said:
 
Hi,

Answers in line:

Hi

Thanks so much.

Just to finish this story I want to make sure I get you correctly:
You cannot use Filter By Selection because sp is being run on SQL Server.
I do not know what is "recordsetclone" but .... ah it is not clear to me.
I mean in mdb Filter By Selection (FBS) queries clone of recordset the
form returned.
Somehow this cannot be done when sp returns recordsetclone? But
still I can query it, just not perform actions on it?
Click to expand...

The form's recordsetclone is a local copy of the recordset returned by the
stored proc. (or table, or query in an MDB)
My options are:
(1) to create a filter and as you say:
"send your criterial back to the server and requery."
But can I create my own filter and make it work as
as Filter By selection?
Click to expand...

Yes, you can add criteria to a column in a new stored proc, then run that
proc from the form event. It is very messy code-wise because you need to do
it for every field you want to filter on. Essentially, you are changing the
form's recordsource to the new stored proc every time you filter.
(2) "If you change the recordsource to a saved query, it
should requery when you apply the filter". So change my
stored proc to save query?
Click to expand...

This is not an option in an ADP, only with an MDP front end.
 
Back
Top