Filtering a Subform

[kopy]

I'm trying to filter a subform. The main form is frmDataEntry, the subform is frmDataEntry2, and the criteria for the filter is a combo box cboNP on a form frmMainSwitchboard. My current code is as follows:
===========================================================
Dim stDocName, strSQL As String
Dim stLinkCriteria As String

stDocName = "frmDataEntry"
DoCmd.OpenForm stDocName, , , stLinkCriteria
strSQL = "Select * from tblPlan where"
strSQL = strSQL & " [NP]=" & Forms![frmMainSwitchboard]![cboNP]
Me.[frmDataEntry2].Form.RecordSource = strSQL
===========================================================
Any help will be greatly appreciated, Kopy

 

[dhookom]

The "Me." in your code is probably referencing frmMainSwitchboard or whatever form contains the code. You would need to use something like:

Dim stDocName, strSQL As String
    Dim stLinkCriteria As String

    stDocName = "frmDataEntry"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    strSQL = "Select * from tblPlan where"
    strSQL = strSQL & " [NP]=" & Forms![frmMainSwitchboard]![cboNP]
    Forms![frmDataEntry].[frmDataEntry2].Form.RecordSource = strSQL

I'm not sure it this will work or not. It might depend on where the code is running. I don't generally like modifying properties of one form from another. I would prefer setting the RecordSource of the subform from code in the subform's parent.

Duane
Hook'D on Access
MS Access MVP

 

[kopy]

I'm now getting an error 2465 that Access can't find the field referred to int he expression.

 

[dhookom]

What is your exact code? Did you trouble-shoot based on the error message?

Duane
Hook'D on Access
MS Access MVP

 

[kopy]

Thanks for the help. I finally solved the p[roblem by writing a query that used the contents of the combo box as selection critera. I thenk filtered the main form by the query.