By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,641 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

Requery Problem - [how to rebuild nested queries]

P: n/a
Hi all,

I have a form (*) based on a query (let's call it qryOuter). qryOuter
is based on resultset from qryInner which I am adding filters to and
rebuilding via QueryDefs.

When I then requery the form, these changes of the nested query do not
take - I have to close the form and reopen it. Is there a way to
refresh the form's rowsource i code to force it to "reconsider" the
underlying query without having to close the form?

thanks in advance
Axel
(*) to tell the truth its actually 2 continuous subforms which are
synchronized through qryInner and scroll together. So I actually
requery these 2. But I call a popup from the parent form for building
the filter then requery once I haved return back to main form.

Apr 26 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
more info:

I meant to write RecordSource not RowSource.

I have tried to delete the (sub)forms recordsource query and refreshing
querydefs but that did not help either:

sQuery = Me.RecordSource
sSQL = CurrentDb.QueryDefs(sQuery).SQL
CurrentDb.QueryDefs.delete sQuery

Set qd1 = CurrentDb.CreateQueryDef(sQuery, sSQL)
CurrentDb.QueryDefs.Refresh
Me.Filter
Me.Requery

The sSQL contains references to qryInner (which was rebuilt) but
doesn't use the new version of qryInner...

any ideas?

Apr 26 '06 #2

P: n/a
one solution I found (although its not very nice) - if anybody can come
up with a better solution please post!

within the subform I created a function that removes the recordsource
query, then I refresh querydefs, afterwards I re-Add the Recourdsource
query. I would say only the act of re-binding the recordsource gets rid
of the cached version of the query.

Public Function refreshQuery()
Dim sQuery As String

sQuery = Me.RecordSource
Me.RecordSource = ""
CurrentDb.QueryDefs.Refresh
Me.RecordSource = sQuery
Me.Requery
refreshQuery = True
End Function

Apr 26 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.