Troubles Referencing Subform from Mainform; Requeries (1 Viewer)

AvantGuy

neophyte
Local time
Today, 10:48
Joined
Mar 25, 2018
Messages
22

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

Super Moderator
Staff member
Local time
Today, 12:48
Joined
Feb 19, 2002
Messages
42,971
It doesn't make any sense that the main form and subform use the same query as a RecordSource. Why?
 

AvantGuy

neophyte
Local time
Today, 10:48
Joined
Mar 25, 2018
Messages
22
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:48
Joined
May 7, 2009
Messages
19,169
What is yiur code fir the combo's afterupdate event?
 

AvantGuy

neophyte
Local time
Today, 10:48
Joined
Mar 25, 2018
Messages
22
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

neophyte
Local time
Today, 10:48
Joined
Mar 25, 2018
Messages
22
What is yiur code fir the combo's afterupdate event?
Thank you, arne lgp. Sorry, it's a bit convoluted...

Code:
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

Code:
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
Code:
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...

Code:
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

Super Moderator
Staff member
Local time
Today, 12:48
Joined
Feb 19, 2002
Messages
42,971
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:48
Joined
May 7, 2009
Messages
19,169
if you only have one subform, then the two subs will be enough to get
what you want:

Code:
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

neophyte
Local time
Today, 10:48
Joined
Mar 25, 2018
Messages
22
if you only have one subform, then the two subs will be enough to get
what you want:

Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:48
Joined
May 7, 2009
Messages
19,169
You are welcome sir.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:48
Joined
Feb 19, 2002
Messages
42,971
I always prefer no code solutions when they are available.
 

AvantGuy

neophyte
Local time
Today, 10:48
Joined
Mar 25, 2018
Messages
22
Indeed.
...well, one line doesn't count, does it.​
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:48
Joined
Feb 19, 2002
Messages
42,971
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.
 

Users who are viewing this thread

Top Bottom