Dynamic report in MS access will not update with source query

Question

So I have a database of mechanical components. I have a search form in the database that allows the user to apply filters to a query (through the use of 5 text/combo boxes and an "apply filters" button). This query will contain the components that made it past the filter, and some details about those components. Also on the search form, I have a sub-report that is bound to the query.

My plan is to have the user filter though the data using the controls on the form. As the user filters out components, the report should update to display what components pass the new query. They would then select the component they are interested in by clicking on a record in the report, and that component ID would be passed into VBA to do more stuff.

My problem is that the report bound to the query will not update as the query does. I have tried using DoCmd.Requery and Me.Form.Refresh on the button click event, but haven't gotten anywhere. It only works if I close the form, then re-open it.

How do I get my bound report to change dynamically as the user modifies the SQL of its source query?

Here's my click event code:

Private Sub cmdApplyFilters_Click()
Call updateSearchFilters
Me.Requery
Me.Form.Refresh
End Sub

Here's my sub to update the query:

Public Sub updateSearchFilters()
Dim strSQL As String
Dim searchQryDef As DAO.QueryDef

strSQL = searchSQLstrBuilder(Forms("frmSearch").tboCompID, Forms("frmSearch").tboDesc, Forms("frmSearch").cboType, Forms("frmSearch").cboVendor, Forms("frmSearch").tboVendPN)
Set searchQryDef = CurrentDb.QueryDefs("qrySearchRes")
searchQryDef.SQL = strSQL
End Sub

Here's my SQL string builder:

Public Function searchSQLstrBuilder(tboCompID As TextBox, tboDesc As TextBox, cboType As ComboBox, cboVend As ComboBox, tboVPN As TextBox) As String
searchSQLstrBuilder = "SELECT tblDocs.compID, tblComponents.Desc, tblComponents.type, tblComponents.vend, tblComponents.vendorPN "
searchSQLstrBuilder = searchSQLstrBuilder & "FROM tblComponents INNER JOIN tblDocs ON tblComponents.numComp = tblDocs.numComp "

Dim strCompID, strDesc, strType, strVend, strVPN As String
strCompID = tboCompID.Value
strDesc = tboDesc.Value
strType = cboType.Value
strVend = cboVend.Value
If IsNull(tboVPN.Value) Then
    strVPN = ""
    searchSQLstrBuilder = searchSQLstrBuilder & "WHERE (((tblDocs.compID) Like '*" & strCompID & "*') AND ((tblComponents.Desc) Like '*" & strDesc & "*') AND ((tblComponents.type) Like '*" & strType & "*') AND ((tblComponents.vend) Like '*" & strVend & "*') AND ([vendorPN] Is Null Or (tblComponents.vendorPN) Like '*" & strVPN & "*'));"
Else
    strVPN = tboVPN.Value
    searchSQLstrBuilder = searchSQLstrBuilder & "WHERE (((tblDocs.compID) Like '*" & strCompID & "*') AND ((tblComponents.Desc) Like '*" & strDesc & "*') AND ((tblComponents.type) Like '*" & strType & "*') AND ((tblComponents.vend) Like '*" & strVend & "*') AND ((tblComponents.vendorPN) Like '*" & strVPN & "*'));"
End If

End Function

Thanks in advance! Let me know if there's more information I can provide.

Johnny Bones Johnny Bones · Accepted Answer · 2015-10-01T18:50:12

I don't fully understand this problem, because you can't have a button on a report. They're not interactive. So, I'm assuming you have a button on a form which opens a report.

What you might try is updating the RecordSource of the report. After the updateSearchFilters code runs, at the very end of that function, add:

Reports!rptMyReport.RecordSource = "Select * from qrySearchRes"

Alternatively, you could have a SQL string as a RecordSource and pass variables in using OpenArgs:

Dim strMyValue As String
strMyValue = "This Is A TEST!"
DoCmd.OpenReport "rptMyReport", acViewPreview, , , acWindowNormal, strMyValue

and then in the report you would add this on Form_Load:

Dim strMyValue As String
strMyValue = Me.OpenArgs

Then you can use strMyValue as a parameter in your RecordSource.

It is also possible to pass multiple values via OpenArgs, but it's a little more tricky. You can use a pipe delimiter ("|") to separate your values, but when you open it you'll have to parse out the values on the other side using some VBA.

Dynamic report in MS access will not update with source query

2 Answers