Access 2007 VBA & SQL - Update a Subform pointed at a dynamically created query
- by Lucretius
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
