using form control values in queries

[Joseph Del Medico]

I'm trying to use a query whose SQL view is shown below to get a
recordset of all first quarter records from a table for a year that is
in the textbox of a form, so I can sum up the totals for the first
quarter for every person and display them on the form. The query works
fine when I preview it in the query builder, but when I try to open a
recordset in vb code with the query I get "too few parameters expected
one". It seems for some reason it can't resolve the value of the txtform
control when I run it in the vb code, whereas it does just fine running
it from query builder. Is there some way around this problem?
SELECT DISTINCT Quality.[UW Last Name], Quality.[Review Year],
Quality.[Review Month], Quality.Q1, Quality.Q2, Quality.Q3, Quality.Q4,
Quality.Q5 FROM Quality
WHERE (((Quality.[Review Year])=[Forms]![frmNewQuarter]![txtYear]) AND
((Quality.[Review Month])="01")) OR (((Quality.[Review
Year])=[Forms]![frmNewQuarter]![txtYear]) AND ((Quality.[Review
Month])="02")) OR (((Quality.[Review
Year])=[Forms]![frmNewQuarter]![txtYear]) AND ((Quality.[Review
Month])="03"))
ORDER BY Quality.[UW Last Name];

PS I have a similar query that I am assigning to the forms recordsource
and it works just fine within the vb code.
Although the weird thing is it doesn't display on the form until I go to
records > remove filter/sort

Here it is:

"SELECT SCQ403Forth.EmployeeID, SCQ403Forth.FirstName,
SCQ403Forth.LastName, SCQ403Forth.Productivity, SCQ403Forth.[P Rank],
SCQ403Forth.[WP Rank], SCQ403Forth.Turntime, SCQ403Forth.[TT Rank],
SCQ403Forth.[WTT Rank],
FROM SCQ403Forth WHERE (((SCQ403Forth.Quarter) =
[Forms]![frmSCDisplay]![cmbQuarter]) And ((SCQ403Forth.Year) =
[Forms]![frmSCDisplay]![cmbYear])) ORDER BY SCQ403Forth.LastName; "

I know I could probably get around it using the arduous process of
making a querydef with a parameter but I can't find the reference book i
used to have on doing this and the help on this topic inside access is
useless in this situation. Is there an easier way? If not how would I do
it with parameters?

Thanks in Advance,
Joe Del Medico

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

DAO does not use the Expression Service to resolve the form references.

In code, you need to construct the query string dynamically by concatenating
the values into the string:
Dim strSQL As String
strSQL = "SELECT ... WHERE (Quality.[ReviewYear] = " &
Forms![frmNewQuarter]![txtYear] & ") AND ...

Alternatively, declare the parameters, and supply them before opening the
recordset:
Dim qdf As QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("NameOrYourQueryHere")
qdf.Parameters(0) = Forms![frmNewQuarter]![txtYear]
qdf.Parameters (1) = ...

To declare the parameters in the query itself:
1. Open the query in design view.
2. Choose Parameters from the Query menu.
3. Type in the names and data types of your parameters, e.g.:
Forms![frmNewQuarter]![txtYear] Long

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joseph Del Medico" <jo*********@yahoo.com> wrote in message
news:40**********************@news.newsgroups.ws.. .

I'm trying to use a query whose SQL view is shown below to get a
recordset of all first quarter records from a table for a year that is
in the textbox of a form, so I can sum up the totals for the first
quarter for every person and display them on the form. The query works
fine when I preview it in the query builder, but when I try to open a
recordset in vb code with the query I get "too few parameters expected
one". It seems for some reason it can't resolve the value of the txtform
control when I run it in the vb code, whereas it does just fine running
it from query builder. Is there some way around this problem?
SELECT DISTINCT Quality.[UW Last Name], Quality.[Review Year],
Quality.[Review Month], Quality.Q1, Quality.Q2, Quality.Q3, Quality.Q4,
Quality.Q5 FROM Quality
WHERE (((Quality.[Review Year])=[Forms]![frmNewQuarter]![txtYear]) AND
((Quality.[Review Month])="01")) OR (((Quality.[Review
Year])=[Forms]![frmNewQuarter]![txtYear]) AND ((Quality.[Review
Month])="02")) OR (((Quality.[Review
Year])=[Forms]![frmNewQuarter]![txtYear]) AND ((Quality.[Review
Month])="03"))
ORDER BY Quality.[UW Last Name];

PS I have a similar query that I am assigning to the forms recordsource
and it works just fine within the vb code.
Although the weird thing is it doesn't display on the form until I go to
records > remove filter/sort

Here it is:

"SELECT SCQ403Forth.EmployeeID, SCQ403Forth.FirstName,
SCQ403Forth.LastName, SCQ403Forth.Productivity, SCQ403Forth.[P Rank],
SCQ403Forth.[WP Rank], SCQ403Forth.Turntime, SCQ403Forth.[TT Rank],
SCQ403Forth.[WTT Rank],
FROM SCQ403Forth WHERE (((SCQ403Forth.Quarter) =
[Forms]![frmSCDisplay]![cmbQuarter]) And ((SCQ403Forth.Year) =
[Forms]![frmSCDisplay]![cmbYear])) ORDER BY SCQ403Forth.LastName; "

I know I could probably get around it using the arduous process of
making a querydef with a parameter but I can't find the reference book i
used to have on doing this and the help on this topic inside access is
useless in this situation. Is there an easier way? If not how would I do
it with parameters?

Thanks in Advance,
Joe Del Medico