Use query like a stored procedure in Access 97 1

[btalon]

I am wanting to use a query like a stored procedure and pass it variables on the fly using VBA. In the past I have assigned the variable to some sort of input control to get the information, but I don't want to hardcode that object into the query. I wan't to pass it the information when I call the query. Can this be done in Access 97?

 

[hneal98]

Yes. Just add a parameter to the query in the field or crietia portion like this

UPDATE DISTINCTROW tblCred SET tblCred.YearID = [ENTER YEAR]
WHERE (((tblCred.YearID)=[ENTER YEAR]))
WITH OWNERACCESS OPTION;

See the "[]"? those tell access to prompt and expect a value.

The other way to do it would be to use a field in a form as a criteria value for the query. You create your form, create the field you want to use as a criteria and then set up or query as usual, but add the field as a criteria.

Let me know if you need any more help.

 

[PHV]

pass it variables on the fly using VBA
You may play with the Parameters collection of the DAO.QueryDef object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[btalon]

I have created queries in the past with a form input hardcoded in the query "WHERE....LIKE txtTextbox". That is not what I'm trying to accomplish. I want to call the query in from the VBA code similar to the following:

db.execute "queryname", variable1, variable2.

I don't want to hardcode the name of a form and textbox into the query. I want to be able to re-use the query with different forms.

 

[JerryKlmns]

btalon,

It looks like an action query. You could also consider to use a Command object which has a Parameters collection to pass your variable values.

Take a look on help files about Command Object

 

[jsteph]

btalon,
You could do something like:

function RunMyQuery(byval variable1,byval variable2)
dim db as dao.database, qd as dao.querydef

set db =currentdb
set qd = db.querydefs("somequery_With2ParametersNamedAsBelow")
qd.Parameters("[enter variable 1]")  = variable1
qd.Parameters("[enter variable 2]")  = variable2
qd.execute

--Jim

 

[btalon]

jsteph, that worked beautifully. A star for you. Thanks.

 

[btalon]

I should mention that I couldn't use the qd.execute. The execute won't work with a select query so I opened a recordset off of the queryDef object.

Set db = CurrentDb
Set qryDef = db.QueryDefs("qryBookedInvoicedByGraphic")
qryDef.Parameters("[@startDate]") = txtStartDate
qryDef.Parameters("[@endDate]") = txtEndDate
Set rs = qryDef.OpenRecordset

 

[btalon]

I should mention that I counldn't use the qd.execute. The execute is only allowed with an action query. So I opened a recordset off of the queryDef object:

Set db = CurrentDb
Set qryDef = db.QueryDefs("qryBookedInvoicedByGraphic")
qryDef.Parameters("[@startDate]") = txtStartDate
qryDef.Parameters("[@endDate]") = txtEndDate
Set rs = qryDef.OpenRecordset

 

[btalon]

I should mention that I couldn't use the qd.execute. The execute won't work with a select query so I opened a recordset off of the queryDef object.

Set db = CurrentDb
Set qryDef = db.QueryDefs("qryBookedInvoicedByGraphic")
qryDef.Parameters("[@startDate]") = txtStartDate
qryDef.Parameters("[@endDate]") = txtEndDate
Set rs = qryDef.OpenRecordset

 

[jsteph]

btalon,
Yes...I had looked at the post from hneal98 and assumed it was an Update query, but your alteration is the right thing for a Select,
--Jim

 

[btalon]

My next goal is to open a report passing the parameters down to the query the report is running off of. I haven't been able to get this to work successfully so far.

In the past I have set the recordsource for the report to a recordset in the Report_Open sub, but I had to code the Detail_Print section and the all the header and footer sections to get the data to display. When you preview the report, you can't print it because the recordset is closed after the report opens. The only other option is to send the report straight to the printer without a preview. So this option isn't a good one.

Any other ideas would be great.

 

[jsteph]

If you set the .recordsource property in a command button that opens the report, you shouldn't have any problems, you don't want to do it in the Open event. You open the report in design mode, set the source, then .save, then open it.

Users should typically be opening reports via a command button, so this should work well.
--Jim

 

[jsteph]

...an alternative is to set the .sql of a querydef which is the report's recordsource...this way you don't have to open the report in design view. Either way you will by dynamically setting the entire sql, including the 'parameters', which are now just a new Where clause.
--Jim

 

[btalon]

I've always used the DoCmd to open the report, but it doesn't really give you any programming flexibility besides the criteria unless you do the coding on the report itself.

I haven't been able to find another way to open a report using VBA where I can set the recordsource when it is opened without coding the report_open itself. Is there another way to open the report besides DoCmd?

 

[jsteph]

btalon,
When you use the docmd.openreport, you don't use any criteria--you've set that prior to the .openreport by either setting the .Sql of the report's Querydef source, which includes all the criteria, or setting the reports Recordsource in code (by first opening in designview, the calling the .Save method, then opening normally).

Really, you can accomplish anything in code for the report if you have the user open the report via a command button (or selecting from a listbox, etc). I've had reports where the grouping levels, fields,label positioning, etc, weren't known until the user selected all his criteria. The report was essentially built from scratch in code prior to the .OpenReport method.

So I'm not sure exactly what else it is you're really looking for--can you be more specific?
--Jim