Alright gang, I'm at a loss. There seems to be several methods for filtering subforms, but I've been successful with very few of them, and none have been able to work with multiple instances.
Here's what I'm trying to accomplish (this is when my VBA nerding collides with my Fantasy Football nerding).
I have a Fantasy Football Auction Draft database that pulls uses from FantasyPros along with player stats, ranks, values, etc.
The primary form allows me to track the draft live. As players are bought, transactions are recorded and linked with Managers.
The "Meat" of the database is the frm_ManagerBox. This form Screen shot here is pulling data with about 16 queries, but the primary record source is the Manager table.
The frm_ManagerBox works fine as a standalone form, and I can scroll through the records (there are 12 managers in our league) without issue.
My goal is to have a master parent form (frm_ProfileHub) that has 12 instances of the frm_ManagerBox, each one filtered to one of 12 of our league managers. Idealy, I'd have a large form with 12 boxes, and under each box, a combobox that populates the subform to the appropriate manager.
However, as soon as I attempt to embed the subform, it all goes to crap. The queries of the subform all break (on the form load, I get 16 popups asking for the definition of each source).
My understanding is that queries in a subform have to call data from the parent form. I could manually set the criteria of each query of the subform to pull from the parent's combobox, but this wouldn't account for the 11 other instances I need on the parent form.
Any help or guidance would be appreciated. Draft day is coming soon! Thanks!
A little late for the original poster, maybe, but here's a solution that doesn't require VBA and is pretty easy to set up. Use the Link Master/Child field's ability to link against multiple fields to your advantage, by defining the filter here.
A concrete example. Let's say you're tracking a person's action items. You've got a parent form called frmPerson, and a subform called frmActionItems, and they link on PersonId. To set up this parent-child relationship, you'd use:
Link Master Fields: personId
Link Child Fields: personId
Let's say, now, that you decide you want two frmActionItems subforms - one to display all currently active action items, and one to display all the completed ones. The action items table has a required isCompleted Yes/No field on it.
Firstly, add two invisible text boxes to the frmPerson form - let's call them txtIsActive and txtIsCompleted. These two textboxes should be unbound, but set the default value for txtIsActive to =False and the default value to txtIsCompleted to =True.
Your child form that will be for displaying active action items will then be linked as follows:
Link Master Fields: personId;txtIsActive
Link Child Fields: personId;isCompleted
...and because txtIsActive is false, the subform will only display records whose isCompleted value is false, so those that are active. Similarly, our child form that will be used to display completed action items will be linked like this:
Link Master Fields: personId;txtIsCompleted
Link Child Fields: personId;isCompleted
The original poster could have set up something similar for his scenario, but with more hidden, unbound controls.