Filter Sub-Form Data - Access 2010

Hi Experts,
I have a Form that contains a SubForm located on a Tab in it.  The SubForm is used to view / update fields displayed in it.  There is no Bound Data Field to link the Form to the SubFiorm.

I want the data in the SubForm to be filtered based on the value in the field WRK_USER_OBJECT_KEY_002.

The Bound Field on the SubForm is USER_OBJECT_KEY.

The following is the Code in the Sub-Form.
Private Sub Form_Load()
On Error GoTo STEP_999
'MsgBox "Step 000-Form Load Event - F-41-091"
GoTo STEP_900
'MsgBox "Step 001-Initialize Common Parameters - F-41-091."
Dim M00000_001 As Form
Set M00000_001 = [Forms]![M-00-000 - Scout Admin System - Main Menu]
Dim F00001_001 As Form
Set F00001_001 = [Forms]![M-00-000 - Scout Admin System - Main Menu]![F-00-001 - System User Lookup SubForm].[Form]
Dim WRK_SYS_USER_KEY_002 As String
[WRK_SYS_USER_KEY_002] = F00001_001![SYS_USER_KEY]
Dim WRK_PARENT_OBJECT_KEY_002 As String
[WRK_PARENT_OBJECT_KEY_002] = "F-41-090**"
Dim WRK_CHILD_OBJECT_KEY_002 As String
[WRK_CHILD_OBJECT_KEY_002] = "R-41-090**"
Dim WRK_USER_OBJECT_KEY_002 As String
[WRK_USER_OBJECT_KEY_002] = [WRK_SYS_USER_KEY_002] & [WRK_PARENT_OBJECT_KEY_002] & [WRK_CHILD_OBJECT_KEY_002]
Me.Filter = "USER_OBJECT_KEY = WRK_USER_OBJECT_KEY_002"
Me.FilterOn = True
STEP_900:
'MsgBox "Step 999-Shutdown."
STEP_990:
'MsgBox "Step 990-Exit Event."
Exit Sub
STEP_999:
'MsgBox Step 999-Error Handler."
Dim M00000_999 As Form
Set M00000_999 = [Forms]![M-00-000 - Scout Admin System - Main Menu]
Dim DEBUG_ERR_MSG_OPTION As String
[DEBUG_ERR_MSG_OPTION] = "Yes" 'Un-Comment for use.
If M00000_999![LKUP_SYS_ERR_MSG_OPTION] = "Yes" Or [DEBUG_ERR_MSG_OPTION] = "Yes" Then
   MsgBox Err.DESCRIPTION & " (" & Err.Number & ")"
   Resume STEP_990
Else
   Resume STEP_990
End If
End Sub

The data is not being filtered.  How can I filter the data in the SubForm?

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.

Rey Obrero (Capricorn1)Commented:
upload a copy of your db
Bob CollisonSystem ArchitectAuthor Commented:
Hi Rey,

The Database Code (without the Back End DBs containing the data) is 110 Meg.  I will have to create a custom version to give to you which will take awhile to do.

Thanks.
Bob C.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Rey,
I have created an App that only contains the relevant data / forms / code.  The Main Form automatically displays when you start the App.

The single Tab contains / displays the SubForm in question.  You will note that 4 records display.  The filtering should filter out the top two records and only display the bottom two record based on the code in the  Form_Load Event of the SubForm.

Please let me know if you have any questions.

Thanks.
Bob C.
2010-SubForm-Filtering.accdb
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
test this

changed

'Me.Filter = "[USER_OBJECT_KEY] = WRK_USER_OBJECT_KEY_002"

with

Me.Filter = "[USER_OBJECT_KEY] = '" & WRK_USER_OBJECT_KEY_002 & "'"
2010-SubForm-Filtering_revised.accdb
Bob CollisonSystem ArchitectAuthor Commented:
Hi Rey,

I replaced the line of code with the one you supplied above in my copy of the DB I supplied to you and it still displays all 4 records.

Thanks,
Bob C.
Gustav BrockCIOCommented:
The simple non-code solution for exactly this is to use the MasterLinkFields and ChildLinkFields properties of the subform control on the main form:

    MasterLinkFields: [WRK_USER_OBJECT_KEY_002]
    ChildLinkFields: [USER_OBJECT_KEY]

/gustav
Rey Obrero (Capricorn1)Commented:
@Bob
did you test the DB i uploaded?

-- comment the code 'GoTo STEP_900  at the top of your codes

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
Bob CollisonSystem ArchitectAuthor Commented:
Hi Gustav,
Your solution requires there to be matching Master / Child Fields and Data.  The two tables don't meet this condition and therefore not a solution.

Hi Rey,
I'm not sure how I had entered the GoTo STEP_900 Code.  It probably was left over from the process of creating the DB for you.  Sorry I didn't pick up on it.

Your solution is exactly what I was looking for and works perfectly.

Thanks.
Bob C.
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.