refresh pivot in form

[aguest]

Hi,
I posted this in another thread but realised it might not been seen as the other one said Solved!.
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

 

[JHB]

Post your database with some sample data, (zip it), + the name of the form with the problem!

 

[aguest]

Hi,
I've attached a copy of the relevant table with a couple examples on there.

When you open the sub form on it's own it refreshes but won't as a subform.

All help appreciated.

 

[JHB]

Change the code line to the below, (you've set it to the query+you've [] around the name, database attached):

   Me.frm_AllHouse_LRT_by_Student.SourceObject = "frm_AllHouse_LRT by Student"

 

[aguest]

Thank you so much. Thats worked a treat.

 

[JHB]

You're welcome.