Subform not refreshing 1

[PRMiller2]

I'm getting pretty irritated with a problem that I cannot seem to solve. I've tried a number of different things with no luck. Please help!

I have two forms: the main form named frmApplyPriceSchedules and the subform called sfmAppliedPriceSchedules. They are NOT linked through a master/child relationship. When I load the main form, the subform is intentionally disabled. After updating combo box cboLOBGroupTier, two global variables are populated, the subform is enabled, and the subform should then be populated.

However, the subform does not want to requery. Here is the code for the main form's combo box after update event:

Private Sub cboLOBGroupTier_AfterUpdate()
On Error GoTo Err_Handler

    Dim strsql As String
 
    glngQALOBGroupTierID = Me.cboLOBGroupTier.Column(0)
    Me.sfmContainer.Enabled = True
    Me.sfmContainer.Requery
    Me.Requery
        
Exit_Handler:
    Exit Sub

Err_Handler:
    Call LogError(Err.Number, Err.Description, "frmApplyPriceSchedules.cboLOBGroupTier_AfterUpdate()")
    Resume Exit_Handler
    
End Sub

I have stepped through the code using the debugger and all lines do execute. However, it simply exits the sub after the line "me.requery" (and any of the varieties I have tried) without actually updating the subform. I have even tried requerying the main form, to no avail.

Here's the code in the Form_Load event of the subform:

Private Sub Form_Load()
On Error GoTo Err_Handler

    Dim strsql As String
 
    If glngQAClientID > 0 And glngQALOBGroupTierID > 0 Then
        strsql = "SELECT a.lonQAAppliedPriceSchedulesID_pk, c.txtQAPriceScheduleName, [b].[datStartDate] & '-' & [b].[datEndDate] AS ValidDates" & _
                 " FROM ((tblQAAppliedPriceSchedules AS a" & _
                 " LEFT JOIN tblQAPriceScheduleDetail AS b ON a.lonQAPriceScheduleDetailID_pk = b.lonQAPriceScheduleDetailID_pk)" & _
                 " LEFT JOIN tblQAPriceScheduleNames AS c ON b.lonQAPriceScheduleNamesID_fk = c.lonQAPriceScheduleNamesID_pk)" & _
                 " INNER JOIN tblQAClientsQALOBGroupTiers AS d ON a.lonQAClientsQALOBGroupTiersID_pk = d.lonQAClientsQALOBGroupTiersID_pk" & _
                 " WHERE (((d.lonQALOBGroupTierID_fk)=" & glngQALOBGroupTierID & ")" & _
                 " AND ((d.lonQAClientID_fk)=" & glngQAClientID & "));"
                 
        Me.RecordSource = strsql
        
        Me.lblQAPriceScheduleName.ForeColor = 0
        Me.lblValidDates.ForeColor = 0
        Me.txtQAAppliedPriceSchedulesID_pk.ControlSource = "lonQAAppliedPriceSchedulesID_pk"
        Me.txtQAPriceScheduleName.ControlSource = "txtQAPriceScheduleName"
        Me.txtValidDates.ControlSource = "ValidDates"
    End If
    
Exit_Handler:
    Exit Sub

Err_Handler:
    Call LogError(Err.Number, Err.Description, "sfmAppliedPriceSchedules.Form_Load()")
    Resume Exit_Handler

End Sub

As this is in development, I don't zero out the two global variables used to populate the recordsource query. As such, if I simply close the main form and reopen it, the subform then properly displays the records it should have shown when being requried initially.

I can't seem to figure out what's missing here. Any suggestions?

 

[PRMiller2]

I should mention that I've tried each of these:

Forms!frmApplyPriceSchedules.sfmAppliedPriceSchedules.Requery
Forms![frmApplyPriceSchedules]![sfmAppliedPriceSchedules].Requery
[sfmAppliedPriceSchedules].Requery


Where sfmContainer is the name of the object on the form, with sfmAppliedPriceSchedules as the name of the actual subform.

 

[PRMiller2]

Error in that last reply of mine, but can't edit it. I meant to say I referenced the name of the control in each of those lines of codes. I also tried this:

    DBEngine.Idle dbRefreshCache
    [Forms]!frmApplyPriceSchedules!sfmContainer.Form.Requery

To no avail.

 

[PRMiller2]

Holy cow, that was frustrating. When nothing worked, I opened the properties of the subform object and deleted the Source Object on the Data tab. I then set this code in the after update event for the combo box:

Private Sub cboLOBGroupTier_AfterUpdate()
On Error GoTo Err_Handler

    Dim strSQL As String
    
    glngQALOBGroupTierID = Me.cboLOBGroupTier.Column(0)
    Me.sfmContainer.SourceObject = "sfmAppliedPriceSchedules"
    Me.sfmContainer.Enabled = True
        
Exit_Handler:
    Exit Sub

Err_Handler:
    Call LogError(Err.Number, Err.Description, "frmApplyPriceSchedules.cboLOBGroupTier_AfterUpdate()")
    Resume Exit_Handler
    
End Sub

Nice to finally have it working!

 

[missinglinq]

Glad you got it working! I suspect that your original problem was the line

If glngQAClientID > 0 And glngQALOBGroupTierID > 0 Then

in the Form_Load event of the Subform.

The problem is that in a Main Form/Subform scenario, the Subform Loads before the Main Form Loads! And this holds true, AFAIK, even when the Subform is Disabled upon Loading. Since the Main Form isn't Loaded/Open when the Subform Form_Load event runs, you cannot reference the value of glngQAClientID (and possibly glngQALOBGroupTierID; not sure where this Value is coming from) which is assigned by the Combobox on the Main Form.

Linq ;0)>

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PRMiller2]

Thank you, Missinglinq. I never knew that about the opening order, and never thought to check it. My assumption was that a subform, being... well, "subbed," would load after the main one. That will teach me to make assumptions like that again!

 

[missinglinq]

Don't feel bad, this trips up a lot of people when they first start using Subforms! And to be honest, I've always thought that it was kind of backwards!

Good luck with your project!

Linq ;0)>

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!