John W. Colby
jwcolby at colbyconsulting.com
Tue Mar 8 10:26:29 CST 2005
Rocky,
See my posts re "Filters".
The downside to your method is you have to have a function GetParameterX for
each form / control. Which means building a new function for every one of
these. Another downside is that the form has to be open or the query won't
run. BTW I call this "pull" technology as it "pulls" the information from
the form.
For testing purposes it is sometimes convenient to not need the form open,
just feed values in from the debug window. Furthermore you can use
OnCurrent to feed PK values into a filter etc without needing a text box to
hold the PK value for your method.
The alternative is "push" where the form "pushes" the value into a Fltr() as
the control changes. Thus in AfterUpdate of a text box you would use:
Fltr "MyTxtBoxName", MyTxtBox.Value
The query now uses Fltr("MyTxtBoxName") instead of GetParameterX(). You now
just add new filter values to the Fltr() as you need them. No writing a new
function every time.
John W. Colby
www.ColbyConsulting.com
Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin -
Beach Access Software
Sent: Tuesday, March 08, 2005 11:10 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Automating parameterized action queries.....
Sander:
I always take parameters from a form. Don't like to let the users muck
around in the database container. So get the parameter into the query I
make a
Public Function GetParameterX
GetParameterX=Forms!frmWhatever!txtBoxHoldingParameter
End Function
Then in the query under criteria I put GetParameterX() and it fetches the
contents of the text box from the form when it runs.
Probably not the most elegant solution but it works and its easy. But you
have to run the query from the form.
The users I write for prefer it. Almost none of them would know how to run
a query from the database container and most don't even know what a query
is.
HTH
Rocky Smolin
Beach Access Software
http://www.e-z-mrp.com
858-259-4334
----- Original Message -----
From: "Sad Der" <accessd666 at yahoo.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Tuesday, March 08, 2005 6:33 AM
Subject: RE: [AccessD] Automating parameterized action queries.....
>I also thought of that but I hoped their was a way to
> fill the parameters per query with form input.
>
> That way i could make a form for the users in wich
> they can enter:
> - a new report
> - queries per report
> - parameters per query
> - parametertype per parameter (eg date, number, etc)
>
> With this info I could, based on a report selection
> build a form dynamicly.
>
> Well I have to look at this some more and let the
> customer decide.
>
> Regards,
>
> Sander
>
> --- Bobby Heid <bheid at appdevgrp.com> wrote:
>> Why not have two sets of queries, one set that looks
>> to a form (this one is
>> called programmatically), and one set that queries
>> the user for the
>> parameters?
>>
>> Bobby
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On
>> Behalf Of Sad Der
>> Sent: Tuesday, March 08, 2005 8:10 AM
>> To: Access Developers discussion and problem solving
>> Subject: RE: [AccessD] Automating parameterized
>> action queries.....
>>
>>
>> You are correct. However the queries are not
>> executed
>> via from several forms but they are executed:
>> 1- via one form
>> 2- a user manually clicks it in the database
>> container.
>>
>> The process is as follows:
>> 1 - the reports (xls files) are stored in the
>> database
>> 2 - the queries that create the report are also
>> stored
>> in the database
>> 3 - the user selects a report
>> 4 - the necessary queries are executed one by one
>>
>> However, several times a week a manager requests a
>> part of the report that is slightly different from
>> the original. A user then
>> must have the change to quickly adjust the query and
>> run it manually.
>>
>> HTH
>>
>> Sander
>>
>>
>> --- Bobby Heid <bheid at appdevgrp.com> wrote:
>> > If you are not wanting to encode a particular form
>> > field because the query
>> > is called from several forms, might I suggest that
>> > you create a new hidden
>> > form that contains fields for all of the fields
>> that
>> > are used as parameters.
>> > Then the queries can access these fields.
>> >
>> > You would need to set the fields on the form
>> before
>> > the queries were
>> > executed.
>> >
>> > Bobby
>> >
>> > -----Original Message-----
>> > From: accessd-bounces at databaseadvisors.com
>> > [mailto:accessd-bounces at databaseadvisors.com] On
>> > Behalf Of Sad Der
>> > Sent: Tuesday, March 08, 2005 3:15 AM
>> > To: Acces User Group
>> > Subject: [AccessD] Automating parameterized action
>> queries.....
>> >
>> >
>> > Hi group,
>> >
>> > my story of the queries continues. I need to run a
>> > set
>> > of action queries. This works fine.
>> >
>> > Several queries have parameters. So that means the
>> > user has to watch the screen for a parameter to
>> > pop-up. Is it possible to add some parameter
>> fields
>> > in
>> > the form to fill the required parameters...WITHOUT changing the
>> > query it self?
>> >
>> > So I have a parameters in my query:
>> > [Enter version:]
>> > [Enter startdate:]
>> >
>> > And I do not want to change these to:
>> forms!frmReport!txtVersion
>> > forms!frmReport!txtStartDate
>> >
>> > TIA
>> >
>> > Sander
>> >
>> > --
>> > AccessD mailing list
>> > AccessD at databaseadvisors.com
>> >
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> > Website: http://www.databaseadvisors.com
>> >
>>
>> __________________________________________________
>> Do You Yahoo!?
>> Tired of spam? Yahoo! Mail has the best spam
>> protection around
>> http://mail.yahoo.com
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com