Access 2007 Filter Subform Based On Field In Main Form?

Question

I was tasked with creating a simple app to maintain a user's collectibles collection using Access 2007. There were some requests, which I have created and implemented. Those being:

One main form listing all of his collectibles
That same main form has a tabbed control below, with each tab containing a subform that in effect "filters" data based on different criteria from the main form. For example, the 1st subform takes the name of the collectible figure in the main form, and displays all other records using that name in the subform. In other words, if the figure is "Darth Vader", the subform would list all collectibles that have the name "Darth Vader".

I have created the application as per user request, but there is one thing that is bothering both of us so far. The subform's first record is the same as the main form. We both find that redundant, and annoying. Granted, my Access skills is weak at best, so this may be a simple fix, but is there a way to remove the duplicate record in the subform? I have tried implementing a where clause in the subform stating to not include the "Figure ID" in the main form. Problem is, it is acting like a Parameter prompt, asking for the main form's FigureID when I open the subform, or the main form. If I type in the Figure ID, it works, but the prompt is something that is obviously not wanted.

FYI:

The main form is based on a query that basically selects all records from the "Figures" table and other related tables
The subform was created when I dropped the subform control onto the tab control, where I linked the necessary master and child fields

So the client has multiple Darth Vader collectible figures. Each of those items has a unique Figure_ID value. But is "Darth Vader" really the "name" of each of those items? Seems like it might be the name of a category of items within the collection, and those member items might have different names: "Lord of Darkness"; "Darth and Luke on Father's Day"; and so forth. It might help to show us a brief representative sample of your data. — HansUp
Hi HansUp! A brief snapshot: Each figure belongs to a series, each series belongs to a line, a line belongs to a manufacturer. So as an example: Mfr: Hasbro Line: Transformers Series: Armada Figure: Optimus Prime — JPM
There are also other tables such as Country and Size since a specific figure can be called by the same name in the same series, but in different size packaging. — JPM
Let's try a different approach. In your main form, you have an entity called "name". Apparently "name" is used as the link field between the main form and a subform ... and the subform displays all rows where "name" matches the value of "name" for the current record in the main form. — HansUp
... But the kicker is you don't actually want the subform to display all rows where "name" matches the value of "name" for the current record in the main form. Instead you want all rows except that specific "name" row which is the main form current record. So what field (or combination of fields) will you use to distinguish which of the subform "name" rows is the one you don't want included? — HansUp

HansUp HansUp · Accepted Answer · 2011-07-09T13:45:39

Say you have a form named frmMain. That form includes two fields in its record source: FigureID; and Figure_name. The form also includes a text box control named txtFigureID which is bound to the FigureID record source field.

frmMain also contains a subform control based on a form named frmSub. The record source for frmSub also includes FigureID and Figure_name fields. The subform control's link master/child field property is Figure_name. Therefore frmSub will display all the rows where Figure_name matches the corresponding value in the frmMain's current record.

Now, if you want frmSub to exclude the specific record (as identified by the unique FigureID value) which is the current record in frmMain, add a WHERE clause to frmSub's record source query:

WHERE FigureID <> Forms!frmMain!txtFigureID

I'm only guessing here, but hope that description is close enough to your actual situation to be useful. If not, show us the SQL you're using as the record source for your subform.

Edit: You get the parameter prompt only when you first open frmMain. Afterwards, you can navigate between records in frmMain, and frmSub shows you only the records you want to see ... without asking you again to supply a parameter value.

The reason that happens is because the subform loads before its parent form ... so a control on the parent form is not available when the subform loads.

I think the cure may be to save the subform without the WHERE condition in its record source. Then, when the main form loads, it can re-write the subform's record source to include the WHERE condition.

So, in frmMain's load event:

Private Sub Form_Load()
    Dim strSql As String
    strSql = "SELECT FigureID, Figure_name FROM YourTable" & vbCrLf & _
        "WHERE FigureID <> Forms!frmMain!txtFigureID"
    Debug.Print strSql
    Me.subformControlName.Form.RecordSource = strSql
End Sub

Watch out for subformControlName. It's a control, not a form. The subform control may have the same name as the form it contains. But it could be a different name.

Access 2007 Filter Subform Based On Field In Main Form?

1 Answers