Re: CrossTab Query with Parameter

<tizmagik@xxxxxxxxx> schreef in bericht
news:1148587723.969551.57520@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have tried using the PARAMETER in the Query Design/Builder, but it
generated the same result. I will try again and also try your
suggestion about the 'underlying query' in the Report.

For now, I have created an ad-hoc work around in which I had to
hard-code all the data fields, it was a pain, but deadlines have to be
met. I'm still interested in learning how to do this, however, should
necessary changes or a new project come up later.

Thank you for your great reply :)

Below is a bit of code from a booking-application that I made nearly 10
years ago for a scouting campsite. In there, I have a similar situation
where a crosstab query is used for a report, and the query itself is based
on a number of underlying queries (even more complex than yours I might
add).

What I did (then) was to generate the report using a wizard, and then I read
through the code to understand what was happening. During this, I added a
series of comments to be able to understand the details later. This way, I
learned the lot.

The trick in crosstab-reports is that the report itself opens the query, and
the resulting table (regardless of the number of columns) triggers a series
of events based on the (volume of) row-data. Think of pre-historic line
printing of reports that you might know from the Cobol-era.
In these events, the column data is linked -using VB-code- to a large number
of pre-formatted unbound (!) data fields that have logical names, but no
other link to the columns of the query.
This VB-code does of course need access to the real data, and therefore the
code *also* opens the query. This is done in the Report_open event.
Furthermore, in the Detail_format section, the data is put in the (unlinked)
textboxes, and in the Page_header_format bit, the column names are put in
other (unlinked) textboxes. The surplus in data field is hidden when your
query has less columns than the blank report.

I suspect that the error in your app is where the code tries to open the
query, but then realises that the parameter is not filled. I vaguely
remember this not working at first in my program, whereas in normal reports
this is never a problem. I strongly remember that it took me quite a long
time without someone explaining me this lot.

If this is indeed the error, the solution should be to modify the code
(Report_open) by inserting the parameter into the query just before the
recordset gets opened. My hunch is that your report is based on the same
standard as mine once was, so by comparing the two, you should be able to
find the relevant differences. (mind you, I changed more than just this).

Best of luck,

Bas Hartkamp.

PS: I'm sure Dutch is not a too difficult language understand for you, as my
16-month old daughter seems to have no problems with it. If you do want to
know what the comments mean, let me know.


-----------
Example code from Scouting Campsite Booking program v7.1
-----------

Private Sub Report_Open(Cancel As Integer)

' Maak schaduwrecordset om gegevens in het rapport te kunnen zetten.
' Dit gaat op basis van de begindatum in 'Print Overzicht'
'

Dim intX As Integer
Dim qdf As DAO.QueryDef
Dim frm As Form

' Rapport niet openen zonder startwaarden uit scherm
If Not FormOpen("Print overzicht") Then
Cancel = True
MsgBox "Dit rapport kun je alleen opvragen vanuit het menu."
Exit Sub
End If

' Maak schaduw-recordset om gegevens te kunnen vullen.
' Structuur van rapport geeft alleen alle events en aantallen regels;
alle
' boxen zijn verder 'unbound' en worden vanuit deze schaduwrecordset
gevuld.

Set dbsReport = CurrentDb
Set frm = Forms![Print overzicht]
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Planboek2")


' Zet parameter voor startdatum voor schaduw-query; rapport pakt 'm
direct
qdf.Parameters("[Forms]![Print overzicht]![StartDatum]") =
frm![StartDatum]
qdf.Parameters("[Forms]![Print overzicht]![EindDatum]") =
frm![EindDatum]


' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Even het aantal kolommen onthouden..
' (= aantal uit query -1 vanwege de verborgen <NULL>-waarde
intColumnCount = rstReport.Fields.Count - 1

' maar mag maximum van rapport niet overstijgen
If intColumnCount > conTotalColumns Then
intColumnCount = conTotalColumns
End If

' Zet knoppenbalk goed en maximaliseer rapport.
DoCmd.ShowToolbar "tbrPrintreport", acToolbarYes
DoCmd.Maximize

End Sub


.

Relevant Pages

  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: Fields in Label dont give correct information
    ... You need to create a query based on tblRegEvent and tblRegPayment. ... -If your DCountis to count values in your report, ... half the time with very few errors (other than that label problem I had.) ... For example in the Room assignment on the report an idividual will be ...
    (microsoft.public.access.reports)
  • Re: Tips on domain aggregate replacements
    ... This already is a split db, but the users are 400 miles away from the server hosting the data:) They are all running the same copy of the FE locally on a single terminal server via TS/RDP sessions. ... It doesn't seem to have much impact on performance; the report takes just about as long to run if they are all logged in as it does when I am logged on testing it at night. ... I would do DSums from the controls on the report on the data returned by the query. ... The biggest offender is a certain report that needs to Sum a particular complex total for each of the next twelve months (the DSum in VBA was a sideline to this issue) and present these as items on each line of output. ...
    (microsoft.public.access.modulesdaovba)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)