Open form and filter form & subform using combo-boxes Rich_in_NZ 12/26/2008 12:03:00 AM Hi, (Using Office 2003, but in Access 2000 format) I have a form that has 14 or so combo-boxes that allow a user to open another form and filter the records based on their combo-box selections and clicking the command button. I've had this working perfectly, but have decided to add in a subform which now contains 5 of the 14 selection fields. How do I alter the stlinkcriteria to filter the entire form recordset, if a user chooses to filter by one of the subform fields as well as the parent form? Here's a sample of the VBA.. Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmQCP" stLinkCriteria = "" < this filters the form based on QCP# which is the link field between the form and subform > If Not IsNull(Me.cboQCPNum) Then If stLinkCriteria = "" Then stLinkCriteria = "[tblQCP]![QCP#] = " & Me.cboQCPNum & "" Else stLinkCriteria = stLinkCriteria & " And " & "[tblQCP]![QCP#] = " & Me.cboQCPNum & "" End If End If . . <other stLinkCriteria assignments for other combo-boxes If Not IsNull(Me.cboBat) Then <first combo-box to filter sub-form and form If stLinkCriteria = "" Then stLinkCriteria = < need to somehow compare subform & combo here> = """ & Me.cboBat & """" End If DoCmd.OpenForm stDocName, , , stLinkCriteria Any help would be most appreciated! Cheers, Rich

Re: Open form and filter form & subform using combo-boxes John Smith <johnDOTsmithATbromleyhospitalsDOTnhsDOTuk> 12/29/2008 2:05:24 PM You need to use an EXISTS sub select: stLinkCriteria = "EXISTS (SELECT 1 FROM subformtable WHERE subformcolumn = '" & Me.cboBat & "')" Substitute your correct names for subformtable and subformcolumn. Note that you will need to consider whether you need AND or OR if more than one subform criterium is selected. HTH John ################################## Don't Print - Save trees Rich_in_NZ wrote: [Quoted Text] > Hi, (Using Office 2003, but in Access 2000 format) > > I have a form that has 14 or so combo-boxes that allow a user to open > another form and filter the records based on their combo-box selections and > clicking the command button. I've had this working perfectly, but have > decided to add in a subform which now contains 5 of the 14 selection fields. > How do I alter the stlinkcriteria to filter the entire form recordset, if a > user chooses to filter by one of the subform fields as well as the parent > form? > Here's a sample of the VBA.. > Dim stDocName As String > Dim stLinkCriteria As String > stDocName = "frmQCP" > stLinkCriteria = "" > < this filters the form based on QCP# which is the link field between > the form and subform > > If Not IsNull(Me.cboQCPNum) Then > If stLinkCriteria = "" Then > stLinkCriteria = "[tblQCP]![QCP#] = " & Me.cboQCPNum & "" > Else > stLinkCriteria = stLinkCriteria & " And " & "[tblQCP]![QCP#] = " > & Me.cboQCPNum & "" > End If > End If > . > . > <other stLinkCriteria assignments for other combo-boxes > > If Not IsNull(Me.cboBat) Then <first combo-box to filter sub-form and > form > If stLinkCriteria = "" Then > stLinkCriteria = < need to somehow compare subform & combo here> > = """ & Me.cboBat & """" > End If > > DoCmd.OpenForm stDocName, , , stLinkCriteria > Any help would be most appreciated!

Re: Open form and filter form & subform using combo-boxes John Smith <johnDOTsmithATbromleyhospitalsDOTnhsDOTuk> 12/30/2008 5:39:39 PM Hope that you have not been trying this and wondering why you got the records that you did. I have just looked back at it and realise that I left out the join, the dangers of dashing off air-code! It should have said: tLinkCriteria = "EXISTS (SELECT 1 FROM subformtable WHERE subformtable.FK = mainformtable.PK AND subformcolumn = '" & Me.cboBat & "')" where FK and PK are the foreign and primary keys of the two tables. HTH John ################################## Don't Print - Save trees John Smith wrote: [Quoted Text] > You need to use an EXISTS sub select: > stLinkCriteria = "EXISTS (SELECT 1 FROM subformtable WHERE subformcolumn > = '" & Me.cboBat & "')" > Substitute your correct names for subformtable and subformcolumn. > Note that you will need to consider whether you need AND or OR if more > than one subform criterium is selected. > Don't Print - Save trees > > Rich_in_NZ wrote: >> Hi, (Using Office 2003, but in Access 2000 format) >> >> I have a form that has 14 or so combo-boxes that allow a user to open >> another form and filter the records based on their combo-box >> selections and clicking the command button. I've had this working >> perfectly, but have decided to add in a subform which now contains 5 >> of the 14 selection fields. How do I alter the stlinkcriteria to >> filter the entire form recordset, if a user chooses to filter by one >> of the subform fields as well as the parent form? >> Here's a sample of the VBA.. >> Dim stDocName As String >> Dim stLinkCriteria As String >> stDocName = "frmQCP" >> stLinkCriteria = "" >> < this filters the form based on QCP# which is the link field >> between the form and subform > >> If Not IsNull(Me.cboQCPNum) Then >> If stLinkCriteria = "" Then >> stLinkCriteria = "[tblQCP]![QCP#] = " & Me.cboQCPNum & "" >> Else >> stLinkCriteria = stLinkCriteria & " And " & >> "[tblQCP]![QCP#] = " & Me.cboQCPNum & "" >> End If >> End If >> . >> . >> <other stLinkCriteria assignments for other combo-boxes >> >> If Not IsNull(Me.cboBat) Then <first combo-box to filter >> sub-form and form If stLinkCriteria = "" Then >> stLinkCriteria = < need to somehow compare subform & combo >> here> = """ & Me.cboBat & """" >> End If >> >> DoCmd.OpenForm stDocName, , , stLinkCriteria >> Any help would be most appreciated!