Openrecordset with parameter query

  • Thread starter Thread starter Reggie Laffond
  • Start date Start date
R

Reggie Laffond

I am trying to create a recordset in code which has as its source a query
that has a user parameter (in this case a four digit year). I assumed that
when the Openrecordset fired that the user would be prompted to enter the
parameter. Instead an error dialog box pops that states something like "Too
few arguments - expected 1". I'm sure it's because the query is not being
passed any parameters by the openrecordset method. Is there a way to
accomplish what I am trying to do? I could prompt the user for the year
prior to the code executing but how can I then get it to the query.

Thanks in advance
 
you could use the InputBox function in the code to get the value. then use
an SQL statement, which includes the return value of the InputBox function,
as the source for the recordset.
 
Reggie said:
I am trying to create a recordset in code which has as its source a query
that has a user parameter (in this case a four digit year). I assumed that
when the Openrecordset fired that the user would be prompted to enter the
parameter. Instead an error dialog box pops that states something like "Too
few arguments - expected 1". I'm sure it's because the query is not being
passed any parameters by the openrecordset method. Is there a way to
accomplish what I am trying to do? I could prompt the user for the year
prior to the code executing but how can I then get it to the query.
Click to expand...


With DAO, you can use the querydef method of opening a
recordset:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strParmValue As String
strParmValue = InputBox( . . .
Set db = CurrentDb()
Set qdf = db.QueryDefs("nameofquery")
qdf.Parameters(0).Value = strParmValue
Set rs = qdf.OpenRecordset(dbOpenDynaset)
. . .
rs.Cloase : Set rs = Nothing
Set qdf = Nothing
Set db = Nothing
 
I am trying to create a recordset in code which has as its source a query
that has a user parameter (in this case a four digit year). I assumed that
when the Openrecordset fired that the user would be prompted to enter the
parameter.
Click to expand...

Wrong assumption. Doesn't happen! Go the way that Marshall suggested.

HTH,
TC


Instead an error dialog box pops that states something like "Too
 
Very Slick and it makes good sense to me. You don't know how much you don't
know.

Thanks Marshall ...... and all!
 
Back
Top