Running a stored parameter query from a form without pop up boxes.
in [Queries]
|
Prev: Query Problem
Next: grouping ...
From: Avid Fan on 27 Mar 2010 12:24 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?
From: PieterLinden via AccessMonster.com on 27 Mar 2010 14:15 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... Avid Fan wrote: >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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
From: Marshall Barton on 27 Mar 2010 14:27 Avid Fan wrote: >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. -- Marsh MVP [MS Access]
From: Avid Fan on 27 Mar 2010 20:44 On 28/03/2010 5:27 AM, Marshall Barton wrote: > Avid Fan wrote: >> 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() > 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.
From: Avid Fan on 27 Mar 2010 21:02
On 28/03/2010 5:15 AM, PieterLinden via AccessMonster.com wrote: > 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 > > Avid Fan wrote: >> 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? > |