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:
Expand|Select|Wrap|Line Numbers
- 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];
Expand|Select|Wrap|Line Numbers
- 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];
Expand|Select|Wrap|Line Numbers
- 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];
"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.