Query from a function

  • Thread starter Thread starter James
  • Start date Start date
J

James

I have a function that works fine when based off a database. When I want to
base the function off a query it runs into a problem....2 parameters
expected
This is what I'm using

Function AddTimes()

Dim db As DAO.Database

Dim rs As DAO.Recordset



Set db = CurrentDb

Set rs = db.OpenRecordset("WebProxyLog1")



When I change webproxylog1 to the querys name, it has a problem cause its
not getting the 2 parameters the query needs. How do i solve this?

Thanks
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a QueryDef and assign the parameters. E.g.:

Dim db As DAO.Database
dim qd as dao.querydef
Dim rs As DAO.Recordset

Set db = CurrentDb
set qd = db.QueryDef("query name")

qd.Parameter("parameter name1") = value1
qd.Parameter("parameter name2") = value2

Set rs = qd.OpenRecordset()

.... etc. ...

Substitute the query name, parameter names and values w/ your info.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ/SCYechKqOuFEgEQLLOwCgtP+U5sDRmwYd3LdqEOixEd91Pc8An2MN
3K1+3KDdHus80yrf7RgASfaP
=oTiq
-----END PGP SIGNATURE-----
 
Ok, this works fine and dandy
But now if i try to make a form or report based off the original query, it
asks for the 2 parameters then it runs the function and asks for the 2
parameters again. How do I get the parameters that the query asks for to be
passed along to the function?

Thanks a lot
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah... You didn't say you'd be using the query as a RecordSource for a
form. In that case you can use referential parameters in the query.
E.g.:

PARAMETERS forms!form_name!control1 long,
forms!form_name!control2 text;
SELECT *
FROM Table_Name
WHERE col1 = forms!form_name!control1
AND col2 = forms!form_name!control2

Make this a query and save it in the Querys tab. Then set the form's
RecordSource to the query name. Or, just save the SQL as the form's
RecordSource.

For the query to work the "form_name" form must be open and there must
be valid data in the controls (control1 and control2).

To run the query from VBA, use the same set up as I posted before, but
change the querydef's parameter assignment to this:

qd.Parameter(0) = value1
qd.Parameter(1) = value2

Parameter(0) is the forms!form_name!control1 parameter.
Parameter(1) is the forms!form_name!control2 parameter.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRBnF4echKqOuFEgEQJakACfVIk0WZTWuPmh7VfaEUCQFYLyTxEAoKTZ
LlphVLa9DmIkdJAEWQDNJ3Ea
=N2U1
-----END PGP SIGNATURE-----
 
Back
Top