CrossTab subReport 1

[BRP250]

Is there anyway I can link a crossTab subreport to the main report?

Access returns an error saying it cannot find the field in a crosstab subreport.

Thanks in advance:

Bruce

 

[dhookom]

Set the Column Headings property of the Crosstab Query to all possible column heading values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BRP250]

This is the error message.
------------------
You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport.

Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property.
------------------
I set the ColumnHeadings property in the query. The subreport runs well.

I tried setting the child field link to a fixed row heading.

It looks like I can't use this report as a subreport, or am I missing something?

Bruce

 

[dhookom]

Can you provide the SQL view of your subreport record source?

Also, how di you try set the link fields? Did you just type them in? Were you attempting to use a field from your column headings for the link?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BRP250]

SQL:
TRANSFORM Max([Result].[resResulttxt]) AS MaxOfresResulttxt
SELECT [Result].[resInfoID], [Test].[tstDesc] AS Analysis, [Result].[resMethod] AS Method, [Result].[resUnit] AS Unit
FROM Result INNER JOIN Test ON [Result].[resTestID]=[Test].[tstID]
GROUP BY [Result].[resInfoID], [Test].[tstDesc], [Result].[resMethod], [Result].[resUnit], Left([resSampleID],InStr([resSampleID],"/")-1)
PIVOT [resSiteDesc] & " " & [resSampleID];

I got it. I added the resinfoID field to the report and made it invisible (that's the one I tried to link). In the onopen sub I hide that column and set the report to filter it with a field from the main report. I hope this makes sense, but the trick is to get the subreport filter working.

Your questions:
To set up the link I used the subreport properties link dialogue where both fields came up. I couldn't use any column headings as they are dynamic.

Thanks for your trouble.

here's some of the code to set up the report:
<code>
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name <> "resInfoID" Then
Me.Detail.Controls(i - 1).Visible = True
Me.Detail.Controls(i - 1).ControlSource = rst.Fields(i).Name
If i > 3 Then
Me.ReportHeader.Controls(i - 1).Caption = rst.Fields(i).Name
Me.ReportHeader.Controls(i - 1).Visible = True
End If
End If
Next i
</code>
The i-1 hides the resInfoID column. A bit tacky but it works.

Bruce

 

[dhookom]

You didn't set the column headings property in the crosstab query. You should really learn how to do this for future reference.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BRP250]

Thanks, that's what I missed.

They are different everytime and none of the information in them is linkable. Do I do it in code in the onopen procedure?

 

[dhookom]

I would use the crosstab report solutions from

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]