Refresh pivot chart in subform after changing the SQL of the subform's query

[bebubo]

Hi!

I'm pretty sure I'm overcomplicating things (I'm new to Access), but here is what I have so far:

I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.

Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.

Help!

 

[bebubo]

Almost forgot, I'm using Access 2010, the db is split into frontend and backend.

 

[JHB]

Requiring the subform should do it, but ...
Post your database with some sample data + name of the form with the problem, zip it because you haven't post 10 post yet.

 

[bebubo]

Requiring the subform should do it, but ...
Post your database with some sample data + name of the form with the problem, zip it because you haven't post 10 post yet.

Here it is. I made a new db (because it was simpler than having to delete 90% of the current one) and managed to recreate the problem. (And also a new one, the dropdowns are not showing values, even though they are there and are taken as WHERE filters in the query correctly.)

The frm_main has the dropdown, which then changes the SQL of Qry_1, which should then be used to update the subfrm_1.
What am I missing here?

 

[JHB]

Add the code line marked with red.

   ... 
       qryD.SQL = strSQL1 & strSQL2 & strSQL3
     
     'here comes the requery of the pivot chart subform......
    
    [B][COLOR=Red]Me.subfrm_1.SourceObject = "Subfrm_1"[/COLOR][/B] ...

In your combobox, the first column is set to 0cm, change it to 2cm then the list appear.

 

[bebubo]

Thanks, both are solved!

 

[aguest]

Hi,
i'm having the same issue. I've tried the fix mentioned above but it's not doing it. Acess 2010 doen't seem to like the last line. It gives me an error saying the form name doesn't followin acces object naming rules.

Help greatly appreciated.

Sub update_subform()
Dim dbsCurrent As Database
Dim qryD As QueryDef
Dim strSQL1 As String, strSQL2 As String, strSQL3 As String
Dim mytmp As String
Dim proc As String, myot As String

stsurname = Me.cboStudent.Column(1)
Set dbsCurrent = CurrentDb
Set qryD = dbsCurrent.QueryDefs("qry_AllHouse_LRT by Student")
strsql = "SELECT [BehaviourData].AcademicYear,[BehaviourData].termDesc, [BehaviourData].Surname, [BehaviourData].LegalForename," & _
"iif([BehaviourData].Type = 'LRT A - Homework', 'LRT A - Homework', 'LRT A - Behaviour') AS LRT_Type," & _
"Count(*) AS Total_LRTs " & _
"FROM [BehaviourData] " & _
"Where [BehaviourData].Surname = '" & stsurname & "'" & _
" And [BehaviourData].LegalForename = '" & Me.txtForename & "'" & _
" Group By [BehaviourData].AcademicYear,[BehaviourData].termDesc, [BehaviourData].Surname, [BehaviourData].LegalForename," & _
"iif([BehaviourData].Type = 'LRT A - Homework', 'LRT A - Homework', 'LRT A - Behaviour')"


qryD.SQL = strsql

'here comes the requery of the pivot chart subform......
Me.[frm_AllHouse_LRT by Student].SourceObject = "[frm_AllHouse_LRT by Student]"

End Sub

 

[bebubo]

Try ... = "frm_AllHouse_LRT by Student"