Need simple way to override string expression in subreport query using DoCmd.OpenReport

Question

I need help on something. I am adding a new report to a second form and need a way to override the string expression in a query in a sub-report I call using DoCmd.Open Report.

Code follows:

the query called qry6IndEncountersInRange found in the sub report:

SELECT "Individual" AS EncounterCat, qry6PersonIndividualEncounterModified.PersonID, qry6PersonIndividualEncounterModified.EncounterDate, qry6PersonIndividualEncounterModified.EncounterID, qry6PersonIndividualEncounterModified.AgencyCode, qry6PersonIndividualEncounterModified.SiteCode, qry6PersonIndividualEncounterModified.ServiceCategory, qry6PersonIndividualEncounterModified.ServiceType, qry6PersonIndividualEncounterModified.EncounterType, qry6PersonIndividualEncounterModified.TimeHours, qry6PersonIndividualEncounterModified.TimeMinutes, qry6PersonIndividualEncounterModified.TravelTimeHours, qry6PersonIndividualEncounterModified.TravelTimeMinutes, qry6PersonIndividualEncounterModified.EncounterStaff, Null AS EncounterStaff2, Null AS EncounterStaff3
FROM qry6PersonIndividualEncounterModified
WHERE (((qry6PersonIndividualEncounterModified.EncounterDate) Between [Forms]![frmReportSubmenu]![StartDate] And [Forms]![frmReportSubmenu]![EndDate]));

Note current stringexpression refers to [Forms]![frmReportSubmenu]![StartDate]

Here is the DoCmd.OpenReport from main program:

DoCmd.OpenReport stDocName, acPreview

As a result of including the new report on this second form, second part of string expression is now different.  Thus:

[Forms]![frmReportSubmenuParametersCG]![StartDate]

So does anyone know specifically using GetArgs or TempVars with the DoCmd.OpenReport HOW to override the current string in the query with this new one?  Would like it to be as simple as possible, even avoiding any coding in the query it that is possible.

Thanks for your help in advance.

Answer 1

You can't change the SQL in a query when you call a report using DoCmd. You must change the recordsource in the report's Open event.

Here is a quick example:

Dim rpt As Report
Dim strRptName as String
strRptName = "rptGrossMarginPerCCG"
'Open report
DoCmd.OpenReport strRptName, acViewPreview
Set rpt = Reports(strRptName)
rpt.Caption = Me.cboCCGroup.Column(1)

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

Thanks for getting back to me.  The navigation is actually more complicated.  You are referring to the main report and the one called.  In my case this is actually a deep series of nested queries.  The main calling program is called rpt6stats2test.  In turn, that report contains a sub-report called:  rpt6SvcCatSub

rpt6SvcCatsub, then has a query under it, called qry6SvcCatUnderlying.

Then qry6SvcCatUnderlying has another query under it called, qry6AllEncountersInRange.

qry6All EncountersInRange has qry6AllEncountersInRangeUnderlying under it.

Finally qry6AllEncounterInRange is the original query I showed with the expression that needs to be overridden. 

Seems this is complex as it is.  Bottom line, is I need the ability to refer to the proper expression otherwise the nested queries don't seem to be working and no data returned here with the report now on Form 2.  ON Form 1 that refers in the expression to "frmReportSubmenu", I can run and no problem.  I do not get called for an "Enter Parameter Value" and all the nestled queries run just fine and I get valid output.

Hope this helps clarify a little more and maybe you have some other thoughts on how I can override to produce the report also on this form 2.

See you also are here in the Bay Area.

Larry Staley

Answer 3

Seems this is complex as it is.  Bottom line, is I need the ability to refer to the proper expression otherwise the nested queries don't seem to be working and no data returned here with the report now on Form 2.  ON Form 1 that refers in the expression to "frmReportSubmenu", I can run and no problem.  I do not get called for an "Enter Parameter Value" and all the nestled queries run just fine and I get valid output.

Hi Larry,

As it is quite complex, I think the problem is hard to solve without some VBA-coding.

In my opinion the simplest way with VBA is to assign the value of the RecordSource of the first report to a variable, strip off the WHERE part, and add a new WHJERE part with the dates for the second report. Then assing the value of the variable to the RecordSource of the second report.

Imb.

Answer 4

Hi Bill,

It is not really any dates that are an issue.  The issue is the lowest level query referenced in the second report (the sub-report) that needs to contain in the form string expression "frmReportSubmenuParamatersCG" and NOT "frmReportSubmenu" as the string is listed for the report in the first form.

Just really need someway, even using VBE code, to override in the second form, as currently has "frmReportSubmenu" and just insure the "frmReportSubmenuParametersCG" string is instead used.  I also looked into using TEMPVARS but that does not seem to be working.  The string is not being overwritten with the TEMPVARS variable value, namely this needed string!

Thanks again.

Answer 5

>>>I also looked into using TEMPVARS but that does not seem to be working.  The string is not being overwritten with the TEMPVARS variable value, namely this needed string!

According to your description, as far as I know that TempVars does hold expressions and when an expression is used as the definition of a TempVars, the TempVars will just treat it like a string and will not Eval at runtime. If you were doing this SQL construct in VBA, it would be easy and work. But within a Query, the expression stored in the TempVars will not act like when used as the criteria of a saved query.

Answer 6

Hi Bill,

I actually have a different issue now, in concerning use of Where clause in my DoCmd.OpenReport.

Here is basic code of the call:

 strCriteria2 = "'Black/African-American','Native American'"
 DoCmd.OpenReport stDocName, acPreview, , "EthnicityNew = "" & strCriteria2 & """

Here is the nested query for which the EhtnicityNew variable overrides:

SELECT tblPersonIndividualEncounter.PersonID, tblPersonIndividualEncounter.EncounterDate, tblPersonIndividualEncounter.EncounterID, tblPersonIndividualEncounter.AgencyCode, tblPersonIndividualEncounter.SiteCode, refServiceCategory.ServiceCategoryRpt AS ServiceCategory, tblPersonIndividualEncounter.ServiceType, tblPersonIndividualEncounter.EncounterType, tblPersonIndividualEncounter.[DirectService?], tblPersonIndividualEncounter.TimeHours, tblPersonIndividualEncounter.TimeMinutes, tblPersonIndividualEncounter.TravelTimeHours, tblPersonIndividualEncounter.TravelTimeMinutes, tblPersonIndividualEncounter.EncounterStaff, tblPerson.Ethnicity1, tblPerson.DataSource
FROM tblPerson INNER JOIN (tblPersonIndividualEncounter INNER JOIN refServiceCategory ON tblPersonIndividualEncounter.ServiceCategory = refServiceCategory.ServiceCategory) ON tblPerson.PersonID = tblPersonIndividualEncounter.PersonID
WHERE (((tblPerson.Ethnicity1) In ("EthnicityNew")) AND ((tblPerson.DataSource)=getdatasourcenumber()));

Bottom line is I cannot get this to work at all.  Appears the EthnicityNew variable never gets reset it the query with the "'Black/African-American','Native American'" string.

Any ideas?

L. Staley

Answer 7

>>>Bottom line is I cannot get this to work at all.  Appears the EthnicityNew variable never gets reset it the query with the "'Black/African-American','Native American'" string.

According to your description, you could create this query with parameter, then you could do something like this in the Open Event of your report:

strCriteria2 = "'Black/African-American','Native American'"
DoCmd.OpenReport strRptName, acViewPreview, , , , strCriteria2 

Private Sub Report_Open(Cancel As Integer)
    dim qd as QueryDef
    Set qd = CurrentDb.QueryDefs("qryQueryName")
    qd.Parameters("yourParamName") = Me.OpenArgs
    Me.Recordsource = qd.SQL
    Set qd = Nothing
End Sub

For more information, click here to refer about DoCmd.OpenReport Method (Access)