Running a stored parameter query from a form without pop up boxes.

[Avid Fan]

I know that you can create a query with sSQL = some SQL statment

me.recordsource = sSQL

I have looked at other threads that have talked about using
forms!myform!mycontrol as a parameter this is good but limits the query
to one form.

I remember that there was a way of calling a stored parameter with
something like.

me.recordsource = qryPlanned With mdate1, mdate2

Any easy way to handle this?

 

[PieterLinden via AccessMonster.com]

Sure.
currentdb.querydefs("MySELECTQuery").SQL = some SQL statement

You can call a stored PROCEDURE with
ProcName Param1, Param2....

but Stored Procedures are only available through ADO.

If all you are trying to do is filter a form or report, you can build the
filter for your form in code and then pass it when you do the DoCmd.OpenForm..
. one of the arguments is for the filter...

 

[Marshall Barton]

I know that you can create a query with sSQL = some SQL statment

me.recordsource = sSQL

I have looked at other threads that have talked about using
forms!myform!mycontrol as a parameter this is good but limits the query
to one form.

I remember that there was a way of calling a stored parameter with
something like.

me.recordsource = qryPlanned With mdate1, mdate2

Is this the kind of thing you are looking for:

Dim db as Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs!yourquery
qdf.Parameters("date1parameter") = mdate1
qdf.Parameters("date2parameter") = mdate2

Me.Recordset = qdf.OpenRecordset()

Or, maybe all you want to do is set the form's Filter
property??

Or, if all this is part of opening the form, then the code
that opens the form should be using the OpenForm method's
WhereCondition argument to filter the form's record source.

To much guessing here. You should explain WHAT you are
trying to accomplish instead of asking about how to take the
next step down the path of your attempted solution.

 

[Avid Fan]

Is this the kind of thing you are looking for:

Dim db as Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs!yourquery
qdf.Parameters("date1parameter") = mdate1
qdf.Parameters("date2parameter") = mdate2

Me.Recordset = qdf.OpenRecordset()

This one sounds like what I am looking for. I come from a VPF
background recordsets are difficult for me.

I found something similar.

Set qdf = CurrentDB.OpenQueryDef(qryName)

In Access 2007 OpenQueryDef is not a method of CurrentDB.

Or, maybe all you want to do is set the form's Filter
property??

I am afraid I don't even know what that is.

Or, if all this is part of opening the form, then the code
that opens the form should be using the OpenForm method's
WhereCondition argument to filter the form's record source.

Err ?????

To much guessing here. You should explain WHAT you are
trying to accomplish instead of asking about how to take the
next step down the path of your attempted solution.

Thank you very much for your help!

Sorry I am not deliberately trying to be vague.

This is my first access application I want show planned customer
contact between two dates.

a two date boxes and a form list.

I am doing this to help a friend who has been handed a whole lot of new
reporting requirements and no tools to do the job. The work computer
is locked down so I can't write something in VFP for her. I found
Access 2003 on her machine so I decided to write an Access program.

Why do I even have Access? My wife wanted Publisher for work so I
bought her Office Professional 2007.

 

[Avid Fan]

Sure.
currentdb.querydefs("MySELECTQuery").SQL = some SQL statement

This is for creating a stored procedure in code right?

You can call a stored PROCEDURE with
ProcName Param1, Param2....

Does not work for a stored query

but Stored Procedures are only available through ADO.

DAO is gone now right since Access 97?

If all you are trying to do is filter a form or report, you can build the
filter for your form in code and then pass it when you do the DoCmd.OpenForm..
. one of the arguments is for the filter...

First Access program have avoided filters because in the past in other
environments they have been too slow.

I will look into this Thank you

 

[Marshall Barton]

This one sounds like what I am looking for. I come from a VPF
background recordsets are difficult for me.

I found something similar.

Set qdf = CurrentDB.OpenQueryDef(qryName)

In Access 2007 OpenQueryDef is not a method of CurrentDB.


I am afraid I don't even know what that is.

Err ?????


Thank you very much for your help!

Sorry I am not deliberately trying to be vague.

This is my first access application I want show planned customer
contact between two dates.

a two date boxes and a form list.

I am doing this to help a friend who has been handed a whole lot of new
reporting requirements and no tools to do the job. The work computer
is locked down so I can't write something in VFP for her. I found
Access 2003 on her machine so I decided to write an Access program.

Why do I even have Access? My wife wanted Publisher for work so I
bought her Office Professional 2007.

OK, so tell us what you are trying to accomplish in which
version and we'll try to get you going.

Note that we normally use a form with text boxes for users
to specify filter information and command button*s to run
reports. The code behind a button for a specific report
would use the OpenReport method's WhereCondition argument
(see VBA Help) to filter the report. This approach
eliminates the need for parameters in the report's record
source query. Another benefit is you can design reports and
test them without a filter and apply different filters
without much, if any, impact on the reports.

I assume that you have a handle on the structure of the data
tables and their degree of normalization so we'll let that
ride until there's an issue.

 

[Avid Fan]

OK, so tell us what you are trying to accomplish in which
version and we'll try to get you going.

Access 2007 writing application for Access 2003

OK I have two Text boxes in date format

txtStartDate txtEndDate


I have simple multiple Item form. I want the user to be able to be
able to limit the out put of the query to only the records chosen buy
the user.

This code does not work. Pop up boxes will still appear.
This parameter query still difficult.

The runtime error 3251 Operation not supported by this object
Me.Recordset = qdf.OpenRecordset()

Form_Load

Dim db As Database
Dim qdf As QueryDef


Set db = CurrentDb()
Set qdf = db.QueryDefs!qryCalendar
qdf.Parameters("date1parameter") = #3/23/2010#
qdf.Parameters("date2parameter") = #3/23/2013#

Me.Recordset = qdf.OpenRecordset()


End Sub