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

Apply Filter to Form & Subform

P: 7
I have a Button on a form that filters on 2 fields using the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_NavyDue_Click()
  2. With Me
  3. .FilterOn = True
  4.     DoCmd.ApplyFilter , "[Branch] = 'NAVY' And [Count]>1"
  5.     Me.OrderBy = "[Person ID] DESC"
  6.     Me.OrderByOn = True
  7. End With
  8. End Sub
This works fine. However, I also want to filter on a field in a Subform when no record exists (or Is Null).

Subform Name: sub Rank Rate Location
Field Name: Duty Station

How do I make the Button apply a filter to the 2 fields on the Form as well as when there is no "Duty Station" on the Subform?
Nov 17 '09 #1

✓ answered by ChipR

In that case, maybe you could filter the main form on:
Expand|Select|Wrap|Line Numbers
  1. NOT EXISTS 
  2.   (SELECT [Duty Station] 
  3.    FROM [tbl Rank Rate Location] 
  4.    WHERE [tbl Rank Rate Location].[Person ID] 
  5.        = [tbl Master].[Person ID])
Spaces in table and field names are a real pain.

Share this Question
Share on Google+
10 Replies


Expert 100+
P: 1,287
Not a lot to go on here, but perhaps you mean something like:
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[Branch] = 'NAVY' AND " _
  2.            & [Count]>1 AND " _
  3.            & [Duty Station] = " _
  4.            & Me![sub Rank Rate Location].Form![Duty Station]
  5. Me.FilterOn = True
Nov 17 '09 #2

NeoPa
Expert Mod 15k+
P: 31,768
@edgalljr
What does this mean Ed?

You've told us nothing about records on the form yet you ask a question related to that. I'm sure if you can explain your requirement a little more clearly then we can help you to realise it.

Remember not to refer to anything in your database unless you have explained it first. Otherwise it's only possible to follow you using guesswork.
Nov 18 '09 #3

P: 7
ChipR... Thanks for the response. When I run the script I get an "Enter Parameter Value" for both "Duty Station" and "Me!sub Rank Rate Location.Form!Duty Station"
Nov 19 '09 #4

Expert 100+
P: 1,287
As NeoPa said, it's impossible to tell what you're doing. I took a guess, hoping that you would get something from the example. Perhaps you can rephrase your question so that we can understand.
Nov 19 '09 #5

P: 7
This database contains 2 relevant tables. "tbl Master" contains all personal information (Person ID, Name, Branch, count, etc.) and a second table called "tbl Rank Rate Location" contains only Person ID, Rank, MOS Rating, and Duty Station. Person ID is common and links the two tables.

On the form I am working with, all of the information from "tbl Master" is displayed on the form "frm Master Tracker". The form also contains a sub form "sub Rank Rate Location" that shows the fields Rank, MOS Rating, and Duty Station.

I want a button on the form "frm Master Tracker" that, when clicked, will only show those records where the person is in a specific [Branch], has a [Count] >1, and does NOT have a Duty Station and will allow me to enter information into any field from either table / form.

Please let me know if I can clarify any further. Thanks for taking the time to respond!
Nov 19 '09 #6

Expert 100+
P: 1,287
I understand much better now, thanks for the clafification.

It sounds like you want to apply a filter the to main form, then apply a separate filter to the subform to show only those records without a Duty Station. To do this from button code on the main form, you'd use (something like):
Expand|Select|Wrap|Line Numbers
  1. Me.[sub Rank Rate Location].Form.Filter = "[Duty Station] IS NULL"
  2. Me.[sub Rank Rate Location].Form.FilterOn = True
Nov 19 '09 #7

P: 7
Actually, I want a single button on the main form. When I click the button, I want to know all of those people with No Duty Station. This will mean that either A) there is a linked record but Duty Station Is Null OR B) there is NO linked record.
Nov 19 '09 #8

Expert 100+
P: 1,287
In that case, maybe you could filter the main form on:
Expand|Select|Wrap|Line Numbers
  1. NOT EXISTS 
  2.   (SELECT [Duty Station] 
  3.    FROM [tbl Rank Rate Location] 
  4.    WHERE [tbl Rank Rate Location].[Person ID] 
  5.        = [tbl Master].[Person ID])
Spaces in table and field names are a real pain.
Nov 19 '09 #9

P: 7
That screaming you hear is me! That works perfectly and I think I now understand the concept! Thank you so much!
Nov 19 '09 #10

NeoPa
Expert Mod 15k+
P: 31,768
@Chip:
Nice solution. The problem wasn't too straightforward.

@Ed:
Pleased you got a solution. I recommend you look at some of the comments posted too. They will help you avoid trouble if you take them onboard.
Nov 19 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.