Troubles Referencing Subform from Mainform; Requeries

[AvantGuy]

​
I have a Mainform and one Subform. They both have the same query as their RecordSource. I dynamically update the SQL of that query via an event in the main form -- the AfterUpdate() of a combo box.

I'm having a dickens of a time getting the Subform to Requery after that event fires. When I exit then relaunch the Mainform, the Subform properly reflects the query results from the previous run. That's because when it first loads, it's seeing the as yet unmodified-by-Mainform SQL of the query.

I've tried all the methods of Subform refresh I could find. The main methods recommended in various fora yield runtime errors (they compile w/o error). They blow up on any code that references the sub from the main. These runtime errors due to references seem like a hint into my problem.

I could use some help. Thanks, --Bob.

 

[Pat Hartman]

It doesn't make any sense that the main form and subform use the same query as a RecordSource. Why?

 

[AvantGuy]

It doesn't make any sense that the main form and subform use the same query as a RecordSource. Why?

Well Pat, I was not sure what to place there. The Mainform's RecordSource seems to have no effect. The only thing on the Mainform is a combo box. It does the replacement of the query's SQL (the query that is the RecordSource for the Subform) and then triggers a Subform refresh. The refresh never happens no matter what method of Subform reference I've tried.

 

[arnelgp]

What is yiur code fir the combo's afterupdate event?

 

[AvantGuy]

Thanks all. I'll post again tomorrow upon returning to my workstation. In these posts I prolifically used "refresh" but meant "requery" in each case. --Bob

 

[AvantGuy]

What is yiur code fir the combo's afterupdate event?

Thank you, arne lgp. Sorry, it's a bit convoluted...

Private Sub cboSiteList_AfterUpdate()
    g_intFromParentForm = Me.cboSiteList.Value
    BuildFormQuery
End Sub

Function BuildFormQuery()
    Dim strSql As String
    strSql = "SELECT * FROM tblSitesDetail WHERE " & _
             "tblSitesDetail.SiteID = " & g_intFromParentForm
    CurrentDb.QueryDefs("qryDetailsFromIdx").SQL = strSql
End Function

Various calls to Requery there were like no-ops or crashed at runtime. So I moved the Requery from the combo's AfterUpdate to a button so as to ensure I could invoke Requery manually after subform loads

Private Sub btnRefreshCommand_Click()
    RefreshBothForms
End Sub

Private Sub RefreshBothForms()
    ' requerySubForm Me, "frmSubLimResults"
    ' requery the SUB form
    RequerySubForm Me, "frmSubLimResultsMaster"
End Sub

Public Function RequerySubForm(frm As Form, subformList As String)
    Dim v As Variant
    Dim strFName As String
    Dim i As Integer
    Dim ctl As Control
    'split the list into an array
    v = Split(subformList, ";")
    'loop the array and get a control reference for each
    'if there's only one sub, just do that one, otherwise run them all
    Set ctl = frm.Controls(CStr(v(0)))
    'ctl is now Sub1
    
    If UBound(v) > 0 Then
        For i = 1 To UBound(v)
            'get next subform
            Set ctl = ctl.Form.Controls(CStr(v(i)))
        Next i
    End If
    
    'what you're left with should be a control reference to the last subform
    strFName = ctl.Form.Name
    '
    MsgBox "The form name calculated is " & [strFName]
    
    'ctl.Form.Controls(CStr(v(i))).Requery
     ctl.Form.Requery
    'ctl.Form.Refresh
End Function

Note that

strFName = ctl.Form.Name

yields the correct Subform name so I assume I finally have gotten a correct reference to it. But a trap I put in the Subform's requery event handler...

Private Sub Form_Query()
    MsgBox "On SUBFORM OnQuery !! S U B"
End Sub

...never fired.

How do I get the Requery to happen?

 

[Pat Hartman]

If the main form is simply used for searching, then it should not be bound. Remove the RecordSource and remove the master/child links if necessary. If controls are bound, you will need to fix or remove those also.

 

[arnelgp]

if you only have one subform, then the two subs will be enough to get
what you want:

Private Sub cboSiteList_AfterUpdate()
	g_intFromParentForm = Me.cboSiteList.Value
	BuildFormQuery
End Sub

Private Function BuildFormQuery()
	Dim strSql As String
	strSql = "SELECT * FROM tblSitesDetail Where tblSiteDetail.SiteID=" & _
	g_intFromParentForm
	
	'there is no need to change the Query
	'set the recordsource of the subform directly
	'
	'you don't also need to requery when you change recordsource
	Me![yourSubFormName].Form.RecordSource = strSql
End Function

 

[AvantGuy]

if you only have one subform, then the two subs will be enough to get
what you want:

Private Sub cboSiteList_AfterUpdate()
    g_intFromParentForm = Me.cboSiteList.Value
    BuildFormQuery
End Sub

Private Function BuildFormQuery()
    Dim strSql As String
    strSql = "SELECT * FROM tblSitesDetail Where tblSiteDetail.SiteID=" & _
    g_intFromParentForm
    
    'there is no need to change the Query
    'set the recordsource of the subform directly
    '
    'you don't also need to requery when you change recordsource
    Me![yourSubFormName].Form.RecordSource = strSql
End Function

Yes, just the Mainform and one Subform (in continuous mode).

Anyway, good sir, updating the Subform's RecordSource was like balm for the aching, salve for a fester. IOW, this solved my issue. Thank you so much Mr. arnelgp! Uncanny how that triggers the magic refresh!

 

[arnelgp]

You are welcome sir.

 

[Pat Hartman]

I always prefer no code solutions when they are available.

 

[AvantGuy]

Indeed.

...well, one line doesn't count, does it.​

 

[Pat Hartman]

I was referring to the fix which could have been accomplished by changing property settings. The Requery would still be necessary to get the subform to find the desired records.