Changed querydef not reflected in subform display

[ragtopcaddy via AccessMonster.com]

I have to display a newly calculated record in a subform that is designed
with "LinkChildField" criteria to display existing records.

I have checked the code and the resulting query and everything works fine.
The problem is, even after requerying the subforms, the existing records
persist and the newly calculated records don't display.

Essentially, I've replaced the sql for the subforms' queries to display the
newly calculated record, but even "F9" in the form and subform have no effect.
The subforms appear to be using the unchanged querydef.sql. The
"strQDFPEFctrs" and "strQDFPE" variables are global and are used to reset the
string to the original strings once the user moves to another record in the
main form.

Here's my code:

strTemp = Me.txtSwapID & " AS SwapID, " & Me.cmbTenor & " AS tnrid"

Set qdf = db.QueryDefs("qrySFrmPEFactors")
strQDFPEFctrs = qdf.SQL
strFind = "tblPEFactors.swapid, tblPEFactors.tnrid"
strQDF = Replace(strQDFPEFctrs, strFind, strTemp, , , vbTextCompare)
strQDF = Replace(strQDF, "tblPEFactors;", "tblPEFactorsTemp;", , ,
vbTextCompare)
qdf.SQL = strQDF
strQDF = ""
qdf.Close
Set qdf = Nothing

Set qdf = db.QueryDefs("qrySFrmPE")
strQDFPE = qdf.SQL
strFind = "tblPEFactors.swapid, tblPEFactors.tnrid"
strQDF = Replace(strQDFPE, strFind, strTemp, , , vbTextCompare)
strQDF = Replace(strQDF, "tblPEFactors;", "tblPEFactorsTemp;", , ,
vbTextCompare)
qdf.SQL = strQDF
qdf.Close
Set qdf = Nothing

Me.sfrmPE.Requery
Me.sfrmPEFactors.Requery

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via AccessMonster.com

 

[Dirk Goldgar]

I have to display a newly calculated record in a subform that is
designed with "LinkChildField" criteria to display existing records.

I have checked the code and the resulting query and everything works
fine. The problem is, even after requerying the subforms, the
existing records persist and the newly calculated records don't
display.

Essentially, I've replaced the sql for the subforms' queries to
display the newly calculated record, but even "F9" in the form and
subform have no effect. The subforms appear to be using the unchanged
querydef.sql. The "strQDFPEFctrs" and "strQDFPE" variables are global
and are used to reset the string to the original strings once the
user moves to another record in the main form.

Here's my code:

strTemp = Me.txtSwapID & " AS SwapID, " & Me.cmbTenor & " AS tnrid"

Set qdf = db.QueryDefs("qrySFrmPEFactors")
strQDFPEFctrs = qdf.SQL
strFind = "tblPEFactors.swapid, tblPEFactors.tnrid"
strQDF = Replace(strQDFPEFctrs, strFind, strTemp, , , vbTextCompare)
strQDF = Replace(strQDF, "tblPEFactors;", "tblPEFactorsTemp;", , ,
vbTextCompare)
qdf.SQL = strQDF
strQDF = ""
qdf.Close
Set qdf = Nothing

Set qdf = db.QueryDefs("qrySFrmPE")
strQDFPE = qdf.SQL
strFind = "tblPEFactors.swapid, tblPEFactors.tnrid"
strQDF = Replace(strQDFPE, strFind, strTemp, , , vbTextCompare)
strQDF = Replace(strQDF, "tblPEFactors;", "tblPEFactorsTemp;", , ,
vbTextCompare)
qdf.SQL = strQDF
qdf.Close
Set qdf = Nothing

Me.sfrmPE.Requery
Me.sfrmPEFactors.Requery

Probably the original query is cached. Try reassigning the subform's
recordsource; maybe that will make it requery properly:

Me.sfrmPE.Form.RecordSource = _
Me.sfrmPE.Form.RecordSource

Me.sfrmPEFactors.Form.RecordSource = _
Me.sfrmPEFactors.Form.RecordSource

 

[ragtopcaddy via AccessMonster.com]

Dirk,

Thanks for the insight. Once again you provide the answers.

Bill R

I have to display a newly calculated record in a subform that is
designed with "LinkChildField" criteria to display existing records.

[quoted text clipped - 38 lines]

Me.sfrmPE.Requery
Me.sfrmPEFactors.Requery

Probably the original query is cached. Try reassigning the subform's
recordsource; maybe that will make it requery properly:

Me.sfrmPE.Form.RecordSource = _
Me.sfrmPE.Form.RecordSource

Me.sfrmPEFactors.Form.RecordSource = _
Me.sfrmPEFactors.Form.RecordSource

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via AccessMonster.com