VB Command to Update SubForm after Query SQL change

[orbojeff]

I'm using a VB Command to change the SQL statement on my Query
qry_SEARCH
On the main form, frm_SEARCH, I have a sub form sfrm_SEARCH bound to
this Query

If I toggle between Design and From View the Sub Form will refresh
after the SQL change?
What command can I use to refresh this?

THanks
Jeff

 

[[MVP] S.Clark]

You would think that it would be Refresh, but it's Requery, instead.

Me.Requery
Me.SfrmName.Requery
Comboboxname.Requery
etc.

 

[Marshall Barton]

I'm using a VB Command to change the SQL statement on my Query
qry_SEARCH
On the main form, frm_SEARCH, I have a sub form sfrm_SEARCH bound to
this Query

If I toggle between Design and From View the Sub Form will refresh
after the SQL change?
What command can I use to refresh this?

Me.subformcontrol.Form.Requery

But, why are you modifying a querydef's SQL property when
you could just set the subform's Record Source property to
the same SQL statement?

 

[orbojeff]

Marshall

That sounds like a good idea to modify the Sub Forms Record Source.
I'm using the following Code to pass the SQL Statement
CurrentDb.QueryDefs("qry_SEARCH").SQL = new_SQL
What would i need to change this to to modify the Sub Form Record
Source?

Thanks
Jeff

 

[Marshall Barton]

That sounds like a good idea to modify the Sub Forms Record Source.
I'm using the following Code to pass the SQL Statement
CurrentDb.QueryDefs("qry_SEARCH").SQL = new_SQL
What would i need to change this to to modify the Sub Form Record
Source?

If the code is in the main form:

Me.subformcontrol.Form.RecordSource = new_SQL

If the code is in the subform itself:

Me.RecordSource = new_SQL

Note that setting a form's RecordSource automatically
requeries the form, so your original question no longer
applies.