By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,663 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,663 IT Pros & Developers. It's quick & easy.

Filter By Selection Issue

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Presumably you have a calculated control to show the total? That's always
the best way if possible.

If the Control Source is an expression such as:
=Sum([Amount])
so that it sums the Amount field on a continuous form, it should return the
right results. You can use the Recalc method of the form to force it to
recalculate, but that should not be necessary for the scenario you describe.

If the Control Source looks like:
=DSum("Amount", "MyTable", ...
then it does not know about the filter applied to the form. It may be
possible to set the 3rd argument to an expression that includes the
appropriate filtering, but that would be messy to maintain.

If you are actually trying to store the total, you have now experienced why
we generally advise against this, and working with a calculated total is
better. You could mess with the form's Apply Filter event, but I've not
found that to be a productive approach.

If you need to open a report that has the same filter as applied to your
form:
Dim strWhere As String
If Me.Dirty Then
Me.Dirty =False
End If
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport, "MyReport", acViewPreview, , strWhere

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*****@hotmail.com> wrote in message
news:2f*************************@posting.google.co m...

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.

Nov 12 '05 #2

P: n/a
TC

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

(snip)
If the Control Source looks like:
=DSum("Amount", "MyTable", ...
then it does not know about the filter applied to the form. It may be
possible to set the 3rd argument to an expression that includes the
appropriate filtering, but that would be messy to maintain.

DSum ("Amount", "MyTable", iif (me.filteron, me.filter, "") ?

TC


Nov 12 '05 #3

P: n/a
Well, *if* the same filter were to apply, TC:

If Me.FilterOn Then
Me.SomeControl.ControlSource = "=DSum(""Amount"", ""MyTable"",
Me.Filter)
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TC" <a@b.c.d> wrote in message news:1070082429.989170@teuthos...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

(snip)
If the Control Source looks like:
=DSum("Amount", "MyTable", ...
then it does not know about the filter applied to the form. It may be
possible to set the 3rd argument to an expression that includes the
appropriate filtering, but that would be messy to maintain.

DSum ("Amount", "MyTable", iif (me.filteron, me.filter, "") ?

TC

Nov 12 '05 #4

P: n/a
Hi,

I would first like to thank all people that contributed
(especially the one below) but it still doesnt work:)

The set up is:

form-**AAA**
* subform-**BBB**
- textfield - **project_name** in detail
- textfield - **value** in detail
- textfield - **total** in form footer
* textfield - finalTotal

If I could display my form footer I'd do it without subform but
as this does not work (and I do not know why) I have to use subform!!!

In textfield **total** in subform footer I have =Sum([**Value**]).
I tried really everything. I also played arround
with the DSUM, but I had hard time getting it to work. Basically
it never sees that filter is on. Maybe it is because my subform contains
the datasheet on which the filter is applied, and when I tell it
to use subform filter, it says that it is not defined.

Recap:
When I open the **AAA* form all projects are there with their values.
The total value is there as well.
Now comes the "tricky" part: I click my mouse cursor on the desired
**project_name** and I click on the access provided button "Filter By Selection".
The data displayed now is only one mathching that **project_name**.
BUT THE TOTAL IS STILL THE OLD ONE, FOR ALL THE PROJECTS!!!!!!

This is becoming nightmare, any addtional help is really appriciated

Damjan

PS I also tried recalc
PPS This is ms project.
PPPS I read my data from view
Nov 12 '05 #5

P: n/a
Okay: thanks for supplying further info.
Forget DSum() - it will not be useful here.
It should work as you expect, so there is something else strange here.

From the database window, open the subform ("BBB") in design view.
Set its Default View to Continuous Form (just for now).
Click the Form Footer section (grey bar), and make sure its Visible property
is Yes.
Save.

Now open the subform directly from the database window.
The form footer show the correct totals for all the rows, right?
Right-click a project, and Filter By Selection.
Now the form footer shows a decreased total?

Once you have the subform working correctly as a form, you can close it, and
then open the main form. You should see the subform in continuous view, with
the total in the subform footer. Filter it. Does the subform footer decrease
as expected?

Once you have that correct, you can then work on why the main form is not
picking up the right amount from the form in the subform control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*****@hotmail.com> wrote in message
news:2f*************************@posting.google.co m...

I would first like to thank all people that contributed
(especially the one below) but it still doesnt work:)

The set up is:

form-**AAA**
* subform-**BBB**
- textfield - **project_name** in detail
- textfield - **value** in detail
- textfield - **total** in form footer
* textfield - finalTotal

If I could display my form footer I'd do it without subform but
as this does not work (and I do not know why) I have to use subform!!!

In textfield **total** in subform footer I have =Sum([**Value**]).
I tried really everything. I also played arround
with the DSUM, but I had hard time getting it to work. Basically
it never sees that filter is on. Maybe it is because my subform contains
the datasheet on which the filter is applied, and when I tell it
to use subform filter, it says that it is not defined.

Recap:
When I open the **AAA* form all projects are there with their values.
The total value is there as well.
Now comes the "tricky" part: I click my mouse cursor on the desired
**project_name** and I click on the access provided button "Filter By Selection". The data displayed now is only one mathching that **project_name**.
BUT THE TOTAL IS STILL THE OLD ONE, FOR ALL THE PROJECTS!!!!!!

This is becoming nightmare, any addtional help is really appriciated

Damjan

PS I also tried recalc
PPS This is ms project.
PPPS I read my data from view

Nov 12 '05 #6

P: n/a
Hi,

I like the fact that we are along the same lines, I do not like the fact
that this still doesnt work:)

I've done all that while waiting for someone to reply:
it does NOT work!!!

What I tried though is all of that (on continious form) on normal .mdb
file and it works.
when I try it on my access project it doesnt.
So the problem must be there.

I have access XP, MS SQL Server Personal Ed & Windows Proffesional.
I use Windows NT authentication on SQL Server (I've got a wizard
that seems to call it trusted connection).
(I can change this but I only know how to do it by re-installing SQL Server).

To restate:
I have a simple table in my mssql server
I have a cont. form with sum field in access project.
It adds it up at the beggining, but when
I use Select By Filter it doesn't re-sum the sum.

Wow!

Again thanks

Damjan

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>...
Okay: thanks for supplying further info.
Forget DSum() - it will not be useful here.
It should work as you expect, so there is something else strange here.

From the database window, open the subform ("BBB") in design view.
Set its Default View to Continuous Form (just for now).
Click the Form Footer section (grey bar), and make sure its Visible property
is Yes.
Save.

Now open the subform directly from the database window.
The form footer show the correct totals for all the rows, right?
Right-click a project, and Filter By Selection.
Now the form footer shows a decreased total?

Once you have the subform working correctly as a form, you can close it, and
then open the main form. You should see the subform in continuous view, with
the total in the subform footer. Filter it. Does the subform footer decrease
as expected?

Once you have that correct, you can then work on why the main form is not
picking up the right amount from the form in the subform control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*****@hotmail.com> wrote in message
news:2f*************************@posting.google.co m...

I would first like to thank all people that contributed
(especially the one below) but it still doesnt work:)

The set up is:

form-**AAA**
* subform-**BBB**
- textfield - **project_name** in detail
- textfield - **value** in detail
- textfield - **total** in form footer
* textfield - finalTotal

If I could display my form footer I'd do it without subform but
as this does not work (and I do not know why) I have to use subform!!!

In textfield **total** in subform footer I have =Sum([**Value**]).
I tried really everything. I also played arround
with the DSUM, but I had hard time getting it to work. Basically
it never sees that filter is on. Maybe it is because my subform contains
the datasheet on which the filter is applied, and when I tell it
to use subform filter, it says that it is not defined.

Recap:
When I open the **AAA* form all projects are there with their values.
The total value is there as well.
Now comes the "tricky" part: I click my mouse cursor on the desired
**project_name** and I click on the access provided button "Filter By

Selection".
The data displayed now is only one mathching that **project_name**.
BUT THE TOTAL IS STILL THE OLD ONE, FOR ALL THE PROJECTS!!!!!!

This is becoming nightmare, any addtional help is really appriciated

Damjan

PS I also tried recalc
PPS This is ms project.
PPPS I read my data from view

Nov 12 '05 #7

P: n/a
Not sure what else to suggest.

The fact that it works correctly in an mdb suggests you have the formula
correct.

Perhaps someone who has more experience with Access projects can help?

<da*****@hotmail.com> wrote in message
news:2f**************************@posting.google.c om...
Hi,

I like the fact that we are along the same lines, I do not like the fact
that this still doesnt work:)

I've done all that while waiting for someone to reply:
it does NOT work!!!

What I tried though is all of that (on continious form) on normal .mdb
file and it works.
when I try it on my access project it doesnt.
So the problem must be there.

I have access XP, MS SQL Server Personal Ed & Windows Proffesional.
I use Windows NT authentication on SQL Server (I've got a wizard
that seems to call it trusted connection).
(I can change this but I only know how to do it by re-installing SQL Server).
To restate:
I have a simple table in my mssql server
I have a cont. form with sum field in access project.
It adds it up at the beggining, but when
I use Select By Filter it doesn't re-sum the sum.

Wow!

Again thanks

Damjan

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...
Okay: thanks for supplying further info.
Forget DSum() - it will not be useful here.
It should work as you expect, so there is something else strange here.

From the database window, open the subform ("BBB") in design view.
Set its Default View to Continuous Form (just for now).
Click the Form Footer section (grey bar), and make sure its Visible property is Yes.
Save.

Now open the subform directly from the database window.
The form footer show the correct totals for all the rows, right?
Right-click a project, and Filter By Selection.
Now the form footer shows a decreased total?

Once you have the subform working correctly as a form, you can close it, and then open the main form. You should see the subform in continuous view, with the total in the subform footer. Filter it. Does the subform footer decrease as expected?

Once you have that correct, you can then work on why the main form is not picking up the right amount from the form in the subform control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*****@hotmail.com> wrote in message
news:2f*************************@posting.google.co m...

I would first like to thank all people that contributed
(especially the one below) but it still doesnt work:)

The set up is:

form-**AAA**
* subform-**BBB**
- textfield - **project_name** in detail
- textfield - **value** in detail
- textfield - **total** in form footer
* textfield - finalTotal

If I could display my form footer I'd do it without subform but
as this does not work (and I do not know why) I have to use subform!!!

In textfield **total** in subform footer I have =Sum([**Value**]).
I tried really everything. I also played arround
with the DSUM, but I had hard time getting it to work. Basically
it never sees that filter is on. Maybe it is because my subform contains the datasheet on which the filter is applied, and when I tell it
to use subform filter, it says that it is not defined.

Recap:
When I open the **AAA* form all projects are there with their values.
The total value is there as well.
Now comes the "tricky" part: I click my mouse cursor on the desired
**project_name** and I click on the access provided button "Filter By

Selection".
The data displayed now is only one mathching that **project_name**.
BUT THE TOTAL IS STILL THE OLD ONE, FOR ALL THE PROJECTS!!!!!!

This is becoming nightmare, any addtional help is really appriciated

Damjan

PS I also tried recalc
PPS This is ms project.
PPPS I read my data from view

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.