Form's Criteria used in Crosstab

[onechriswhite]

Hi

I'm trying to open a crosstab query from a form, using some of the form's current data as input criteria into the query.

When I run the crosstab query without criteria from the form, it works. When I manually type specific data into the criteria of the crosstab query, it works.
When I use references to the form's fields in the crosstab query it says, "field not recognised" or similar.

The crosstab query has a data source that is another query. If I try to run THIS one from the form, with criteria referenced back to data on the form, it works fine.



The form is "FRMClass" and on the "APP" button I am trying to open the crosstab query QRYPupilAchievement_Crosstab with data sourced from the form. The non-crosstab query that is the data source for the crosstab is called QRYPupilAchievement.

I have tried putting the referenced fields in the criteria boxes of both the normal query and the crosstab - they are currently residing in the non-crosstab, I think.

Is it just something peculiar to crosstabs? As an alternative, if I could get a form that displays the crosstab, I could use DoCmd.OpenForm with an stLinkCriteria. Can you get crosstabs displayed in a form?

Thanks

[weekend00]

I don't think you can open the query "QRYPupilAchievement" without problem.

you should not just type the object name of the form as query's criteria. The query never know what is in you form. you need to write the value of the object to the query in the runtime using some code in the form's event.

[weekend00]

You must know where is the following code from in you form.
Please take 2 steps:
1 remove the criteria from your query "QRYPupilAchievement";
2 add one line (like following in red) to you code (Please put all the red code in one line).
then try again.

Private Sub CMDRunAPPCrosstab_Click()
On Error GoTo Err_CMDRunAPPCrosstab_Click
Dim stDocName As String

stDocName = "QRYPupilAchievement_Crosstab"
CurrentDb.QueryDefs(stDocName).SQL = "TRANSFORM Last(Security) AS LastOfSecurity SELECT FocusDescription FROM QRYPupilAchievement WHERE TBLClass.ClassCode='" & ClassCode & "' AND TBLPupilAchievement.Level='" & BaseLevel & "' GROUP BY FocusDescription, ClassID, [Level], AttainmentTargetID, ClassID, ClassCode PIVOT [""Name""]"

DoCmd.OpenQuery stDocName, acViewNormal, acEdit
Exit_CMDRunAPPCrosstab_Click:
Exit Sub
Err_CMDRunAPPCrosstab_Click:
MsgBox Err.Description
Resume Exit_CMDRunAPPCrosstab_Click

End Sub

[onechriswhite]

Thanks, that worked well

I'm still confused as to why it worked for a standard query by refderencing the form, but not for a crosstab.

And I have no idea how to decipher your code!

But thank you - anything that works is fine by me!

Chris

[weekend00]

If you don't know why, please check my first reply.

[weekend00]

Things in query are different from things in VBA code. query run by data server while codes run by program interpreter, they are totally two different things, this is more obvious when using other database as data source, e.g. ORACLE, query run by ORACLE server, VBA code run by Access interpreter. ORACLE no way to know what is in you Access form. That means you need to provide the data itself to a query, not the control which holding the data.


May I mark it as solved?

[pbaldy]

This was your problem:

Crosstab Query with a Parameter

[onechriswhite]

Absolutely perfect. Thanks.

BaldyWeb is also bookmarked... there is no escape now..

[pbaldy]

LOL! Happy to help.