Filter Subform in Tab Control

[Guest]

Hi all, I need assistance in filtering a subform in a tab control. Any help
is appreciated (TIA). The relevant info is below:

Tables:
1. Projects Table
pk: ProjectID

2.Servers Table
pk:ServerID
fk: ProjectID

Forms:
1. Projects Form - Two Tab Control
- Tab 1: Projects Information
- Tab 2: Servers Information (subform linked to Projects form via
ProjectID), contains two additional nested subforms

2. Lookup/Search Form
- Project Name Combo box
- Design SR Combo box
- Deploy SR Combo box
- Server Name Combo box (need to add this one)

Functionality:
1. The user uses the lookup form to search for projects/servers by the
available combo boxes
2. The search criteria is used to construct a SQL statement used to filter
the main Projects form
3. The Projects form is opened based on the filter criteria and the Search
form is closed

Question:
My question is regarding adding additional search criteria that is contained
in the Servers subform, i.e, Server Name. I would like to add a 4th combo
box listing the server names to allow the user to search using this
information in addition to the other criteria. Is this possible based on the
current design? If so, please provide guidance on syntax and reference.

Relevant Code:
Private Sub cmdLookup_Click()

Dim strProjectID As String
Dim strDesignSR As String
Dim strDeploySR As String
Dim strWhere As String

'Populate the project name lookup
'
If IsNull(cboProjectName) Then
strProjectID = "Like '*'"
Else
strProjectID = "=" & Me.cboProjectName.Value & ""
End If

'Populate the design SR lookup
'
If IsNull(cboDesignSR) Then
strDesignSR = "Like '*'"
Else
strDesignSR = "='" & Me.cboDesignSR.Value & "'"
End If

'Populate the deploy SR
'
If IsNull(cboDeploySR) Then
strDeploySR = "Like '*'"
Else
strDeploySR = "='" & Me.cboDeploySR.Value & "'"
End If

'Populate the where clause
'
strWhere = "lngProjectID " & strProjectID & _
" AND strDesignSR " & strDesignSR & _
" AND strDeploySR " & strDeploySR

Debug.Print strWhere

Select Case fraOptions.Value
Case 1
'Open the form with the WHERE clause populated
'
DoCmd.OpenForm "frmProjectInformation", acNormal, , strWhere,
acFormEdit, acWindowNormal

'Close the lookup form
'
DoCmd.Close acForm, "frmProjectServerLookup", acSaveNo
Case 2
'Open the report with the WHERE clause populated
'
DoCmd.OpenReport "rptProjects", acViewPreview, , strWhere,
acWindowNormal

'Close the lookup form
'
DoCmd.Close acForm, "frmProjectServerLookup", acSaveNo
End Select

End Sub

 

[Allen Browne]

The form's Filter can contain anything that could go in the WHERE clause of
a query. That includes a subquery.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
In essence, a subquery is a complete SELECT query inside another query.

The additional string you are after will end up being something like this:
Dim strServer As String
If Not IsNull(Me.cboServerID) Then
strServer = " EXISTS (SELECT ServerID FROM Servers WHERE
(Servers.ServerID = " & Me.cboServerID & ") AND (Servers.ProjectID =
Projects.ProjectID))"
End If

An alternative approach is to change the RecordSource of the main form to an
INNER JOIN statement so that it is effectively filtered. For an explanation
of how to implement that approach, in combination with a filter, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

 

[Guest]

Thanks for the info Allen. This looks exactly like what I need.