Expressions/Referencing a form element in a Crosstab Query

[jkonline2]

I am having problems with a WHERE clause in my cross tab query. If I change it to a standard query or manually type in a value it works fine however the cross tab query will not work by refrancing it to a form.

It shows the number of people who answered each level of difficulty for each question. With the Answers along the top and question numbers down the side as below:

TRANSFORM Count(tbl_answers.Difficulty) AS CountOfDifficulty
SELECT tbl_questions.Number
FROM tbl_questions LEFT JOIN tbl_answers ON tbl_questions.Question_ID = tbl_answers.Question_No
WHERE (((tbl_answers.Questionnaire)=Forms![frm_main].test))
GROUP BY tbl_questions.Number, tbl_answers.Questionnaire
PIVOT tbl_answers.Difficulty;


However I only wish to show the results for a certain group of questionnaires hence the WHERE clause. I wish users to select the questionnaire by inputting it’s number into a text box on a form read by WHERE clause in the Query. If I manually type in a value in the query number it works but referencing to the form does not.

Can anyone help?

 

[Onyxpurr]

Try including the form object's name (just as you would type it in the query criteria or sql statement) as a parameter in the query.

 

[jkonline2]

I have as frm_main is the form and test is the textbox on the form which i am tring to read as below:

Forms![frm_main].test

and Forms![frm_main].test.text which should both work?

 

[GingerR]

make it a parameter, as onyx suggests:

in query design, right click in the grey part up top and choose PARAMETERS. put in forms![frm_Main]![test] and set it's type.

that should do ya.

g

 

[jrmont]

I have tried this and it only seems to work if I set focus on the text box in code. What if my parameters are in two text boxes?

 

[GingerR]

confused....

put your criteria in your query as you would a normal query. you can reference as many form controls as you wish.

in order for Access to 'understand', you must also cite these references in the PARAMETERS section. to do this, right-click in the grey area of the query grid, and choose PARAMETERS. put in ALL references to form controls and their data types. have you tried this yet?

i'm not sure what you mean by it only seems to work if you set focus to the text box in code? perhaps what you mean is that you type in a value on the form, but access doesnt recognize it? you have to SAVE or move to another control for it be 'sucked in' to the field on the form.

assuming you have a button which you click to get to your query results, put in the first line of code of the button's OnClick event me. refresh

g

 

[jrmont]

I have two text boxes labeled text30 and text32. For my tests, these have different default values that match "ANI" values in my table. I have a button that runs the query called "test".

Here is the query:

PARAMETERS [forms]![main]![text30].[text] Text ( 255 ), [forms]![main]![text32].[text] Text ( 255 );
SELECT voipcdr.ANI, voipcdr.DESTINATION, voipcdr.CALLDATE
FROM voipcdr
WHERE (((voipcdr.ANI)=[forms]![main]![text30].[text] Or (voipcdr.ANI)=[forms]![main]![text32].[text]));

Here is the button click event with various attempts commented out:

Private Sub Command37_Click()
'Me.Refresh Neither text box has focus (the button does) and no criteria match.
'Text30.SetFocus The query matched criteria in text30 and not text32
'Text32.SetFocus The query matched criteria in text32 and not text30
stQueryName = "Test"
DoCmd.OpenQuery stQueryName, acPreview
End Sub

I am baffled. I have seen this described in one of the MS FAQs just as I implemented it in my query and they make no mention of setting focus. I have also tried to put my values into variables with even less luck. It appears that that query uses (some form of NULL) for the parameter that is supposed to come from the control that does not have focus. Do you see anything that I am doing wrong? Have I made the correct use of [] in my query?