Apply Filter to Form & Subform

[edgalljr]

I have a Button on a form that filters on 2 fields using the following code:

Private Sub cmd_NavyDue_Click()
With Me
.FilterOn = True
    DoCmd.ApplyFilter , "[Branch] = 'NAVY' And [Count]>1"
    Me.OrderBy = "[Person ID] DESC"
    Me.OrderByOn = True
End With
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?

[ChipR]

Not a lot to go on here, but perhaps you mean something like:

Me.Filter = "[Branch] = 'NAVY' AND " _
           & [Count]>1 AND " _
           & [Duty Station] = " _
           & Me![sub Rank Rate Location].Form![Duty Station]
Me.FilterOn = True

[NeoPa]

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?

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.

[edgalljr]

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!D uty Station"

[ChipR]

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.

[edgalljr]

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!

[ChipR]

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):

Me.[sub Rank Rate Location].Form.Filter = "[Duty Station] IS NULL"
Me.[sub Rank Rate Location].Form.FilterOn = True

[edgalljr]

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.

[ChipR]

In that case, maybe you could filter the main form on:

NOT EXISTS 
  (SELECT [Duty Station] 
   FROM [tbl Rank Rate Location] 
   WHERE [tbl Rank Rate Location].[Person ID] 
       = [tbl Master].[Person ID])

Spaces in table and field names are a real pain.

[edgalljr]

That screaming you hear is me! That works perfectly and I think I now understand the concept! Thank you so much!

[NeoPa]

@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.