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?
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.
0
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
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.