Passing two parameter from report into crosstab query

[rmm13]

I am hoping some of the expert help on this forum can again help me solve a problem. In advance, sorry for the length of this.

I have a set of reports which display data in different grids primarily by location and date.

They are complicated by the fact that the monthly and yearly (by month) reports require multiple queries and levels of queries to report only one entry per month which must be the max valued version and/or the last.
As a result the reports have different queries and I have them set up as Subreports in the Report Footer.

The Monthly turned out to be straightforward as the two grids were based on the same simple query and the other 3 values could be calculated, I was able to pass the parameters into the queries for Location/Month/Year by setting them up in a select a report form and referencing them through.
Forms![fmSelectaReport]![LocationCombo] etc.

The Yearly report with a pointer from Paul, is a set of Crosstab queries which references the 3 level simple query. I am able to pass the Report Year parameter into the Report using openArgs in openReport.

DoCmd.OpenReport "YrFMSRep", acViewPreview, "", "", acNormal, "[ReportYear]=" & ReportYear

In the third set of reports - the Weeklies, I again have a set of straightforward grids based on simple queries which I was able use the Form to pass the StartDate and EndDate.

The last weekly report is a bit tricky - I need to again pass both the StartDate and EndDate into a multiple level query,
I have it set up as a simple query, WkRecords which collects all the records for the interval requested and sorts them by week.
Then 4 crosstab queries set the data up in grids with location across the columns and week in the rows for the Subreports.

The report works fine with a set of dates hard coded into the Simple query WkRecords.

I tried passing the 2 parameters - StartDate and EndDate in from the Form using Forms![fmSelectaReport]![WkStartDate]
but I got a error that the database engine didn't recognize it. I found something on this forum about putting the parameters
in the Queries -> Parameters and tried that - don't get the error anymore but there is no data in the reports.

I also tried passing the 2 parameters in the open Args with a | in the OpenReport
DoCmd.OpenReport "WkFMSRep", acViewPreview, "", "", acNormal, StartDate & "|" & EndDate
Splitting the openArgs in the OnOpen event of the Report
The Parameters seem to be being passed correctly there are no type errors, however the queries are obviously not getting the information, I get pop up windows asking for StartDate and EndDate and when I type values in multiple times I still get a blank report.

Seems like I have missed something to connect the parameters back to the query.

 

[vbaInet]

The forms must remain open if you want to pass the values from it. An idea will be to set the Visible property of the form to false (on open) and close it in the Unload event of both reports.

 

[rmm13]

Yeah I am using the me.visible =False code below for all these reports.
It seems to be working for the weekly report as I am not being asked for parameters in any pop ups - but the report is just blank.


Me.Visible = False
DoCmd.OpenReport "WkFMSRep", acViewPreview, "", "", acNormal
While SysCmd(acSysCmdGetObjectState, acReport, "WkFMSRep") = acObjStateOpen
DoEvents
Wend
Me.Visible = True

 

[vbaInet]

Just fyi, your code will most likely hang in this block:

While SysCmd(acSysCmdGetObjectState, acReport, "WkFMSRep") = acObjStateOpen
DoEvents
Wend

Simply test using an IF block not a While loop.

I still believe somewhere in your code you have closed the form so it can't find the form. Maybe you closed the form on the Unload or Close event of the first report you opened? Remove the visible code and leave the form visible (or open the form, enter the dates) then run the report. If you can't find the form then you know there's code closing it somewhere. You may want to set the Modal property of the form to YES so for testing purposes you are sure that it remains open.

 

[rmm13]

Appreciate your help and pointers, so here's what I tried.
Using the form with the Form controls in the Query and declared in the query parameters.
The Form is already modal so I commented out the me.visible=false so the form is always visible. Tried running main report with the subreports in the report footer - all I can get is the borders round the reports - no data, I even made a new main report with only one sub report in.
I am getting not pop up boxes or errors so the parameters seem to be working.

As a check I replaced the Main Report with each of the Sub report names and ran these from the Form - everything works great, all the data appears.

So how do I get the data in the subreports to display in the main report? I am thinking I am missing some link or declaration or something

 

[rmm13]

Found it.

Troubleshooting and investigating further suddenly got this odd error message to come up.

"You can't use a pass-through query or non-fixed-column crosstab query as a record source for a subform or a subreport."

Went in the Crosstab query, declared the locations in the column headings and all the data appeared in my main report!! Had done this in my Yearly report so the month names would come out in order, but alphabetical ordering on the locations was fine.

Really appreciate your suggestions focusing me down on using the forms helped pinpoint the problem! as well as all the other helpful info I found on this forum.

 

[vbaInet]

I'm glad you got it working.