MS-Access Howto refresh a form when its RecordSource Query is updated by VBA

Question

I have a form whose recordsource is a Query Called qryProdSearch whose SQL is generated using VBA based on search parameters as defined below:

What the code below does is that is splits the text search box into multiple items in an array to allow me to search for all of them simultaneously like Google Search.

iSQL = "SELECT tblProduct.ProductID, tblProduct.ProdDescription, tblProduct.Brand, tblProduct.ShortCode, tblProduct.BarCode, tblProduct.MRP, tblProduct.Discount, tblProduct.TaxRate, tblProduct.LandingCost, tblProduct.MarketPrice From tblProduct WHERE ((([ShortCode] & ' ' & [Brand] & ' ' & [ProdDescription] & ' ' & [BarCode]) Like '*"
If Nz(Me.txtSearch, "") = "" Then
    iSQL = "SELECT tblProduct.ProductID, tblProduct.ProdDescription, tblProduct.Brand, tblProduct.ShortCode, tblProduct.BarCode, tblProduct.MRP, tblProduct.Discount, tblProduct.TaxRate, tblProduct.LandingCost, tblProduct.MarketPrice From tblProduct"
    Exit Sub
Else
    iArray = Split(Me.txtSearch, " ")
End If

If UBound(iArray) = 0 Then
        iSQL = iSQL & Me.txtSearch & "*'));"
    Else
        iSQL = iSQL & iArray(0) & "*'"
        For i = LBound(iArray) To UBound(iArray)
            iSQL = iSQL & " And  ([ShortCode] & ' ' & [Brand] & ' ' & [ProdDescription] & ' ' & [BarCode]) Like '*" & iArray(i) & "*'"
        Next i
        iSQL = iSQL & "));"
End If

CurrentDb.QueryDefs("qryProdSearch").SQL = iSQL

This is working fine. However the subform that is using this query is not refreshing no matter what I try till I reopen the form. I have tried the following:

Me.sfrmProdSearch.Requery
Me.sfrmProdSearch.Form.Requery
Me.sfrmProdSearch.Form.Refresh
Me.Form.Requery
Me.Form.Refresh

Any Suggestions would be welcome. Bottomline I do not want to reopen the form but i want it to be refreshed such that the subform shows the filtered content

If I alternatively use the Searchbox directly in the RecordSource of the SubForm and refresh the subform it works fine. example below. But the limitation is that i cannot split the words and make it search using all the words independent of their position.

WHERE ((([ShortCode] & ' ' & [Brand] & ' ' & [ProdDescription] & ' ' & [BarCode]) Like "*" & [Forms]![frmProdSearch]![txtSearch] & "*"));

Gustav Gustav · Accepted Answer · 2015-03-03T11:34:15

You may need to refresh the QueryDefs:

CurrentDb.QueryDefs("qryProdSearch").SQL = iSQL
CurrentDb.QueryDefs.Refresh

Then:

Me!sfrmProdSearch.Form.Requery

MS-Access Howto refresh a form when its RecordSource Query is updated by VBA

1 Answers