Incorrect request for parameter

[Guest]

I created a form (Form A) for running queries. The form requests a start date
and end date and one can then select from a list of queries. The form works
properly. I then created a second form (Form B), which is an exact duplicate
of the first, except that criteria within the queries limit the results to a
subset of the database. The problem is some (not all) of the queries in Form
B request the user to enter the start and end dates from Form A. In order to
create Form B and the "B" queries, I had first simply copied the "A" queries
and form and made the appropriate alterations. Even though everything
appeared to be correct in both design view and SQL view, I thought perhaps
there was some artifact because of copying and pasting so I deleted
everything and started over. I created Form B from scratch and also created
the "B" queries from scratch, but I'm still experiencing the same problem. In
fact, for one query, before I had entered any criteria at all for any field,
when I switched to datasheet view, it asked for the Form A start and end
dates! Does anyone know what's going on and how to fix it? This is driving me
crazy and any help anyone can offer will be greatly appreciated!

 

[Michael Gramelspacher]

I created a form (Form A) for running queries. The form requests a start date
and end date and one can then select from a list of queries. The form works
properly. I then created a second form (Form B), which is an exact duplicate
of the first, except that criteria within the queries limit the results to a
subset of the database. The problem is some (not all) of the queries in Form
B request the user to enter the start and end dates from Form A. In order to
create Form B and the "B" queries, I had first simply copied the "A" queries
and form and made the appropriate alterations. Even though everything
appeared to be correct in both design view and SQL view, I thought perhaps
there was some artifact because of copying and pasting so I deleted
everything and started over. I created Form B from scratch and also created
the "B" queries from scratch, but I'm still experiencing the same problem. In
fact, for one query, before I had entered any criteria at all for any field,
when I switched to datasheet view, it asked for the Form A start and end
dates! Does anyone know what's going on and how to fix it? This is driving me
crazy and any help anyone can offer will be greatly appreciated!

I suppose this all makes sense to you, but not to me. FormA
allows a query to be selected and then provides two parameters,
start date and end date. Then FormB is supposed to take the
query selected in Form A and add more criteria? In effect a
Where clause is split across two forms, which each supply some
of the criteria. Little wonder FormB knows nothing of the
parameters you supplied in FormA, and so asks again for the
start date and end date. Am I even close to what you want to
do?

 

[Guest]

I suppose this all makes sense to you, but not to me. FormA
allows a query to be selected and then provides two parameters,
start date and end date. Then FormB is supposed to take the
query selected in Form A and add more criteria? In effect a
Where clause is split across two forms, which each supply some
of the criteria. Little wonder FormB knows nothing of the
parameters you supplied in FormA, and so asks again for the
start date and end date. Am I even close to what you want to
do?

No--sorry this was unclear. The point is that Form A and Form B, like the
queries that are accessed from each, are totally independent. There should be
no relationship between them. The "B" set of queries differs from the "A" set
in that the "A" series provides results from the entire database while the
"B" series is limited to a subset of the database (e.g., only account numbers
that begin with 9). (FYI, this was done because two departments in the
organization need the same types of information, i.e. queries, but one needs
to see this information for all accounts whereas the other is only concerned
with a subset of accounts.) Because the queries are the same except for the
restriction to certain accounts in the "B" set, I thought I would save time
by copying and modifying the "A" queries rather than creating the "B" series
from scratch so I created a "B" query by copying an "A" query, set the
criteria for the account number, and changed the references for the start and
end date from Between [Forms]![A]![AStartDate] and [Forms]![A]![AEndDate] to
Between [Forms]!![BStartDate] and [Forms]!![BEndDate]. Later, because I
thought the copying might have somehow introduced the error (some residual
code that isn't visible in design or SQL view), I created new "B" queries
from scratch, but the outcome is the same. So now I have a newly created Form
B and "B" queries created from scratch, none of which contain (or have ever
contained) any reference whatsoever to Form A or the "A" queries, yet the
program is asking for parameters from Form A. Hope this is now more clear,
but the fact that it's not logical makes it hard to understand. It's like the
software is trying to intuit what I want instead of literally executing the
code.

 

[Michael Gramelspacher]

I suppose this all makes sense to you, but not to me. FormA
allows a query to be selected and then provides two parameters,
start date and end date. Then FormB is supposed to take the
query selected in Form A and add more criteria? In effect a
Where clause is split across two forms, which each supply some
of the criteria. Little wonder FormB knows nothing of the
parameters you supplied in FormA, and so asks again for the
start date and end date. Am I even close to what you want to
do?

No--sorry this was unclear. The point is that Form A and Form B, like the
queries that are accessed from each, are totally independent. There should be
no relationship between them. The "B" set of queries differs from the "A" set
in that the "A" series provides results from the entire database while the
"B" series is limited to a subset of the database (e.g., only account numbers
that begin with 9). (FYI, this was done because two departments in the
organization need the same types of information, i.e. queries, but one needs
to see this information for all accounts whereas the other is only concerned
with a subset of accounts.) Because the queries are the same except for the
restriction to certain accounts in the "B" set, I thought I would save time
by copying and modifying the "A" queries rather than creating the "B" series
from scratch so I created a "B" query by copying an "A" query, set the
criteria for the account number, and changed the references for the start and
end date from Between [Forms]![A]![AStartDate] and [Forms]![A]![AEndDate] to
Between [Forms]!![BStartDate] and [Forms]!![BEndDate]. Later, because I
thought the copying might have somehow introduced the error (some residual
code that isn't visible in design or SQL view), I created new "B" queries

Maybe look at the query parameters and see if there is a
PARAMETERS statement with FormA references and parameters in
the SQL referring to FormB. Otherwise maybe someone else has an
idea.

 

[Guest]

It could be a PARAMETERS clause as Michael suggests, but while you wouldn't
see that in design view without opening the Parameters dialogue, it would be
pretty obvious in SQL view, so I'm doubtful on that score.

Another possibility is that some of the 'B' queries include one or more of
the 'A' queries in their FROM clauses.

You could use a single form and set of queries however by including an
additional Boolean parameter, i.e. a check box on the form so that all users
use a single form and set of queries, but the result set could be further
restricted by those users who wish that by checking the box, e.g.

WHERE
TransactionDate BETWEEN Forms!frmA!txtStartDate
AND Forms!frmA!txtEndDate
AND ((Forms!frmA!chkRestrict AND AccountNumber LIKE "9*)
OR NOT Forms!frmA!chkRestrict)

If the restriction is on more than the one column you'd simply add
additional AND operations to the inner parenthesised expression:

(Forms!frmA!chkRestrict AND AccountNumber LIKE "9* AND…..AND…..)

Ken Sheridan
Stafford, England

I suppose this all makes sense to you, but not to me. FormA
allows a query to be selected and then provides two parameters,
start date and end date. Then FormB is supposed to take the
query selected in Form A and add more criteria? In effect a
Where clause is split across two forms, which each supply some
of the criteria. Little wonder FormB knows nothing of the
parameters you supplied in FormA, and so asks again for the
start date and end date. Am I even close to what you want to
do?

No--sorry this was unclear. The point is that Form A and Form B, like the
queries that are accessed from each, are totally independent. There should be
no relationship between them. The "B" set of queries differs from the "A" set
in that the "A" series provides results from the entire database while the
"B" series is limited to a subset of the database (e.g., only account numbers
that begin with 9). (FYI, this was done because two departments in the
organization need the same types of information, i.e. queries, but one needs
to see this information for all accounts whereas the other is only concerned
with a subset of accounts.) Because the queries are the same except for the
restriction to certain accounts in the "B" set, I thought I would save time
by copying and modifying the "A" queries rather than creating the "B" series
from scratch so I created a "B" query by copying an "A" query, set the
criteria for the account number, and changed the references for the start and
end date from Between [Forms]![A]![AStartDate] and [Forms]![A]![AEndDate] to
Between [Forms]!![BStartDate] and [Forms]!![BEndDate]. Later, because I
thought the copying might have somehow introduced the error (some residual
code that isn't visible in design or SQL view), I created new "B" queries
from scratch, but the outcome is the same. So now I have a newly created Form
B and "B" queries created from scratch, none of which contain (or have ever
contained) any reference whatsoever to Form A or the "A" queries, yet the
program is asking for parameters from Form A. Hope this is now more clear,
but the fact that it's not logical makes it hard to understand. It's like the
software is trying to intuit what I want instead of literally executing the
code.

 

[Guest]

Sorry for taking up your time--I discovered my problem, something I should
have detected sooner but overlooked. I realized that the queries I was having
trouble with had one thing in common: a nested query that also contained a
field for "Eff Date". Even though this field wasn't pulling into the main
query, it was obviously affecting the outcome since removing this nested
query (which wasn't essential to these results) solved the problem. Thank you
both for your time and your suggestions--even though the problem is solved,
I'm interested to try your alternative technique, Ken. Thanks again!

It could be a PARAMETERS clause as Michael suggests, but while you wouldn't
see that in design view without opening the Parameters dialogue, it would be
pretty obvious in SQL view, so I'm doubtful on that score.

Another possibility is that some of the 'B' queries include one or more of
the 'A' queries in their FROM clauses.

You could use a single form and set of queries however by including an
additional Boolean parameter, i.e. a check box on the form so that all users
use a single form and set of queries, but the result set could be further
restricted by those users who wish that by checking the box, e.g.

WHERE
TransactionDate BETWEEN Forms!frmA!txtStartDate
AND Forms!frmA!txtEndDate
AND ((Forms!frmA!chkRestrict AND AccountNumber LIKE "9*)
OR NOT Forms!frmA!chkRestrict)

If the restriction is on more than the one column you'd simply add
additional AND operations to the inner parenthesised expression:

(Forms!frmA!chkRestrict AND AccountNumber LIKE "9* AND…..AND…..)

Ken Sheridan
Stafford, England

I created a form (Form A) for running queries. The form requests a start date
and end date and one can then select from a list of queries. The form works
properly. I then created a second form (Form B), which is an exact duplicate
of the first, except that criteria within the queries limit the results to a
subset of the database. The problem is some (not all) of the queries in Form
B request the user to enter the start and end dates from Form A. In order to
create Form B and the "B" queries, I had first simply copied the "A" queries
and form and made the appropriate alterations. Even though everything
appeared to be correct in both design view and SQL view, I thought perhaps
there was some artifact because of copying and pasting so I deleted
everything and started over. I created Form B from scratch and also created
the "B" queries from scratch, but I'm still experiencing the same problem. In
fact, for one query, before I had entered any criteria at all for any field,
when I switched to datasheet view, it asked for the Form A start and end
dates! Does anyone know what's going on and how to fix it? This is driving me
crazy and any help anyone can offer will be greatly appreciated!

I suppose this all makes sense to you, but not to me. FormA
allows a query to be selected and then provides two parameters,
start date and end date. Then FormB is supposed to take the
query selected in Form A and add more criteria? In effect a
Where clause is split across two forms, which each supply some
of the criteria. Little wonder FormB knows nothing of the
parameters you supplied in FormA, and so asks again for the
start date and end date. Am I even close to what you want to
do?

No--sorry this was unclear. The point is that Form A and Form B, like the
queries that are accessed from each, are totally independent. There should be
no relationship between them. The "B" set of queries differs from the "A" set
in that the "A" series provides results from the entire database while the
"B" series is limited to a subset of the database (e.g., only account numbers
that begin with 9). (FYI, this was done because two departments in the
organization need the same types of information, i.e. queries, but one needs
to see this information for all accounts whereas the other is only concerned
with a subset of accounts.) Because the queries are the same except for the
restriction to certain accounts in the "B" set, I thought I would save time
by copying and modifying the "A" queries rather than creating the "B" series
from scratch so I created a "B" query by copying an "A" query, set the
criteria for the account number, and changed the references for the start and
end date from Between [Forms]![A]![AStartDate] and [Forms]![A]![AEndDate] to
Between [Forms]!![BStartDate] and [Forms]!![BEndDate]. Later, because I
thought the copying might have somehow introduced the error (some residual
code that isn't visible in design or SQL view), I created new "B" queries
from scratch, but the outcome is the same. So now I have a newly created Form
B and "B" queries created from scratch, none of which contain (or have ever
contained) any reference whatsoever to Form A or the "A" queries, yet the
program is asking for parameters from Form A. Hope this is now more clear,
but the fact that it's not logical makes it hard to understand. It's like the
software is trying to intuit what I want instead of literally executing the
code.