crosstab form/prompt problem 1

[topwaya]

I have a crosstab query, sql below

TRANSFORM Count(qryNONMembers.LocW) AS CountOfLocW
SELECT qryUnionProgramDates.FirstOfMth, qryNONMembers.LocW
FROM qryNONMembers LEFT JOIN qryUnionProgramDates ON qryNONMembers.PID = qryUnionProgramDates.pid
WHERE (((qryUnionProgramDates.FirstOfMth) Between ([Forms]![fdlgNONMemProgParticipants]![txtStart]) And ([Forms]![fdlgNONMemProgParticipants]![txtEnd])))
GROUP BY qryUnionProgramDates.FirstOfMth, qryNONMembers.LocW
ORDER BY qryUnionProgramDates.FirstOfMth
PIVOT qryNONMembers.Mtype;

It gives me an error message:

The Microsoft Jet database engine does not recognize '[Forms]![fdlgNONMemProgParticipants]![txtStart]' as a valid field name or expression.

It does this because the parameter needs to be explicitly defined. When I do that, I have the following sql

PARAMETERS [Forms]![fdlgNONMemProgParticipants]![txtStart] DateTime, [Forms]![fdlgNONMemProgParticipants]![txtEnd] DateTime;
TRANSFORM Count(qryNONMembers.LocW) AS CountOfLocW
SELECT qryUnionProgramDates.FirstOfMth, qryNONMembers.LocW
FROM qryNONMembers LEFT JOIN qryUnionProgramDates ON qryNONMembers.PID = qryUnionProgramDates.pid
WHERE (((qryUnionProgramDates.FirstOfMth) Between ([Forms]![fdlgNONMemProgParticipants]![txtStart]) And ([Forms]![fdlgNONMemProgParticipants]![txtEnd])))
GROUP BY qryUnionProgramDates.FirstOfMth, qryNONMembers.LocW
ORDER BY qryUnionProgramDates.FirstOfMth
PIVOT qryNONMembers.Mtype;

And then when I run the query from a Report, I first get the parameter prompts (one for txtStart, and one for txtEnd) and then I get the form dialog box to input the same criteria. This is quite annoying - having to enter the same criteria twice. I want to use the form and not the prompts.

Is there a way to have the form dialog box and NOT have the prompts?

 

[PHV]

fdlgNONMemProgParticipants must be an open main form when the query is launched.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Thank you so much for your quick response.

Please have patience with me, I am a newbie.

I am not sure (and have tried to find) the definition of an "open main form." So please elaborate if I'm wrong.

I opened the fdlgNONMemProgParticipants form
And then I ran the rptNONMemProgParticipants report. Now it gives me this exact error message:

The Microsoft Jet database engine does not recognize '' as a valid field name or expression.

Do I need to change something in the form properties?
Thanks so much for your help!

 

[PHV]

3 steps:
1) Open fdlgNONMemProgParticipants
2) Enter values in txtStart and txtEnd
3) Launch the report

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Awesome!!

How do I make this user friendly? I can have users open the fdlgNONMemProgParticipants form and have the submit button run the report.

I tried this using the code below for the OPEN EVENT and it appeared to work except that I didn't see any report, it starting printing the report instead. Do you know what changes I would need to make in the code below to view the report instead of print it?

Private Sub Search_Click()
   Me.Visible = False
   DoCmd.OpenQuery "qfltNONMemProgParticipants", acViewNormal, acEdit
   DoCmd.Close acForm, "qfltNONMemProgParticipants"
End Sub

Thanks so much!!

 

[topwaya]

Oops - that is in the ON CLICK event. Still playing with it, but can't figure it out - it keeps wanting to print. Should I post this in the forms forum?

 

[topwaya]

I found the answer in the forms forum. I needed to change "acViewNormal" to "acViewPreview"

Thank you very much for all your help!!!!