Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Tue Mar 8 10:40:45 CST 2005
John:
All true. But where I have a lot of parameter values - like the data
selection criteria in the manufacturing system, I actually build the SQL
statement or report filter in code and push it into the record source or
filter of the report instead of using a stored query. Another brute force
solution. :)
Rocky
----- Original Message -----
From: "John W. Colby" <jwcolby at colbyconsulting.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Tuesday, March 08, 2005 8:26 AM
Subject: RE: [AccessD] Automating parameterized action queries.....
> 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
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>