Filter a Subform Issue

[kopy]

I have a subform that I'm trying to filter via the selection on a combo box from another form. My current problem is that the main form opens, displays all of the records on the main form and if the subfom doesn't meet the criteria its blank. How do I display only the records where the subform meets the criteria?

Here's my current code:

Dim stDocName, strSQL As String
Dim stLinkCriteria As String

stDocName = "frmDataEntry"
DoCmd.OpenForm stDocName, , , stLinkCriteria

strSQL = "SELECT * FROM tblPlan " _
& "WHERE NP = """ _
& Forms![frmMainSwitchboard]![cboNP] & """"

Forms![frmDataEntry].[frmDataEntry2].Form.RecordSource = strSQL
Forms![frmDataEntry].[frmDataEntry2].Form.Requery

 

[MajP]

You need to change the recordsource of the main form or filter the main form not the subform. Example

Main form is "Owners"
subform is "Cars Owned"

If the main form is linked to the subform by Owner ID, the sub form shows all car type for each owner.

If I want to show only the owners who have mustangs then it would be something like

dim strSql as string
strSql = "Select OwnerID, other Owner fields, from tbl Owner INNER JOIN tblCar ON tblOwner.ownerID = tblCar.ownerID where tblCar.carID = '"& Forms![frmMainSwitchboard]![cboNP] & "'"

docmd.open form "frmCarOwner"
set forms("frmCarOwner").recordsource = strSql

 

[MajP]

One way to make this easier so that you do not have to try to get a complex sql string correct is to do the following.

1) Your main form is currently based on hopefully a query but if not a table. Lets call it qryMain.
2) Make a new query called qryMainFiltered. This should have all of the same fields as query main, but now do an inner join to the table that contains your records in the subform. I beleive it is called tblPlan. Include in the left side of the join at least the field NP from tblPlan. In the criteria for NP put "Forms![frmMainSwitchboard]![cboNP]" (no quotes). This query returns all main form records that have a child record where NP equals the value in cboNP.
3)Then make your code
DoCmd.OpenForm stDocName
forms(stDocName).recordsource = "qryMainFiltered"

 

[kopy]

I followed your advice in the last post and it solved the problem, Thanks!