Report, Crosstab Query and Parameters

[Bryan]

I've been looking over this newsgroup, but I can't find an answer to my
problem. I see that a few others have posted this issue over the
years, but none of them got a response. I'll give it another shot.

I have a report that is based on a crosstab query. The crosstab query
is based off of another query, and that has a parameter in the where
clause. Like this:

WHERE e.elevationid = [Forms]![ElevationSelect]![elevationcode]

If I run the sub-query alone, it prompts me for the parameter, then
runs fine.

If I run the crosstab query, it prompts me for the parameter, then runs
fine.

If I run the report, it shows me the form, lets me enter in the data
for the parameter, then gives me an error saying:

(-2147217904) No value given for one or more required parameters.

I don't get it. If I use a normal query for the report rather than a
crosstab, it works fine. When I switch to the crosstab query, kaboom.

Can anyone help?!? Thanks in advance...

- Bryan

[Rick Brandt]

Bryan wrote:

I've been looking over this newsgroup, but I can't find an answer to
my problem. I see that a few others have posted this issue over the
years, but none of them got a response. I'll give it another shot.

I have a report that is based on a crosstab query. The crosstab query
is based off of another query, and that has a parameter in the where
clause. Like this:

WHERE e.elevationid = [Forms]![ElevationSelect]![elevationcode]

If I run the sub-query alone, it prompts me for the parameter, then
runs fine.

If I run the crosstab query, it prompts me for the parameter, then
runs fine.

If I run the report, it shows me the form, lets me enter in the data
for the parameter, then gives me an error saying:

(-2147217904) No value given for one or more required parameters.

I don't get it. If I use a normal query for the report rather than a
crosstab, it works fine. When I switch to the crosstab query, kaboom.

Can anyone help?!? Thanks in advance...

Not sure I've seen that exact symptom, but crosstabs insist that all
parameters be explicitly defined in the parameters dialog when in query
design view whereas other query types will usually evaluate parameters
on-the-fly. This includes parameters that are coming from queries providing
input to the crosstab.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Bryan]

Rick - thanks for the reply.

I do have the parameters defined in the Crosstab query.

I've found a bit more info since my original post.

The report for this query has a number of unbound fields where the data
will eventually end up. I'm doing this to account for some queries
having more columns than others. Based on the example in the Access
Developer's Handbook by Getz, Litwin & Gilbert, I use this code in the
Report_Open function to map the query to the unbound fields:
Set oRst = New ADODB.Recordset
oRst.ActiveConnection = CurrentProject.Connection

oRst.Open Me.RecordSource, CurrentProject.Connection, , , adCmdTable

iCtlCount = Me.Detail.Controls.count - 3
iFldCount = oRst.Fields.count

For iLoop = 1 To iFldCount - 3
sName = oRst.Fields(iLoop + 2).name
Me.Controls("col" & iLoop).ControlSource = sName
Next
The error occurs on the oRst.Open statement. The question is, how do I
sent the parameters to that query????

[Bryan]

Okay, I figured it out. For anyone who may try to do this in the
future, here's what I did:

Instead of the oRst.Open I was doing before, I changed it to this:
Dim oRst As adodb.Recordset
Dim oCmd As adodb.Command

Set oCmd.ActiveConnection = CurrentProject.Connection

With oCmd
.CommandText = Me.RecordSource
.CommandType = adCmdStoredProc
.Parameters.Append
..CreateParameter("[Forms]![ElevationSelect]![elevationcode]",
adInteger, adParamInput, , Forms(frmElevationForm)("elevationcode"))
End With

Set oRst = oCmd.Execute
Works like a charm!