Filtering multiple instances of Access subform in parent form

Question

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!

Answer 1

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.

Answer 2

Link fields preliminaries

The Link Master Fields and Link Child Fields (Link fields) define basic binding between a subform record source and the parent record. In other words, if a subform should automatically update to show only related records when the parent form navigates to a new record, then the Link fields specify this filtering. Such built-in subform binding works very well with no hassle when the parent and child have well-defined primary key and foreign key pairs. In a well-normalized database, the key pairs are often single-field numberic ID's. An example would be:

Link Master Fields:  ID 
Link Child Fields:   ParentID

However, it is perfectly reasonable that parent and child queries be related via multiple fields. This is also supported by the Link fields. Multiple fieldnames are separeated by semicolons. As another example:

Link Master Fields:  Title;TrxDate
Link Child Fields:   GroupTitle;TrxDate

A subform can also be independent of the parent form's record source, or the parent form might not even have a record source--acting as a static container for unbound elements and subforms. In other words, a subform need not be automatically filtered based on the parent record navigation. In this case, the Link fields are set to be empty strings.

---

Setting RecordSource property at run time

Although form record sources are usually specified at design time, this is not a requirement. It is sometimes useful and/or necessary to set the record source during run-time in a variety of cases, such as when

• the subform should be filtered based on unbound control values.
• the subform is designed to show records from similar (same fields and datatypes) but distinct queries or tables.
• the same form will be the Source Object of multiple subform controls on the same parent form.

When a subform RecordSource property is set at runtime, it is best to set the LinkMasterFields and LinkChildFields properties immediately afterward. This is true even if the Link fields should be blank (i.e. undefined). This is because Access will automatically define the Link fields if it determines that the parent form's record source and subform's record source have compatible fields. Sometimes it guesses right based on indexes and relationships, but sometimes it defines unwanted and bogus Link fields, so it best to set them explicitly.

With that said, the method of setting a subform record source and whether or not Link fields are specified are really separate issues. The approach to either aspect is defined by fulfilling different requirements.

---

Examples

When and where to set a subform's RecordSource property depends on how the subform should be filter based on other factors of the parent form. There are perhaps common patterns, but otherwise there is no particular requirement or definite approach. Only as a matter of example will I mention a couple possibilities.

If all variables are known at the time a parent form is loaded, it may be sufficient to set the subform's RecordSource in the Form_Load() event handler. For instance, consider that you have a parent form for managing music playlists. You want to show public and private playlists separately, but they otherwise have identical attributes/fields. You create one form that contains all playlist fields, then you add two subform controls to the primary form and set each Source Object to the same playlist form. Now you define the following on the parent form:

'* ---------------------------------------------------
'* In the subform's module

Private Sub SetRecordSource(visibility as String)
    Me.RecordSource = "SELECT * FROM PlayLists WHERE [Visbility] = '" & visibility & "'"
End Sub

Public Sub SetPublicRecordSource()
    SetRecordSource "Public"
End Sub

Public Sub SetPrivateRecordSource()
    SetRecordSource "Private"
End Sub

'* ---------------------------------------------------
'* In the parent form's module

Private Sub Form_Load()
    '* I personally like using strongly-typed local variables
    '* to enhance compile-time error checking and to facilitate Intellisense 
    Dim subform as Form_Playlist

    With Me.PrivatePlaylistSubform
        Set subform = .Form
        subform.SetPrivateRecordSource
        .LinkMasterFields = "ID"
        .LinkChildFields = "PersonID"
    End With            

    With Me.PublicPlaylistSubform
        Set subform = .Form
        subform.SetPublicRecordSource
        .LinkMasterFields = "ID"
        .LinkChildFields = "PersonID"
    End With        
End Sub

This all could have been coded in the Form_Load() event using full references like Me.PrivatePlaylistSubform.Form.RecordSource, but that goes against better programming practices.

---

Now consider a more complicated and relevant example. I'm just making up the details since the question doesn't specify specifics. In this case, the parent form represents records for a particular user. For each user, multiple subforms show statistics for different "managers". Only one form is created for the subform, so the RecordSource property needs to be set during runtime for the multiple subform control instances.

If the multiple "managers" were predefined--perhaps stored in a databse table that could be joined in the RecordSource query, it would be rather simple to add appropriate joins and/or references in the specific queries. But we'll go a step further and let the managers be chosen dynamically for each user. Thus, each subform will have a corresponding ComboBox for choosing from available managers.

Honestly, this could be accomplished in an almost identical fashion as the last example. Here's a quick and dirty show of the Form_Load event handler on the parent form. It uses two features to filter each subform: 1) Link fields AND 2) reference to a particular control on the parent form.

Private Sub Form_Load()
    '* ManagerBox# are all subform controls
    '* comboManagers# are ComboBoxes corresponding to each subform
    '* Both the parent form and each child form's record source has a UserID field.
    '* frm_ProfileHub is the name of the parent form.

    With Me.ManagerBox1
        .Form.RecordSource = "SELECT * FROM UserManagers" & _ 
            " WHERE ManagerID = [Forms]![frm_ProfileHub]![comboManager1]"
        .LinkMasterFields = "UserID"
        .LinkChildFields = "UserID"
    End With            

    With Me.ManagerBox2
        .Form.RecordSource = "SELECT * FROM UserManagers" & _
            " WHERE ManagerID = [Forms]![frm_ProfileHub]![comboManager2]"
        .LinkMasterFields = "UserID"
        .LinkChildFields = "UserID"
    End With            
End Sub

In the code above, I imagined that the ComboBox controls are NOT bound to fields of the primary record. There is also an apparent link between the primary userID and the manager table. Neither of these have to be the case and so the code might instead look like

    '* Subform is not directly linked to the primary record
    '* Subform is only filtered by the ComboBox value
    With Me.ManagerBox2
        .Form.RecordSource = "SELECT * FROM Managers" & _
            " WHERE ManagerID = [Forms]![frm_ProfileHub]![comboManager2]"           
        .LinkMasterFields = ""
        .LinkChildFields = ""
    End With

or

    '* In this case, the comboBoxes are already bound to a parent form's field,
    '*  more specifically to fields labeled [MangerID1], [MangerID2], etc.
    '* So instead of referring to a form control (which is less efficient),
    '* just use the Link fields to handle the binding.
    '* RecordSource is actually identical, so that could be set
    '* at design time and then only update the Link fields at run-time.
    With Me.ManagerBox2
        .Form.RecordSource = "SELECT * FROM UserManagers WHERE"
        .LinkMasterFields = "UserID;ManagerID2"
        .LinkChildFields = "UserID;ManagerID"
    End With

I've found in many of my own complicated forms that the built-in, automatic binding features and form control references--while they are quicker to program--can be rather slow during navigation. It's likely that multiple subforms will already slow down the parent form, but if Link fields are define and queries contain direct reference to form controls, there can be excessive refreshes with every trival action on the form. Here's an alternative approach that sets each RecordSource to a verify specific, filtered query only when relevant values are updated.

'* ---------------------------------------------------
'* In the subform's module

Public Sub SetRecordSource(vUserID As Variant, vManagerID as variant)
    If Not (IsNumeric(vUserID) AND IsNumeric(vManagerID)) Then
        Me.RecordSource = ""
    Else
        Me.RecordSource = "SELECT * FROM UserManagers" & _
            " WHERE UserID = " & vUserID & " AND ManagerID = " & vManagerID
    End If
End Sub

'* ---------------------------------------------------
'* In the parent form's module

Private Sub LinkManagerBox1()
    Dim subform as Form_frm_ManagerBox
    With Me.ManagerBox1
        Set subform = .Form
        subform.SetRecordSource Me.UserID, comboManager1.Value
        .LinkMasterFields = ""
        .LinkChildFields = ""
    End With            
End Sub

Private Sub LinkManagerBox2()
    Dim subform as Form_frm_ManagerBox
    With Me.ManagerBox2
        Set subform = .Form
        subform.SetRecordSource Me.UserID, comboManager2.Value
        .LinkMasterFields = ""
        .LinkChildFields = ""
    End With            
End Sub

Private Sub comboManager1_AfterUpdate()
    LinkManagerBox1
End Sub

Private Sub comboManager2_AfterUpdate()
    LinkManagerBox2
End Sub

Private Sub Form_Current()
    LinkManagerBox1
    LinkManagerBox2
End Sub