Applying a filter when used from one unbound subform to another unbound subform while on a navigation control page

[Michael Barkemeyer]

Hi,
I have a navigation control with various tabs on it. On my tab called "Task Center" I have a blank form called "Task Center". On this form I have two subforms which are both unbound.
The first subform is called "Task Center subform". This form contains various fields. [ID],[Project Name],[Owner][Dashboard Open Project Items.CountOfFinished],[Dashboard Closed Project Items.CountOfFinished]

The idea is that the user be able to click the field [Dashboard Open Project Items.CountOfFinished] which represents a count of all the projects that are not yet closed. Or they can click into the field [Dashboard Closed Project Items.CountOfFinished] which represents all the items that are closed.
By clicking into the open projects or closed projects field, the second subform would then be filtered to show only those items that are open or closed.

The second subform name is "ProjectActivitiesChecklist subform". The two fields that need to be used to filter this form are: [Project ID] and [Finished]
so:
Task Center subform [ID] = ProjectActivitiesChecklist subform [ProjectID]
[Finished] is a True/False field

I would appreciate if someone could help with the event procedure code.

Thank you in advance for your help.

 

[theDBguy]

Hi Michael. Welcome to AWF!

I'm not sure I understand what you're asking. Are you able to share your db? Are you saying the user would click in the Dashboard tab and it would take them to the Task Center tab filtered to the one they wanted to see?

 

[Michael Barkemeyer]

Hi,
Thanks for taking a look. What I am attempting to say is that when the user clicks on the count of the open items {highlilted in red box} (which is part of the first subform) the second subform will reflect only open items for that record.

 

[theDBguy]

Hi. Thanks for the clarification. You could try something along these lines:

Me.SubformControlName.Form.Filter = "YourCriteriaHere"
Me.SubformControlName.Form.FilterOn=True

Hope that helps...

 

[Deleted member 147267]

I would think form.filter only applies where the form is bound?
I think you're complicating things by not binding the forms to anything. Must they be unbound?
IMO if they must be unbound, you're going to have to create the subform recordsource on the fly and apply it.

 

[theDBguy]

I would think form.filter only applies where the form is bound?
I think you're complicating things by not binding the forms to anything. Must they be unbound?
IMO if they must be unbound, you're going to have to create the subform recordsource on the fly and apply it.

Hi. Good point. Although the OP mentioned "unbound," I guess in my mind I assumed "bound" after seeing the image of a datasheet with a record counter. I guess I was wrong.

 

[Michael Barkemeyer]

As you can tell I am very new to the whole Access thing and don't have any experience coding. I attempted to zip the db but even after being ziped it is too large.

Private Sub Dashboard_Open_Project_Items_CountOfFinished_Click()
Dim strSQL As String

strSQL = "ProjectID = " & Chr(34) & Me.ID & Chr(34)

Me.[ProjectActivitiesChecklist subform].Form.Filter = strSQL
Me.[ProjectActivitiesChecklist subform].Form.FilterOn = True

End Sub

When I run this I get an error. Run-time error 2465. Microsoft Access cant find the field '|1' referred to your expression.

If I create a "bound" form within the navigation control, can I show it as a continuous form or in datasheet view and still add my second subform?

Sorry for the confusion, but I barely know what it is I'm asking.

Thanks.

 

[theDBguy]

Yeah, it would probably be better if we can actually see your actual db. You can add a datasheet subform to a continuous form by putting it in the footer section of the main form.

 

[Deleted member 147267]

did you start with a copy and compact/repair it first? That may help with the zip size.
If your db contains embedded images in forms, or attachments stored in tables, they usually won't compress. You'd have to remove them.

Forgot to mention in case it's pertinent: the behaviour of a nav form is that only one form (can contain subforms) can be loaded at one time. Switch tabs, 1st form closes, 2nd one opens. If that is going to upset the apple cart, you might want to consider a tab control instead.

 

[Michael Barkemeyer]

Hi,
I have made some changes and removed 90% of the database so I can attach. As suggested, I have placed my second subform into the footer of the first and have linked the master/child fields.
So it seems the only thing that is missing is to automatically apply a filter to only show "Finished" vs non "Finished" items when clicking in the Open or Closed fields. Clear as mud. I know.

I still get an error when clicking into the Open/Closed fields.