Access: update query in sub form

Question

Instead of using DoCmd.OpenQuery "qrySearch", , acReadOnly to view the results of a query I'm looking at using a form instead to help with presentation, including a couple of command buttons for things like export to Excel etc. Note that the query can display a variable number of fields depending on the user's chosen criteria.

To do this I created 2 forms: frmResults and frmSub

frmSub was placed within frmResults by dragging frmSub from the Forms tab into frmResults (opened in Design view).

Under Properties of the frmSub:-

Source Object was changed from frmSub to Query.qrySearch.
The Name set at frmResultsSub

Since the original frmSub is now no longer used, I've deleted it (at least, with my limited Access experience I'm assuming it's safe to do so since there appears no effect on the query getting displayed in frmResults - the form displays nicely and the fields and field numbers vary according to the search criteria.

Question: If the user performs another search, and frmResults is currently open, in order to update the results I have to close frmResults and Open it again. This does work but I'm thinking it's not the recommended way - is there any way to refresh frmResults while it's still open? I've tried various permutations of

Forms!frmResults!frmResultsSub.Requery / .Refresh

from behind the Search form itself but nothing works.

[ms Access 2003 compliance still required]

EDIT: based on HansUp and Alexander's 1st replies...

Alexander: the .Requery (/frmResults display) takes place close to the end of the VBA behind the Search button on form used to take in the user search criteria (frmSearch, a separate form not detailed above)...

If CurrentProject.AllForms("frmResults").IsLoaded Then
    Forms!frmResults!frmResultsSub.Requery
Else: DoCmd.OpenForm "frmResults"
End If

--> it's the Forms!frmResults!frmResultsSub.Requery that is not updating for a new user search on the currently opened frmResults form.

HansUp: replacing the above line

Forms!frmResults!frmResultsSub.Requery

to

Forms!frmResults!frmResultsSub.SourceObject = "Query.qrySearch"

...did the trick, and works well - all new searches on an already open frmResults are updated without having to re-open the form. But I'm confused!...I though I already set SourceObject of the subform to the same value as detailed above (under the subform's properties in design view) - why does Access not honour this setting?

Pls post the complete context of where you put your .Requery statement. Ususally a Me.Requery from within the form or a Forms!frmResults.Requery should work. If you want to requery the subform only, you might need to use: Forms!frmResults!frmResultsSub.Form.Requery since frmResultsSub only adresses the field in which the sub form resides on the main form. — Alexander Remesch
Thanks Alexander, please see my EDIT: comments in my original post. — Jim

HansUp HansUp · Accepted Answer · 2016-01-31T15:09:17

In your example code ...

Forms!frmResults!frmResultsSub.Requery

... frmResultsSub is a subform control which contains a query instead of a form. In other words, its SourceObject property is set to "Query.qrySearch".

In that situation, Requery of the subform control does not recognize changes to the query design.

Setting the subform's SourceObject property to its original value is enough to make Access recognize the query design change ...

Forms!frmResults!frmResultsSub.SourceObject = "Query.qrySearch"

Access: update query in sub form

1 Answers