VBA to update Subform Data Source

[SpentGeezer]

Greetings and Salutations,

Access 2003. I have subform who's datasource is a query. I have a button on the parent form that deletes the query def and recreates it. THis all works fine, but the new query definition is not displayed in the subform (even though the datasource query has changed). How do I get the subform to display the updated query?

Basically this button removes records that have "NA" in their answer field. If clicked again they become visible, if clicked again they are hidden etc.. It all works, just the subform does not display the changes......

Private Sub btn_View_Click()

If InStr(1, lbl_SQL.Caption, "NA") > 0 Then
    QryStat = Replace(lbl_SQL.Caption, "((Not (tbl_Answers.txt_Answer)=""NA"") AND  ((", "(((")
    'QryStat = " SELECT tbl_Answers.ID, tbl_Questions.Q_Link, tbl_Questions.InspectionPoints AS Question, tbl_Answers.txt_Answer AS [Yes or No?], tbl_Answers.mem_Comments AS Comments"
    'QryStat = QryStat + " FROM (tbl_Answers INNER JOIN tbl_Properties ON tbl_Answers.Blding_ID = tbl_Properties.ID) INNER JOIN tbl_Questions ON tbl_Answers.Q_ID = tbl_Questions.Q_ID"
    'QryStat = QryStat + " WHERE ((Not (tbl_Answers.txt_Answer)=""NA"") AND  ((tbl_Properties.BUILDINGS) = """ & theBuilding & """) And ((tbl_Properties.[St No]) = """ & theStreetNumber & """) And ((tbl_Properties.Address) = """ & theStreetName & """) And ((tbl_Properties.SUBURB) = """ & thesuburb & """))"
    'QryStat = QryStat + " ORDER BY tbl_Questions.Q_ID;"
Else
    QryStat = Replace(lbl_SQL.Caption, "WHERE (((tbl_Properties.BUILDINGS)", "WHERE ((Not (tbl_Answers.txt_Answer)=""NA"") AND  ((tbl_Properties.BUILDINGS)")
    'QryStat = " SELECT tbl_Answers.ID, tbl_Questions.Q_Link, tbl_Questions.InspectionPoints AS Question, tbl_Answers.txt_Answer AS [Yes or No?], tbl_Answers.mem_Comments AS Comments"
    'QryStat = QryStat + " FROM (tbl_Answers INNER JOIN tbl_Properties ON tbl_Answers.Blding_ID = tbl_Properties.ID) INNER JOIN tbl_Questions ON tbl_Answers.Q_ID = tbl_Questions.Q_ID"
    'QryStat = QryStat + " WHERE (((tbl_Properties.BUILDINGS) = """ & theBuilding & """) And ((tbl_Properties.[St No]) = """ & theStreetNumber & """) And ((tbl_Properties.Address) = """ & theStreetName & """) And ((tbl_Properties.SUBURB) = """ & thesuburb & """))"
    'QryStat = QryStat + " ORDER BY tbl_Questions.Q_ID;"
End If

strQueryName = "qry_main"
For Each QryDef In CurrentDb.QueryDefs
    If QryDef.Name = strQueryName Then
        CurrentDb.QueryDefs.Delete (strQueryName)
    End If
Next QryDef

Set QryDef = CurrentDb.CreateQueryDef(strQueryName, QryStat)
Me.qry_main_subform.Requery
lbl_SQL.Caption = QryStat

End Sub

 

[the_net_2.0]

what happens? does the subform box seem to go blank? or appear as a plain white box? if it does, try requerying the main form. I would guess that would work.

are you refreshing the database window as well, after you recreate a querydef?? it won't appear in the window until you double click the mouse in it, or refresh it through code