Search code examples
ms-accessvbasubform

Subform column and data change at runtime not refreshing on Form

I have Main Form in which There are Two Comobobx.

1. Type of Work (C_Art: Name of Combobox)

Repratur
Maintenance
Optimierung


2. Year (C_Year: Name of Combobox)

2018
2019

On the Main Form There is Bar chart which works perfectly and a subform with a datasheet view.

Overall it looks like this.

My Problem is when I select the Year 2019 the Subform view with new header 2019-Jan 2019-Feb ......2019-Dec didn't update and the row give me error as #Name?

The Graph is generating correctly without any Problem.

I am changing the query at runtime as below.

Dim count_Status As String
Dim G_Stoerung As String


'Change the query for Graph
count_Status = "TRANSFORM Count(History_query.[LFD_NR]) AS CountOfLFD_NR " & _
" SELECT History_query.[Anlage] " & _
" FROM History_query " & _
" WHERE History_query.[Anlage] <>Null AND History_query.[WAS]= '" & Me.C_Art & "' AND History_query.[Year]= '" & Me.C_Year & "' " & _
" GROUP BY History_query.[Anlage] " & _
" PIVOT History_query.[Status] "

CurrentDb.QueryDefs("Count_Status_Anlage").sql = count_Status

'Updating the Graph with a new Data
Me.Graph9.Requery
Me!Graph9.ChartTitle.Text = Me.C_Art



'Chnage the query of subform
G_Stoerung = "TRANSFORM Count([History_query].WAS) AS CountOfWAS " & _
" SELECT [History_query].Status " & _
" FROM History_query " & _
" WHERE ((([History_query].WAS)= '" & Me.C_Art & "' ) AND [History_query].Status IN ('Offen','beobachten','In bearbeitung','erledigt')  ) " & _
" GROUP BY Status " & _
" Order by (IIf([Status]='Offen',1,IIf([Status]='beobachten',2,IIf([Status]='In bearbeitung',3,IIf([Status]='erledigt',4))))) " & _
" PIVOT Format([Datum],'yyyy-mmm') IN ('" & Me.C_Year & "-Jan','" & Me.C_Year & "-Feb','" & Me.C_Year & "-Mar','" & Me.C_Year & "-Apr','" & Me.C_Year & "-May','" & Me.C_Year & "-Jun','" & Me.C_Year & "-Jul','" & Me.C_Year & "-Aug','" & Me.C_Year & "-Sep','" & Me.C_Year & "-Oct','" & Me.C_Year & "-Nov','" & Me.C_Year & "-Dec') "

CurrentDb.QueryDefs("G_Stoerung_query").sql = G_Stoerung

'Updating the subform with new Data 
Me.G_Stoerung_Sub.Form.RecordSource = G_Stoerung
Me.G_Stoerung_Sub.Form.Requery
Solution

  • The #NAME? error would be because textboxes are no longer bound to fields that exist. Make the field names generic and apply filter parameter for year. Or set the subform container SourceObject property to query object instead of form

    Edit

    G_Stoerung_Sub.SourceObject = ""
G_Stoerung_Sub.SourceObject = "Query.G_Stoerung_query"

    updating the source object gives desired result.