Subform not refreshing

[Adam Milligan]

Hello all-

I have a form with a tab control in it. I have placed a subform on top the
tab control such that it shows up on each page. I am trying to rewrite the
query that the subform is based on in the on change event of the subform and
then requery the subform. My code looks like this:


Private Sub tabCheckAllThatApply_Change()

Select Case Me.tabCheckAllThatApply.Value

Case 0
ChangeBPSQueries 31
Case 1
ChangeBPSQueries 32
Case 2
ChangeBPSQueries 33
Case 3
ChangeBPSQueries 34
Case 4
ChangeBPSQueries 35
Case 5
ChangeBPSQueries 36

End Select

Me.frmBPSCheckAllThatApply_Subform.Form.Requery

End Sub

The "ChangeBPSQueries" sub looks like this:

Public Sub ChangeBPSQueries(sCheckAllThatApplySection As String)

CurrentDb.QueryDefs("qryBPSCheckAllThatApplyRecordsource").SQL = "SELECT *
FROM LibBPSCheckAllThatApply INNER JOIN tblBPSEnrollmentCheckAllThatApply ON
LibBPSCheckAllThatApply.intBPSCheckAllThatApplyID =
tblBPSEnrollmentCheckAllThatApply.intBPSCheckAllThatApply_FK WHERE
intBPSSection = " & sCheckAllThatApplySection

End Sub

The event fires but the subform does not refresh/requery. If I close the
main form and re-open it, the correct data is in the subform, confirming to
me that the query is being re-written properly. I am stumped. Thanks in
advance for any time folks put into this.

Adam Milligan

P.S. The following list of commands also seem to have no effect:

Me.frmBPSCheckAllThatApply_Subform.Form.Refresh
Me.frmBPSCheckAllThatApply_Subform.Requery
Me.Requery
Me.Refresh

 

[Dirk Goldgar]

Hello all-

I have a form with a tab control in it. I have placed a subform on top
the
tab control such that it shows up on each page. I am trying to rewrite
the
query that the subform is based on in the on change event of the subform
and
then requery the subform. My code looks like this:


Private Sub tabCheckAllThatApply_Change()

Select Case Me.tabCheckAllThatApply.Value

Case 0
ChangeBPSQueries 31
Case 1
ChangeBPSQueries 32
Case 2
ChangeBPSQueries 33
Case 3
ChangeBPSQueries 34
Case 4
ChangeBPSQueries 35
Case 5
ChangeBPSQueries 36

End Select

Me.frmBPSCheckAllThatApply_Subform.Form.Requery

End Sub

The "ChangeBPSQueries" sub looks like this:

Public Sub ChangeBPSQueries(sCheckAllThatApplySection As String)

CurrentDb.QueryDefs("qryBPSCheckAllThatApplyRecordsource").SQL = "SELECT *
FROM LibBPSCheckAllThatApply INNER JOIN tblBPSEnrollmentCheckAllThatApply
ON
LibBPSCheckAllThatApply.intBPSCheckAllThatApplyID =
tblBPSEnrollmentCheckAllThatApply.intBPSCheckAllThatApply_FK WHERE
intBPSSection = " & sCheckAllThatApplySection

End Sub

The event fires but the subform does not refresh/requery. If I close the
main form and re-open it, the correct data is in the subform, confirming
to
me that the query is being re-written properly. I am stumped. Thanks in
advance for any time folks put into this.

Adam Milligan

P.S. The following list of commands also seem to have no effect:

Me.frmBPSCheckAllThatApply_Subform.Form.Refresh
Me.frmBPSCheckAllThatApply_Subform.Requery
Me.Requery
Me.Refresh

I take it that the RecordSource of the subform is
"qryBPSCheckAllThatApplyRecordsource"? If not, that would be the reason.
But assuming that's right, then my guess is that Access is caching the
recordsource query that was originally loaded, and not reloading it from the
stored QueryDef.

You may be able to get the subform to go back out and reload the QueryDef by
reassigning the recordsource. Instead of this:

Me.frmBPSCheckAllThatApply_Subform.Form.Requery

.... try this:

With Me.frmBPSCheckAllThatApply_Subform.Form
.RecordSource = .RecordSource
EndWith

But there's another approach that occurs to me. How about this? Instead of
changing the querydef each time, just change the recordsource of the subform
directly to the SQL statement:

'----- start of suggested code -----
Private Sub tabCheckAllThatApply_Change()

Const conBaseSQL As String = _
"SELECT * FROM LibBPSCheckAllThatApply INNER JOIN
tblBPSEnrollmentCheckAllThatApply ON
LibBPSCheckAllThatApply.intBPSCheckAllThatApplyID =
tblBPSEnrollmentCheckAllThatApply.intBPSCheckAllThatApply_FK WHERE
intBPSSection = "

Dim strRecordSource As String

Select Case Me.tabCheckAllThatApply.Value

Case 0
strRecordSource = conBaseSQL & "31"
Case 1
strRecordSource = conBaseSQL & "32"
Case 2
strRecordSource = conBaseSQL & "33"
Case 3
strRecordSource = conBaseSQL & "34"
Case 4
strRecordSource = conBaseSQL & "35"
Case 5
strRecordSource = conBaseSQL & "36"

End Select

Me.frmBPSCheckAllThatApply_Subform.Form.RecordSource = strRecordSource

End Sub
'----- end of suggested code -----

 

[Adam Milligan]

Dirk-

With Me.frmBPSCheckAllThatApply_Subform.Form
.RecordSource = .RecordSource
EndWith

Worked like a charm! Thanks a lot.

Adam Milligan