Updating/Refreshing Subforms after Changing SQL

Question

In Access 2010, I have a form, and a subform ("sfrm_HoursChart") which has a saved query ("myQuery") as its record source.

When a user manipulates the main form, I want to rebuild the SQL for the subform's underlying query, then refresh the subform.  The subform is displayed as a pivot chart, if that's important.

The code I'm using is basically this:

Private Sub setChartPeriods()

Dim qdf as QueryDef

Dim qrySQL as String

'build new SQL string

qrySQL = *new SQL string*

Set qdf = CurrentDb.QueryDefs("myQuery")

qdf.SQL = qrySQL

Me.sfrm_HoursChart.Requery

End Sub

Trouble is, nothing happens, at least not immediately.  If I close the form and reopen it (or if I open the subform as a standalone form), the subform's recordsource is updated properly using the new SQL, but it doesn't update upon using the Requery method (which looks to me to be the right thing to do!)  

I have also tried setting the focus to the subform and then running the Requery action (which I thought essentially closes and reopens the subform?) but that doesn't work either.

Answer 1

Same result, ... however...

If I put in the line 

Me.sfrm_HoursChart.SourceObject = "sfrm_HoursChart"   (  <-- the name of the form  )

then it will refresh!

I suspect that this would also work:

Me.sfrm_HoursChart.Form.RecordSource = Me sfrm_HoursChart.Form.RecordSource

Answer 2

Same result, ... however...

If I put in the line 

Me.sfrm_HoursChart.SourceObject = "sfrm_HoursChart"   (  <-- the name of the form  )

then it will refresh!

Thanks for the help.  :-)

Answer 3

Its not a property of the Subform control, but it is a property of the subform. 

Try

Me.sfrm_HoursChart.Form.RecordSource = *new SQL string*

Answer 4

Hmm.  

RecordSource is not a member of a subform object.

Answer 5

I think this should have worked, but why the hoopla. You can replace all this code by the one-liner:

Me.sfrm_HoursChart.RecordSource = *new SQL string*