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:

1. One main form listing all of his collectibles
2. 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:

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

Answer 1

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.