running a inut parameter query

[Guest]

I am relatively new to Access. Can someone show me an example of how I would
run an input parameter Query from VB

Something to the likes of
DoCmd.OpenQuery "QRYFAY3test" Parm1, Parm2, parm3, acViewNormal, acReadOnly

 

[John Vinson]

I am relatively new to Access. Can someone show me an example of how I would
run an input parameter Query from VB

Something to the likes of
DoCmd.OpenQuery "QRYFAY3test" Parm1, Parm2, parm3, acViewNormal, acReadOnly

You need to evaluate the members of the Parameters collection. It's
not particularly obvious... <g>

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim prm As Parameter
Set db = CurrentDb
Set qd = db.Querydefs("QRYFAY3test")
For Each prm In qd.Paramters
prm.Value = Eval(prm.Name) ' e.g. if the paramter is
' [Forms]![frmMyForm]![txtCritA]
' find out what's there
Next prm

What you do next depends on the type of query: if it's an Action query
use

qd.Execute dbFailOnError

and trap any query errors in the procedure's On Error block; if it is
a Select query, it's generally best to display the results in a Form
by setting the Form's recordsource, but you can use OpenQuery if you
don't mind users seeing query datasheets. I try to avoid doing so
though.


John W. Vinson[MVP]