John

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have query (saved as Qu1)

Select * From Table1 Where A= Parameter B

How can pass the parameter into query?

Or how can I build a store procedrue in MS Access 2002. Please show me a
simple sample.

Thanks
 
SELECT * From Table1 WHERE Table1.somefieldnamegoeshere = [EnterParameter]

Enter your field name and in the "EnterParameter" you can type whatever
prompt you'd like your users to see when running the query.
 
Use a form where the user can enter Parameter B. Your query then looks like:

Select * From Table1 Where A=
Forms!MyForm!NameOfControlWhereParameterBWasEntered
 
Hi,


You have to use DoCmd. If you use CurrentDb, you will get an error
(about a missing parameter, see Andy's comment at
http://www.mvps.org/access/queries/qry0013.htm). It is unfortunate that
DoCmd does not work well with a SELECT query, RunSQL only accept "action"
query, so you will end up probably by using the parameters collection
(since there is one in ADO and one in DAO, you will choose to DIM the
Parameter with the library suffix);


Dim qdf As QueryDef : Set qdf=CurrentDb.QueryDefs("myQuery")
Dim x As DAO.Parameter

For each x in qdf.Parameters
x.Value = eval(x.Name)
Next x

' now, open the recordset

Dim rst As DAO.Recordset
Set rst=qdf.OpenRecordset( )



Note that the loop assume the name of the argument is in a syntax that 'PC
Datasheet' proposed: FORMS!FormName!ControlName and that the required form
is open, and that the data has been validated, at the control level (by
opposition to being edited, with the cursor still in the control in progress
of being modified). That means that if you clicked on a button to run the
code, like above, then you are ok, as example. For more details, consult the
supplied reference at the start.



Hoping it may help,
Vanderghast, Access MVP
 
John said:
how can I build a store procedrue in MS Access 2002. Please show me a
simple sample.
Click to expand...

I assume you mean a Jet database:

CREATE PROCEDURE MyStoredProc
(argKeyValue INTEGER)
AS
SELECT MyDataCol
FROM MyTable
WHERE MyKeyCol = argKeyValue
;

Execute this via ADO and the OLE DB provider for Jet 4.0.

You can execute the procedure using:

EXECUTE MyStoredProc 55;

Jamie.

--
 
Back
Top