Parameter Context in Query

  • Thread starter Thread starter lopemanc
  • Start date Start date
L

lopemanc

Hi All,

Have not dug deep into MS Access in quite some time, so please realize
I am probably missing something simple. Thanks for your patience.

I have created a form. As part of my validation I run a query from VBA
code. After I figured out that I needed to evaluate the parameters
before fetching the records this worked well. As created the query
contains something like:
"Forms![Compact Control Editor]![CGE_Control_Index] "

However, I am trying to get a little reuse out of the query and
understand MS Access better. I really want to use the same query on a
different form. This is a problem since the "[Compact Control Editor]"
ties it to a single form. I can certainly keep the field name
consistent across forms. So what I really need is a way the query to
reference the current form. I have tried several different object and
still do not have success.

Thanks For Your Help,

Chris
 
The only two options you have in terms of parmaters are tying it to a
specific control on a specific form, or making it a pop-up parameter.

If you run the query from a form, you can programmatically set the value of
the parameter before open the recordset, or you could dynamically change the
SQL to include the value of the control.
 
As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignored?. So, if the form expression is not entered, then your
application usually fails!!

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query, BUT NO FORMS! conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

Take a look at the following screen shots to see what I mean:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.
 
I thank you all for your responses. And I understand the reasoning as
to why you are suggesting moving away from Forms references in the
Queries. I may yet follow your advice. But I am not convinced at this
time.

Luckily I was able to answer my own question and solve my problem. I
now realize that my question was not presented perfectly. I asked for
the "Parameter Context in a Query". Truth is this really does not
matter. It is the Context of the Eval function that matters. And
while this context should be somewhat flexible as the documentation
implies, it currently has but one context available...an Application
object.

The application contains a code context object and that contains the
current form. And the rest is pretty straight forward. I can now have
a query that will work against multiple forms. I just need to do
something like the following in the query:

[CodeContextObject].[Form].[Field1]

Maybe I am missing something that will break this. But it seems to
work pretty well at the moment.

Thanks Again,

Chris Lopeman
 
expressions in queries is bad...So, the solution use now is simply to
take the values from the form, and build your own where clause in code.
<<
specific control on a specific form, or making it a pop-up parameter.
<<

Please help me understand why two extremely knowledgeable people have
not suggested something which seems the most obvious answer to me i.e.
a generic SQL procedure?

I'm thinking along the lines of:

CREATE PROCEDURE GetCustomerDetails
(arg_cumstomer_number CHAR(10) NOT NULL)
AS
SELECT last_name, first_name
FROM Customers
WHERE cumstomer_number = arg_cumstomer_number;

or if you prefer the 'non-ANSI mode' version:

PARAMETERS arg_customer_number Text ( 10 );
SELECT last_name, first_name
FROM Customers
WHERE customer_number = arg_customer_number;

Someone seems to be missing something; is it me?

TIA,
Jamie.

--
 
onedaywhen said:
expressions in queries is bad...So, the solution use now is simply to
take the values from the form, and build your own where clause in code.
<<

specific control on a specific form, or making it a pop-up parameter.
<<

Please help me understand why two extremely knowledgeable people have
not suggested something which seems the most obvious answer to me i.e.
a generic SQL procedure?

I'm thinking along the lines of:

CREATE PROCEDURE GetCustomerDetails
(arg_cumstomer_number CHAR(10) NOT NULL)
AS
SELECT last_name, first_name
FROM Customers
WHERE cumstomer_number = arg_cumstomer_number;

or if you prefer the 'non-ANSI mode' version:

PARAMETERS arg_customer_number Text ( 10 );
SELECT last_name, first_name
FROM Customers
WHERE customer_number = arg_customer_number;
Click to expand...

Don't you still have to get the value from the form's control into the
query? That's what I was talking about in the first part of the second
paragraph, which you didn't quote:

"If you run the query from a form, you can programmatically set the value of
the parameter before open the recordset, or you could dynamically change the
SQL to include the value of the control."

Correct me if I'm wrong, but isn't your suggestion the same as the "pop-up
parameter" option I mentioned?
 
Douglas said:
Don't you still have to get the value from the form's control into
the query?
Click to expand...

Not exactly. Rather, you would invoke the proc with the parameter
values. No dynamic SQL, no hard-coding of Access object names in Jet
objects.
isn't your suggestion the same as the "pop-up
parameter" option I mentioned?
Click to expand...

I'm not sure. By "pop-up parameter" option, I though you mean something
more like:

SELECT last_name, first_name
FROM Customers
WHERE customer_number = [Please enter your customer number];

which is quite a bit different.

Do you see what I mean?

Thanks again,
Jamie.

--
 
onedaywhen said:
Don't you still have to get the value from the form's control into
the query?
Click to expand...

Not exactly. Rather, you would invoke the proc with the parameter
values. No dynamic SQL, no hard-coding of Access object names in Jet
objects.
isn't your suggestion the same as the "pop-up
parameter" option I mentioned?
Click to expand...

I'm not sure. By "pop-up parameter" option, I though you mean something
more like:

SELECT last_name, first_name
FROM Customers
WHERE customer_number = [Please enter your customer number];

which is quite a bit different.
Click to expand...

We're quibbling about semantics, in my opinion.

You still need VBA code to pass the appropriate value to the query, don't
you, which is what I said before.
Do you see what I mean?
Click to expand...

To be honest, I don't see what difference there is between

SELECT last_name, first_name
FROM Customers
WHERE customer_number = [Please enter your customer number];

and

PARAMETERS [Please enter your customer number] Text;
SELECT last_name, first_name
FROM Customers
WHERE customer_number = [Please enter your customer number];

other than explicitly declaring the type of the parameter. The second
declaration above is no different than what you posted

PARAMETERS arg_customer_number Text ( 10 );
SELECT last_name, first_name
FROM Customers
WHERE customer_number = arg_customer_number;

other than the name of the parameter
 
Because, likely we are using that query for a report, or a form.

Where exactly do we want to send the results of the query to? 99% of the
time, it is going to be a report, or a form....

So, we are going to open a report, or open a form....write me your code that
will supply the parameter, and then open the report...

What does your code look like.......

my looks like:

dim strCity as string

strCity = inputbox("reprot for what city")

if strCity = "" then exit sub

docmd.openreport "myreprot",acviewPreview,,"City = '" & strCity & "'"

So, the above prompts for a city, and the report then runs...I count 4 lines
of code....

now,. show how to use your proc for the above report....
 
Douglas said:
To be honest, I don't see what difference there is between

SELECT last_name, first_name
FROM Customers
WHERE customer_number = [Please enter your customer number];

and

PARAMETERS [Please enter your customer number] Text;
SELECT last_name, first_name
FROM Customers
WHERE customer_number = [Please enter your customer number];

other than explicitly declaring the type of the parameter.
Click to expand...

Agreed. Although strong data typing of parameters is a worthy point
IMO, the truth is I made a mistake here. Humble apologies and sincere
thanks for taking the time to reply (ditto Albert).

Jamie.

--
 
Back
Top