crosstab fails w/ form parameters Jet error

[stormin_norm]

I have a parameter form with two fields txtClass and lbSemester. I then click a command button to run the qrySemesterGrades retrieving the set of students, course number, course name, grade, etc.. This works fine.

I then run a crosstab query against the qrySemesterGrades results in order to create a matrix of students & grades. It works great if I hardcode class & semester in qrySemesterGrades, but fails when using the paramater form.

Error message:

"The microsoft Jet Engine does not recognize '[Forms]![frmRptSemesterGrade]![txtClass]' as a valid field name or expression"

What is the best way to do this?
form->qry->crosstab qry

I reviewed the crosstab discussions threads, although the microsoft link provided is for acccess 2002, fails to run in my access2000.


Thanks!

 

[Rich]

You have to define the parameters as text, which I assume they are

 

[stormin_norm]

In the joined tables, the class is defined as text.

In the query, I do not have explicit parameters. Simply have in the criteria for Class--> [Forms]![frmRptSemesterGrade]![txtClass]

Again, works fine when the form opens the qryStudentGrade, but fails on the crosstab

 

[Rich]

Again you have to define the Query Parameters
something like
PARAMETERS Forms!AccDtes![BeginningDate] DateTime, Forms!AccDtes![EndingDate] DateTime;
SELECT DISTINCT Deductions.TrDate, etc etc

 

[MattS]

Whenever I've had this problem in the past it's because I haven't set the Column Headings property in the crosstab query

Matt..

 

[stormin_norm]

Rich- I tried using [Forms]!... as a parameter, but I then get an error message "you canceled the previous operation."
I also tried to use parameters called "ClassSelect" and "SemesterSelect", but then get prompted for response, perhaps I can use VB code to push the fields into these parameters?


Matt- This worked! The only problem is I have to dynamically change the column headers based on the semester. (whole reason I went down this crosstab path)
I could write VB code which looks up the distinct courses, and puts them as the header info. Any other tricks for dynamically changing header info?
But then again, perhaps it may be easier to just write vb code in the report to get the matrix look vs. this crosstab. Crosstabs seem limited in how many fields you can put in a row & column. Must be a performance issue.

 

[stormin_norm]

Can I reference a queries' column heading property from a form or VBA?

Perhaps through command. or object. ???

-norm.