OK, let' say that you have a selection Form where you enter in the criteria. Let can this Form "frmSelect",
For your specific example, let's create an unbound Combo Box and named it "cboDesc".
For the Form we want to return (based on our criteria selection), let's call that "frmEntry". And let's say the source of that form is a query name "qryEntry".
So, on "frmSelect", let's also have a command button that you click after you enter your criteria when you want to return your entry Form. Let's call this "cmdOpenForm".
On the Click event of "cmdOpenForm", your VBA code would look something like this:
Code:
Private Sub cmdOpenForm_Click()
Dim mySQL as String
' Set default part of SQL string without criteria
mySQL= "SELECT Cluster_Dept.ID, Clusters.Cluster_Desc, Department.Dept_Desc, Source.Day_Month_Year, Source.Original_Source, Source.Headline, " & _
"Source.Issue, Source.Analysis, Source.Action, Source.Flag "& _
"FROM Source INNER JOIN (Department INNER JOIN (Clusters INNER JOIN Cluster_Dept ON Clusters.Cluster_ID = Cluster_Dept.Cluster_ID) " & _
"ON Department.Dept_ID = Cluster_Dept.Dept_ID) ON Source.ID = Cluster_Dept.ID "
' Check for criteria and add if necessary
If Len(cboDesc & "X")>1 Then
mySQL=mySQL & "WHERE Clusters.Cluster_Desc=" & Chr(34) & cboDesc & Chr(34) & ";"
Else
mySQL=mySQL & ";"
End If
' Assign SQL code to query that the entry form uses
CurrentDQ.QueryDefs("qryEntry").SQL=mySQL
' Open the entry Form
DoCmd.OpenForm "frmEntry"
End Sub