Subform not requerying after changing the Underlying query

[Guest]

Hi All,

I have a button that changes the underyling query used by a subform,
depending of the values in a set of controls. Updated using QueryDef

The query change is ok (if I close and reopen the form it is fine), just the
Me.SubName.Form.Requery command does not do anything. I assume this is
because I have changed the query, so I can't "requery".

Is there a way to get around this?

Many thanks.

 

[Allen Browne]

Would it be easier just to set the RecordSource of the subform to the query
statement?

Example:
Me.[Sub1].Form.RecordSource = "SELECT ...

 

[shaderpixel]

Sorry for reactivating this old thread as I am facing a similar issue. I have a main form that contains a search box with a search button. When a keyword is entered and onclick, the button recreates the query for a subform. The subform is in datasheet view and ideally, should display the new data returned from the query (in the sense a keyword search). In the subform, the query is attached to the record set. My problem is that I am having a hard time figuring out how to refresh the data in the subform datasheet view when the button is clicked. Here is a sample code from my main form:

Set qryDef1 = dbs.CreateQueryDef("qry_oralhealthsub") 'reset query definition

stWhereCondition1 = "tbl_goals.goal_desc LIKE ""*" & Me.searchtext & "*""" & " OR tbl_strategies.strat_desc LIKE ""*" & Me.searchtext & "*"""

testsql1 = "SELECT tbl_categories.cat_id, tbl_goals.goal_desc, tbl_strategies.strat_desc" & _
" FROM (tbl_categories INNER JOIN tbl_goals ON tbl_categories.cat_id = tbl_goals.cat_id) INNER JOIN tbl_strategies ON tbl_goals.goal_id = tbl_strategies.goal_id" & _
" WHERE (((tbl_categories.cat_id)=1))AND (" & stWhereCondition1 & ");"


And I tried this:
Forms!frm_main!frm_oralhealth_sub.Form.Requery

Not sure my syntax is correct. Hope someone can help me out! Thanks

 

[shaderpixel]

Nevermind, I found the answer. Upon update of query statement in the main form, set the subform record source to the query before requerying. e.g.

Forms![frm_main]![frm_oralhealth_sub].Form.RecordSource = "qry_oralhealthsub"
Forms![frm_main]![frm_oralhealth_sub].Requery