Bob Collison
asked on
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.
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]
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.
Use the Load event instead. The form is not data-loaded when the Open event fires.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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].Valu e
Me.Filter = "PERIOD_EVENT_KEY='" & WRK_PERIOD_EVENT_KEY_001 & "'"
Me.FilterOn = True
Thanks.
Bob C.
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
Me.Filter = "PERIOD_EVENT_KEY='" & WRK_PERIOD_EVENT_KEY_001 & "'"
Me.FilterOn = True
Thanks.
Bob C.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.