Object Control Reference problem

[Brett]

Can you tell me what I'm doing wrong here. I'm trying to
simply set a query field criteria to a value. I get an
Error 424 on the line where I try to set the control's
criteria equal to a integer:

Option Compare Database
Dim Year As Integer

Function SetYear()
Set dbs = CurrentDb
Year = InputBox("Enter the year:")
DoCmd.OpenQuery "qryFileNetPercentUptime: quarter 1",
acViewDesign
Query![qryFileNetPercentUptime: quarter 1]![Beginning
Date].Criteria = Year
End Function

 

[Tim Ferguson]

DoCmd.OpenQuery "qryFileNetPercentUptime: quarter 1",
acViewDesign
Query![qryFileNetPercentUptime: quarter 1]![Beginning
Date].Criteria = Year

If you just want to open a query datasheet, you don't have to put up the
input box first: if the query is created with the parameter then the DoCmd
object will put up its own input box to get the value.

An alternative, if you want more control, is to change the parameter in the
query to something that the expression engine can see, such as a control on
an open form:

....WHERE Year(ContractDate) = Forms!"frmPickAYear"!txtYearNumber

Finally if you are after the recordset itself, you can use the QueryDef
object:

Set qdf = QueryDefs("qryFileNetPercentUptime")
qdf.Parameters("[Beginning Date]") = 1998

Set rs = qdf.OpenRecordset(dbSnapshot, dbForwardOnly)
' etc etc


Hope that helps



Tim F

 

[brett]

I think your last suggestion is what I'm looking for; I'm
trying to make the code change the criteria of the query
for one of the query fields. I attempted what you said
and the qdf.parameters line fails with a 3265 error ("Item
Not Found In This Collection"). Am I still doing
something wrong??? Please see the code below and I am
using Access 2000 (not 97).

Dim Year As Integer

Function SetYear()
Set dbs = CurrentDb
Year = InputBox("Enter the year:")
Set qdf = dbs.QueryDefs("qryFileNetPercentUptime:
quarter 1")
qdf.Parameters("[beginning date]") = Year
End Function

-----Original Message-----

DoCmd.OpenQuery "qryFileNetPercentUptime: quarter 1",
acViewDesign
Query![qryFileNetPercentUptime: quarter 1]! [Beginning
Date].Criteria = Year

If you just want to open a query datasheet, you don't have to put up the
input box first: if the query is created with the parameter then the DoCmd
object will put up its own input box to get the value.

An alternative, if you want more control, is to change the parameter in the
query to something that the expression engine can see, such as a control on
an open form:

....WHERE Year(ContractDate) = Forms!"frmPickAYear"! txtYearNumber

Finally if you are after the recordset itself, you can use the QueryDef
object:

Set qdf = QueryDefs("qryFileNetPercentUptime")
qdf.Parameters("[Beginning Date]") = 1998

Set rs = qdf.OpenRecordset(dbSnapshot, dbForwardOnly)
' etc etc


Hope that helps



Tim F

.

 

[Tim Ferguson]

I attempted what you said
and the qdf.parameters line fails with a 3265 error ("Item
Not Found In This Collection"). Am I still doing
something wrong??? Please see the code below and I am
using Access 2000 (not 97).

Check the spelling of the parameter name -- thinking about it, you may not
need the [brackets] in this situation.

As another thought, using Year as a variable name is not a good idea, as
it's a reserved word in VBA (it's the name of the Year() function). It's
common to tag variable names with their data type, so you might try
something like dwYear or lngYear if it's a long integer.

As yet another thought, I notice you are not using Dim statements, so
presumably you are not using Option Explicit in all your modules. This
option is Highly Recommended, and will save you from all sorts of errors by
picking up when you misspell a variable name (like Yaer), and it's good
discipline to decide what a variable is going to hold and how you are going
use it. You're not writing code to be able to read it now: you are going to
have to decode it again in two years time....

Hope that helps


Tim F