Abstract: I'm using VB to recreate a query each time a user selects one of 3 options from a drop down menu, which appends the WHERE clause If they've selected anything from the combo boxes. I then am attempting to get the information displayed on the form to refresh thereby filtering what is displayed in the table based on user input.

1) Dynamically created query using VB.

Private Sub BuildQuery()
' This sub routine will redefine the subQryAllJobsQuery based on input from
' the user on the Management tab.

Dim strQryName As String
Dim strSql As String            ' Main SQL SELECT statement
Dim strWhere As String          ' Optional WHERE clause
Dim qryDef As DAO.QueryDef
Dim dbs As DAO.Database

strQryName = "qryAllOpenJobs"
strSql = "SELECT * FROM tblOpenJobs"
Set dbs = CurrentDb

' In case the query already exists we should deleted it
' so that we can rebuild it.  The ObjectExists() function
' calls a public function in GlobalVariables module.
If ObjectExists("Query", strQryName) Then
    DoCmd.DeleteObject acQuery, strQryName
End If

' Check to see if anything was selected from the Shift
' Drop down menu.  If so, begin the where clause.
If Not IsNull(Me.cboShift.Value) Then
    strWhere = "WHERE tblOpenJobs.[Shift] = '" & Me.cboShift.Value & "'"
End If

' Check to see if anything was selected from the Department
' drop down menu.  If so, append or begin the where clause.
If Not IsNull(Me.cboDepartment.Value) Then
    If IsNull(strWhere) Then
        strWhere = strWhere & " AND tblOpenJobs.[Department] = '" & Me.cboDepartment.Value & "'"
    Else
        strWhere = "WHERE tblOpenJobs.[Department] = '" & Me.cboDepartment.Value & "'"
    End If
End If

' Check to see if anything was selected from the Date
' field.  If so, append or begin the Where clause.
If Not IsNull(Me.txtDate.Value) Then
    If Not IsNull(strWhere) Then
        strWhere = strWhere & " AND tblOpenJobs.[Date] = '" & Me.txtDate.Value & "'"
    Else
        strWhere = "WHERE tblOpenJobs.[Date] = '" & Me.txtDate.Value & "'"
    End If
End If

' Concatenate the Select and the Where clause together
' unless all three parameters are null, in which case return
' just the plain select statement.
If IsNull(Me.cboShift.Value) And IsNull(Me.cboDepartment.Value) And IsNull(Me.txtDate.Value) Then
    Set qryDef = dbs.CreateQueryDef(strQryName, strSql)
Else
    strSql = strSql & " " & strWhere
    Set qryDef = dbs.CreateQueryDef(strQryName, strSql)
End If

End Sub

2) Main Form where the user selects items from combo boxes.

picture of the main form and sub form http://i48.tinypic.com/25pjw2a.png

3) Subform pointed at the query created in step 1.

Chain of events: 1) User selects item from drop down list on the main form. 2) Old query is deleted, new query is generated (same name). 3) Subform pointed at query does not update, but if you open the query by itself the correct results are displayed.

Name of the Query: qryAllOpenJobs name of the subform: subQryAllOpenJobs Also, the Row Source of subQryAllOpenJobs = qryAllOpenJobs Name of the main form: frmManagement