Open form and filter form & subform using combo-boxes

[Rich_in_NZ]

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

 

[John Smith]

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

 

[John Smith]

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

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

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!