Parameters removed - still get prompted for them

[Scott F via AccessMonster.com]

I am making an app more user friendly. Rather than have the user enter
multiple parameters, many times, I placed text boxes on the form and use
these to provide start and end dates to the queries.

So, I removed the parameters from the query. (Note: Actually it is 2
queries as one is embedded in the other. But I removed the parameters from
both queries.)

A button runs this code:

Set qdf = db.QueryDefs("qry_Error Check_Missing Start or End")
'qdf![Data Export Begin Date] = Forms!frmBilling!txtStartDate
'qdf![Data Export End Date] = Forms!frmBilling!txtEndDate
Set rs = qdf.OpenRecordset

I commented out the lines setting the parameters, but when the code runs, I
get a "Too few parameters, 2 expected" error. (Wait, I removed the
parameters!)

If I uncomment the two lines to set the parameters, I get a "Item not found
in this collection." (Probably because I removed the parameters!)

A compact and repair does not solve the problem. Also, if I run the query
manually from the database window with the "frmBilling" form open and the
date fields populated, it runs fine.

What a I missing?

Thanks,
Scott

 

[Allen Browne]

Scott, if you have a name in your query that Access does not recognise,
Access assumes it is a parameter. It can be as simple as misspelling a field
name, e.g. omitting a space, or not adding the square brackets around names
that start with a number, or contain a space or other special character.

When you say you removed the parameters, I take you that you completely
removed every occurance of [Data Export Begin Date] and [Data Export End
Date] from both queries (not that you merely removed their declaration.)
There is no way this is going to work if the query you are trying to supply
parameters to refers to another query that also requires parameters.

If just [qry_Error Check_Missing Start or End] query had the parameters (not
the lower level query), you could supply them as:
qdf.Parameters("[Data Export Begin Date]") =
Forms!frmBilling!txtStartDate
qdf.Parameters("[Data Export End Date]") = Forms!frmBilling!txtEndDate

In practice, is it usually a whole lot easier to just build the SQL
statement as a string in your code instead of relying on saved queries. That
way you can concatente the values form the form into the string. It is
*much* more flexible for handling cases where the user supplies only some of
the parameters, and so the others are not needed in the WHERE clause at all.
Typically the code goes something like this:
Dim rs As DAO.Recordset
Dim strSql As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

strSql = "SELECT * FROM Table1 WHERE [MyDate] Between " & _
Format(Forms!frmBilling!txtStartDate, conJetDate) & " And " & _
Format(Forms!frmBilling!txtEndDate, conJetDate) & ";"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am making an app more user friendly. Rather than have the user enter
multiple parameters, many times, I placed text boxes on the form and use
these to provide start and end dates to the queries.

So, I removed the parameters from the query. (Note: Actually it is 2
queries as one is embedded in the other. But I removed the parameters
from
both queries.)

A button runs this code:

Set qdf = db.QueryDefs("qry_Error Check_Missing Start or End")
'qdf![Data Export Begin Date] = Forms!frmBilling!txtStartDate
'qdf![Data Export End Date] = Forms!frmBilling!txtEndDate
Set rs = qdf.OpenRecordset

I commented out the lines setting the parameters, but when the code runs,
I
get a "Too few parameters, 2 expected" error. (Wait, I removed the
parameters!)

If I uncomment the two lines to set the parameters, I get a "Item not
found
in this collection." (Probably because I removed the parameters!)

A compact and repair does not solve the problem. Also, if I run the query
manually from the database window with the "frmBilling" form open and the
date fields populated, it runs fine.

What a I missing?

 

[Scott F via AccessMonster.com]

Allen,
Thanks for the reply. I did ensure that all occurences of the parameters
from both queries (both the "qry_Error Check_Missing Start or End" query and
the underlying query) were removed. In fact, with the form open and the data
populated in the relevant fields, I can manually open both queries. I am not
prompted for parameters when opening them ths way.

So, from the database window, both queries open fine, but not when opened via
the code.

One error (Parameters missing...) suggests that the query has parameters,
while the other (Item not found in this collection) suggests the contrary.

Weird.

P.S. I agree in the dynamic creation of the SQL string via code. I normally
code similar to this, in my own apps. I "inherited" this one however. I
didn't want to invest a lot of time in this particular portion of the
database as the two dates are always required and the only parameters
necessary. BTW, I would never use spaces AND underscores in the same name of
an object. In fact I rarely use either. Useless typing IMHO. Hope my boss
doesn't read this.

Scott, if you have a name in your query that Access does not recognise,
Access assumes it is a parameter. It can be as simple as misspelling a field
name, e.g. omitting a space, or not adding the square brackets around names
that start with a number, or contain a space or other special character.

When you say you removed the parameters, I take you that you completely
removed every occurance of [Data Export Begin Date] and [Data Export End
Date] from both queries (not that you merely removed their declaration.)
There is no way this is going to work if the query you are trying to supply
parameters to refers to another query that also requires parameters.

If just [qry_Error Check_Missing Start or End] query had the parameters (not
the lower level query), you could supply them as:
qdf.Parameters("[Data Export Begin Date]") =
Forms!frmBilling!txtStartDate
qdf.Parameters("[Data Export End Date]") = Forms!frmBilling!txtEndDate

In practice, is it usually a whole lot easier to just build the SQL
statement as a string in your code instead of relying on saved queries. That
way you can concatente the values form the form into the string. It is
*much* more flexible for handling cases where the user supplies only some of
the parameters, and so the others are not needed in the WHERE clause at all.
Typically the code goes something like this:
Dim rs As DAO.Recordset
Dim strSql As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

strSql = "SELECT * FROM Table1 WHERE [MyDate] Between " & _
Format(Forms!frmBilling!txtStartDate, conJetDate) & " And " & _
Format(Forms!frmBilling!txtEndDate, conJetDate) & ";"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

I am making an app more user friendly. Rather than have the user enter
multiple parameters, many times, I placed text boxes on the form and use

[quoted text clipped - 26 lines]

What a I missing?

 

[Allen Browne]

If the query works correctly from the Query window without asking for
parameters, but it asks for parameters when you OpenRecordset(), the 2
possibilities that come to mind are:

a) Something in the query is using the Expression Service (ES).
For example, if the query did include:
Forms.Form1.Text0
and the form was open, the query would not ask for a parameter because the
ES would resolve it to the value in the text box. However, the ES is not
available in the context of DAO recordsets, so the code would ask for a
parameter (even if the form was open).

b) Access is confused about the names.
If you have not unchecked the Name AutoCorrect boxes under:
Tools | Options | General
it is quite common for Access to associate the wrong name with an object.
This behavior in queries can be really weird, including displaying the wrong
column because of a name it used to have, or something in the Caption of one
of the fields, or an alias, or ...

I imagine you are aware of this issue, Scott, but if not, see:
http://allenbrowne.com/bug-03.html
I have given up trying to add new items to this list because there are just
too many, so the list is not up to date with all the current problems, but
it should be enough to warn people away from this misfeature.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks for the reply. I did ensure that all occurences of the parameters
from both queries (both the "qry_Error Check_Missing Start or End" query
and
the underlying query) were removed. In fact, with the form open and the
data
populated in the relevant fields, I can manually open both queries. I am
not
prompted for parameters when opening them ths way.

So, from the database window, both queries open fine, but not when opened
via
the code.

One error (Parameters missing...) suggests that the query has parameters,
while the other (Item not found in this collection) suggests the contrary.

Weird.

P.S. I agree in the dynamic creation of the SQL string via code. I
normally
code similar to this, in my own apps. I "inherited" this one however. I
didn't want to invest a lot of time in this particular portion of the
database as the two dates are always required and the only parameters
necessary. BTW, I would never use spaces AND underscores in the same name
of
an object. In fact I rarely use either. Useless typing IMHO. Hope my
boss
doesn't read this.

Scott, if you have a name in your query that Access does not recognise,
Access assumes it is a parameter. It can be as simple as misspelling a
field
name, e.g. omitting a space, or not adding the square brackets around
names
that start with a number, or contain a space or other special character.

When you say you removed the parameters, I take you that you completely
removed every occurance of [Data Export Begin Date] and [Data Export End
Date] from both queries (not that you merely removed their declaration.)
There is no way this is going to work if the query you are trying to
supply
parameters to refers to another query that also requires parameters.

If just [qry_Error Check_Missing Start or End] query had the parameters
(not
the lower level query), you could supply them as:
qdf.Parameters("[Data Export Begin Date]") =
Forms!frmBilling!txtStartDate
qdf.Parameters("[Data Export End Date]") = Forms!frmBilling!txtEndDate

In practice, is it usually a whole lot easier to just build the SQL
statement as a string in your code instead of relying on saved queries.
That
way you can concatente the values form the form into the string. It is
*much* more flexible for handling cases where the user supplies only some
of
the parameters, and so the others are not needed in the WHERE clause at
all.
Typically the code goes something like this:
Dim rs As DAO.Recordset
Dim strSql As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

strSql = "SELECT * FROM Table1 WHERE [MyDate] Between " & _
Format(Forms!frmBilling!txtStartDate, conJetDate) & " And " & _
Format(Forms!frmBilling!txtEndDate, conJetDate) & ";"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

I am making an app more user friendly. Rather than have the user enter
multiple parameters, many times, I placed text boxes on the form and use

[quoted text clipped - 26 lines]

What a I missing?