ACCESS 2010 Data Filter Property Syntax

Hi Experts,
I have a SubForm that I want to filter the data that displays based on a Field on another Form (not the Parent Form).

Instead of using a query to filter that data I want to use the Me.Filter command.

I am trying to use the Me.Filter command in the Form Open Event but can't get it to work.

The Field in the SubForm Table Record is called PERIOD_EVENT_KEY  The Field containing the Other Form matching value is WRK_PERIOD_EVENT_KEY_001.

Here is the syntax I have been trying to use.
Dim M40000_001 As Form
Set M40000_001 = [Forms]![M-40-000 - Event Module Menu]
Dim WRK_PERIOD_EVENT_KEY_001 As String
[WRK_PERIOD_EVENT_KEY_001] = [M40000_001]![PERIOD_EVENT_KEY]
Me.Filter = "PERIOD_EVENT_KEY='" & [WRK_PERIOD_EVENT_KEY_001] & "'"
Me.FilterOn = True

Could you please advise what the correct syntax should be?

Thanks.
Bob C.
Bob CollisonSystem ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Use the Load event instead. The form is not data-loaded when the Open event fires.
Gustav BrockCIOCommented:
No brackets:

 Dim M40000_001 As Form
 Dim WRK_PERIOD_EVENT_KEY_001 As String

 Set M40000_001 = [Forms]![M-40-000 - Event Module Menu]
 WRK_PERIOD_EVENT_KEY_001 = [M40000_001]![PERIOD_EVENT_KEY].Value
 Me.Filter = "PERIOD_EVENT_KEY='" & WRK_PERIOD_EVENT_KEY_001 & "'"
 Me.FilterOn = True

Try to avoid the heavy use of caps.

/gustav
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,
I have moved the code to the Form Load Event with the following syntax.  When I try to open the Form I get a Enter Parameter Value prompt for Me.Filter.

If I just click the Prompt OK  option the form displays with the correct filtering.

Here is the current code.
Dim M40000_001 As Form
Set M40000_001 = [Forms]![M-40-000 - Event Module Menu]
Dim WRK_PERIOD_EVENT_KEY_001 As String
WRK_PERIOD_EVENT_KEY_001 = [M40000_001]![PERIOD_EVENT_KEY].Value
Me.Filter = "PERIOD_EVENT_KEY='" & WRK_PERIOD_EVENT_KEY_001 & "'"
Me.FilterOn = True

Thanks.
Bob C.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is Period_event_key a String value?

When I try to open the Form I get a Enter Parameter Value prompt for Me.Filter.
That often means something is not spelled correctly, or you're referring to something incorrectly. Check the spelling for all your Form and Control names.

Also, you can shorten that code block to:

Me.Filter = "PERIOD_EVENT_KEY='" & Forms("M-40-000 - Event Module Menu").PERIOD_EVENT_KEY & "'"
Me.FilterOn = True

Assuming Period_Event_Key is a Control on the form. If it's a Field, try this:

Me.Filter = "PERIOD_EVENT_KEY='" & Forms("M-40-000 - Event Module Menu")!PERIOD_EVENT_KEY & "'"
Me.FilterOn = True
Bob CollisonSystem ArchitectAuthor Commented:
Hi Scott,

PERIOD_EVENT_KEY is a string Value in a Field on the Form being referenced.  It is one of the main filtering values for the application and is 'looked-up' in hundreds of places.  The Value in this particular case is "20122000TEST**'

I tried your code and I get the same results.

Thanks.
Bob C.
Gustav BrockCIOCommented:
It may be better and simpler to add the filter to the LinkMaster-LinkChild fields for the subform control.

Create a textbox on the parent form, say, txtPeriodEventKey
Set as ControlSource: =[Forms]![M-40-000 - Event Module Menu]![PERIOD_EVENT_KEY]

Expand the LinkMasterFields with: txtPeriodEventKey
Expand the LinkChildFields with: PERIOD_EVENT_KEY

Remove the filter and filter code.

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.