Solved

Filter a subform on load in Access 2010 based on a calculated field

Posted on 2011-09-06
12
531 Views
Last Modified: 2013-11-28
Experts...I've tried so many different ways and researched how to accomplish this and to no avail...I am at a stand still and the veins are popping out of my neck!  

Here goes...

The calculated field in my subform is simply FieldA - FieldB = MyCalculatedField.

When the form opens with its subform, I would like the subform to only show records where the MyCalculatedField are greater than 0.

Please help!  Simple, easy to follow instructions would be greatly appreciated!

Thank you.
0
Comment
Question by:Sashaski
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
12 Comments
 
LVL 12

Accepted Solution

by:
danishani earned 250 total points
ID: 36492656
You set the Criteria for your Calculated Field to >0.

You can do this in a Query of the Subform Recordsource.

Hope this helps,
Daniel
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 36492661
place this codes in the load event of th main form


Private Sub Form_Load()
Me.SubFormControlName.Form.Filter = "[FieldA]-[FieldB]>0"
Me.SubFormControlName.Form.FilterOn = True
End Sub
0
 

Author Comment

by:Sashaski
ID: 36492715
danishani - I tried your solution.  While it did work in the query to limit the results, it did not impact the subform.

capricorn1 - I tried your solution, it worked!  The subform is filtering properly on load now.  But the controls in the main form are ignoring your code, I believe I may not have the code in the right place?

I'll explain a little further:  I have a number of controls in the main form that allows the end user to filter the subform by.  As soon as I choose any one of the controls to filter by, the filter you just helped me with is ignored.  Is there a particular place in my code that I should be placing your code to ensure that that filter is always on?  

Not sure if I'm explaining this clearly.  I'm also not sure if I should be opening a new question for this part since you solved my initial problem.

Thank you!
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 12

Expert Comment

by:danishani
ID: 36492724
> danishani - I tried your solution.  While it did work in the query to limit the results, it did not impact the subform.<

Is the Query you changed the actual Recordsource of the Subform?

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36492733
You want it to filter...or you want the subform Master/Child relation to reflect the calculated field criteria?

I personally want the second option.
To get that to work nicely you need to add a field to the query underlying the sub-form
That field will be
MyCalculatedField:FieldA - FieldB
Then give it a criteria in the grid of >0
The subform will then only display records where FieldA - FieldB >0

Which is what @danishani said, but more verbosely :)
0
 

Author Comment

by:Sashaski
ID: 36492766
danishani - you were right, I was not in the correct query.  I did go to the recordsource query, applied the filter and that did work in the subform.  However, when I tested the additional filters in my main form, the filters are not filtering properly.  Now that I am looking at a much smaller dataset basedon the filter, I'm not sure it this filtering problem in the main form already existed.  I am going to research that later tonigh (I must leave for class in a few minutes).

Nick67 - thanks for your reply.  My controls on the main form are unbound, and the goal of these controls is to 'refilter' the subform based on any combination of the controls chosen.  There are 6 of them, combo box, multiple choice list boxes & two date range fields.  I want the subform to filter based on the controls chosen in the main form AND the subform should always filter on the calculated field >1 before anything else is chosen.



0
 
LVL 26

Expert Comment

by:Nick67
ID: 36492788
So definitely alter the query the way @danishani and I mentioned.
For the main form then you need some event to fire

Me.Filter = "SomeString"
Me.FilterOn = true


where SomeString is a properly constructed WHERE clause, without the 'where'
That means string criteria in quotes --> SomeField = "This String"
Date criteria in hashes --> AnotherField = #6-Sep-2011#

with all your AND's and OR's in good order.
Try building that in the query grid and switching to SQL view til you get the syntax figured
0
 

Author Comment

by:Sashaski
ID: 36492813
Nick67...thank you for the quick reply.  I will be trying this later tonight after my class and will respond first thing in the morning.  I appreciate all of your input.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36492836
<capricorn1 - I tried your solution, it worked!  The subform is filtering properly on load now.  

But the controls in the main form are ignoring your code, I believe I may not have the code in the right place?>

what controls in the main form ?

did you place the codes in the load event of the MAIN form?
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36496902
You might find my Fancy Filters sample database useful.  It filters data on a subform based on selections from combo boxes on the main form.  Here is a link for downloading it:
http://www.helenfeddema.com/Files/accarch129.zip

and here is a screen shot of the form:
Fancy-Filters-Form.jpg
0
 

Author Comment

by:Sashaski
ID: 36497350
Both capricorn1's solution:

place this codes in the load event of th main form

Private Sub Form_Load()
Me.SubFormControlName.Form.Filter = "[FieldA]-[FieldB]>0"
Me.SubFormControlName.Form.FilterOn = True
End Sub

AND danishani's suggestion

You set the Criteria for your Calculated Field to >0.

You can do this in a Query of the Subform Recordsource.

worked.  The subform is filtering the way I needed it to.  The other bound controls had a separate issue which I resolved.  Thank you both for your help!

Helen & Nick67 - thank you both for your input, Helen - I could have used that about a month ago!  But it has been a learning curve to build a search form from scratch!


0
 

Author Closing Comment

by:Sashaski
ID: 36497360
Experts on this forum are amazing and fast!  Worth every penny of my membership!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question