item not found in this collection

  • Thread starter Thread starter R Vaughn
  • Start date Start date
R

R Vaughn

I am trying to modify the criteria parameters in a query "Query1" from a
command button in a form, using VBA code. The following code triggers the
"item not found in this collection" message. I don't know how to correct it.
What am I doing wrong?

Private Sub Command79_Click()
Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")

qdf.Parameters("TagIndex").Value = 1

End Sub
 
Which line gives the error?

If its the "Set qdf = ..." line, Query1 doesn't exist (space in name
perhaps?)

If its the Parameters line, JET can't find a parameter named TagIndex in
Query1.

(Presumably you are then planning to do something else within this Sub, as
qdf will go out of scope when the sub ends.)
 
Thank you. It was the parameters line and I didn't realize that I was
actually referring to a field, not a parameter. Your tip was very helpful.

Regarding your second comment...yes, there was an additional line that
opened the query "Query1".

My objective is to use visual basic to add a criterion to the query
language, to more easily change the criterion's value. I am a novice in this
area and have looked at other related postings but am still struggling.
Would you mind providing some suggestions, especially for using a subroutine
that would append a value from a control (in a form) to the criteria list in
the SQL?
 
A generic routine to pull a SQL statement apart is messy (e.g. Access allows
fields called Where, or there could be nested WHERE clauses in subqueries.)

My preference is to build the SQL statement in code, and then assign it to
whatever.

This may help take a sample SQL statement from SQL View of a query into your
VBA code:
http://allenbrowne.com/ser-71.html
 
Thank you.



Allen Browne said:
A generic routine to pull a SQL statement apart is messy (e.g. Access allows
fields called Where, or there could be nested WHERE clauses in subqueries.)

My preference is to build the SQL statement in code, and then assign it to
whatever.

This may help take a sample SQL statement from SQL View of a query into your
VBA code:
http://allenbrowne.com/ser-71.html

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

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





.
Click to expand...
 
Would you mind providing additional advice? I tried the following code and
triggered a 3065 (cannot run a Select query) message. I also tried the
Docmd.runSQL "..." approach with no luck.



Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")
strSQL = "SELECT FloatTable.DateAndTime," + _
" FloatTable.TagIndex, TagTable.TagName, FloatTable.Val" + _
" FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex
=TagTable.TagIndex" + _
" WHERE (((FloatTable.DateAndTime) > [Forms]![form1]![Text6]) And
(FloatTable.TagIndex) = 0) "+ _
"ORDER BY FloatTable.TagIndex;"

CurrentDb.QueryDefs("Query1").Execute

End Sub
 
Are you wanting to:
a) view the results of the query, or
b) just programmatically examine the query records in your code.

For (a) create a dummy query for viewing, and assign its SQL property. For
example, say you create any old query and name it qry2C, then instead of the
Execute line use:
CurrentDb.QueryDefs("qry2C").SQL = strSQL
DoCmd.OpenQuery "qry2C"

If (b), use OpenRecordset, and you can loop through the records. Here's an
example:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample
Note that the Forms! bit will need modifying for OpenRecordset: concatenate
the value into the string:
" WHERE (((FloatTable.DateAndTime) > " & _
Format([Forms]![form1]![Text6], "\#mm\/dd\/yyyy\#") & _
") And (FloatTable.TagIndex) = 0) "+ _

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

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


R Vaughn said:
Would you mind providing additional advice? I tried the following code
and
triggered a 3065 (cannot run a Select query) message. I also tried the
Docmd.runSQL "..." approach with no luck.



Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")
strSQL = "SELECT FloatTable.DateAndTime," + _
" FloatTable.TagIndex, TagTable.TagName, FloatTable.Val" + _
" FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex
=TagTable.TagIndex" + _
" WHERE (((FloatTable.DateAndTime) > [Forms]![form1]![Text6]) And
(FloatTable.TagIndex) = 0) "+ _
"ORDER BY FloatTable.TagIndex;"

CurrentDb.QueryDefs("Query1").Execute

End Sub




Allen Browne said:
A generic routine to pull a SQL statement apart is messy (e.g. Access
allows
fields called Where, or there could be nested WHERE clauses in
subqueries.)

My preference is to build the SQL statement in code, and then assign it
to
whatever.

This may help take a sample SQL statement from SQL View of a query into
your
VBA code:
http://allenbrowne.com/ser-71.html

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

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





.
Click to expand...
Click to expand...
 
It was option "a" that I needed. Thank you. Again, very helpful.



Allen Browne said:
Are you wanting to:
a) view the results of the query, or
b) just programmatically examine the query records in your code.

For (a) create a dummy query for viewing, and assign its SQL property. For
example, say you create any old query and name it qry2C, then instead of the
Execute line use:
CurrentDb.QueryDefs("qry2C").SQL = strSQL
DoCmd.OpenQuery "qry2C"

If (b), use OpenRecordset, and you can loop through the records. Here's an
example:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample
Note that the Forms! bit will need modifying for OpenRecordset: concatenate
the value into the string:
" WHERE (((FloatTable.DateAndTime) > " & _
Format([Forms]![form1]![Text6], "\#mm\/dd\/yyyy\#") & _
") And (FloatTable.TagIndex) = 0) "+ _

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

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


R Vaughn said:
Would you mind providing additional advice? I tried the following code
and
triggered a 3065 (cannot run a Select query) message. I also tried the
Docmd.runSQL "..." approach with no luck.



Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")
strSQL = "SELECT FloatTable.DateAndTime," + _
" FloatTable.TagIndex, TagTable.TagName, FloatTable.Val" + _
" FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex
=TagTable.TagIndex" + _
" WHERE (((FloatTable.DateAndTime) > [Forms]![form1]![Text6]) And
(FloatTable.TagIndex) = 0) "+ _
"ORDER BY FloatTable.TagIndex;"

CurrentDb.QueryDefs("Query1").Execute

End Sub




Allen Browne said:
A generic routine to pull a SQL statement apart is messy (e.g. Access
allows
fields called Where, or there could be nested WHERE clauses in
subqueries.)

My preference is to build the SQL statement in code, and then assign it
to
whatever.

This may help take a sample SQL statement from SQL View of a query into
your
VBA code:
http://allenbrowne.com/ser-71.html

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

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


My objective is to use visual basic to add a criterion to the query
language, to more easily change the criterion's value. I am a novice
in
this
area and have looked at other related postings but am still struggling.
Would you mind providing some suggestions, especially for using a
subroutine
that would append a value from a control (in a form) to the criteria
list
in
the SQL?


.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top