Problem with Crosstab Query in subform

[russellhq]

Hi, I'm fairly new to access and have a little trouble with a crosstab query I've setup.

I have a main form where the user selects a project name and below in a subform, a crosstab query is displayed in a datasheet view. The info is dependant on the project picked.

What I tried to do is set the criteria in the the query to only show results that match the project name but I am having no success. If I type a name into the critera then it works but if I enter a field name from the form then I run into trouble.

This is the initial SQL that works:

TRANSFORM First(tblWelderQualification.CertificateNo) AS FirstOfCertificateNo
SELECT [First Name] & " " & [surname] AS Name
FROM tblWelders INNER JOIN tblWelderQualification ON tblWelders.[Welder ID] = tblWelderQualification.[Welder ID]
WHERE (((tblWelderQualification.Project)="filterCriteria"))
GROUP BY [First Name] & " " & [surname], tblWelderQualification.Project
PIVOT tblWelderQualification.[WPS No];

When I add a parameter and change the critera as below it fails.

PARAMETERS [Forms]![frmQualReg]![Text5] Text ( 255 );
TRANSFORM First(tblWelderQualification.CertificateNo) AS FirstOfCertificateNo
SELECT [First Name] & " " & [surname] AS Name
FROM tblWelders INNER JOIN tblWelderQualification ON tblWelders.[Welder ID] = tblWelderQualification.[Welder ID]
WHERE (((tblWelderQualification.Project)=[Forms]![frmQualReg]![Text5]))
GROUP BY [First Name] & " " & [surname], tblWelderQualification.Project
PIVOT tblWelderQualification.[WPS No];

This fails also, I've only added a parameter here.

PARAMETERS [Forms]![frmQualReg]![Text5] Text ( 255 );
TRANSFORM First(tblWelderQualification.CertificateNo) AS FirstOfCertificateNo
SELECT [First Name] & " " & [surname] AS Name
FROM tblWelders INNER JOIN tblWelderQualification ON tblWelders.[Welder ID]=tblWelderQualification.[Welder ID]
WHERE (((tblWelderQualification.Project)="filterCriteria"))
GROUP BY [First Name] & " " & [surname], tblWelderQualification.Project
PIVOT tblWelderQualification.[WPS No];

When it fails I get the following error from the form:
"You can't use a pass-through query or a non-fixed 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."

The reason for using the crosstab query is the number of colums is dynamic and I didn't want to use the pivotchart as my users are more used to the datasheet view.

Any help would be greatly appreciated. Thanks.

[nico5038]

Crosstable queries are nasty to use for forms and reports.
The basic problem is the fact that the content of a column will be transformed into fieldnames.
Thus when your selection fails to deliver a value or has a new value, then a field will be missing or a new field will be missing.
Best to make sure that all different values are present by using an outerjoin query with a table holding all values.

Personally I often create first a temp table with the selected data and use that for the crosstable query to have no trouble with the selection in the crosstable query.

When a report will work for you, then I can supply you with VBA code to fill a report depending on the result, but the max number of rows will need to be known.

Nic;o)

[russellhq]

outerjoin query? I'm not sure how this would be done but i think i undertand your concept

[nico5038]

Make a table with all values for the crosstable query.
JOIN that table with the original table and make the "value table" leading (click the JOIN line and chose option 2 or 3)
Now there will always be a value, even a Null will show.
I use the trick e.g. for having all 12 months and by joining to a field with the month all months will show.

Nic;o)