Passing a query parameter using code to feed a subform

[Lester Lane]

Hello,
Can someone please help with this issue I have in wanting to reuse a
query that is the source for a subform that is used across my database
by many main forms. The query is itself linked to a grouped query
(Max of date) so it potentially has many records behind it. It
basically retrieves the latest price of stock. I want to pass a
parameter to this query (StockID) to a) speed up Access loading the
form (because I believe that unless you have a MDE it "tests" your
query on loading) and b) keep my queries to one per job.

Should I pass the parameter to the final query (a select query) or the
embedded grouped query? I.e. does the select query "pass" the
parameter by use of the join it has? It's been a while since my SQL
course!

If I change the main form combo to choose another stock, then this
subform must of course requery and pass the new parameter. It does
work with no parameter and using LinkFields for the subform but it is
slow. If I use a criteria of Forms!frmForm!field style then this will
only work for one form. Pointers most welcome!

Using Access 2003 on XP.

 

[Dale Fye]

Lester,

The way I handle this is to create a function. You cannot use a global
variable in a query, but you can us a function, so here is how I do it.
Assuming that you have a value that is an integer, declare the function as an
integer and give it an optional parameter that is a variant set to default to
NULL. You'll see why when you look at the code.

Public Function fnSomeValue(Optional SomeValue as Variant = Null) as Integer

Static myValue as integer

'If a value is passed then set the static variable to the value
is not isnull(SomeValue) then myValue = SomeValue

'Since it is declared as an integer myValue will default to zero
fnSomeValue = myValue

End Function

Now you can set the value of this function in any one of your forms and can
call it as a column or in a where clause of a query.

An example of how I might use this is in the AfterUpdate of a combo box.
Lets assume that the combo box has an All option that has a value of zero, an
the other values are all integers.

Private sub cbo_SomeCombo_AfterUpdate

Call fnSomeValue(me.cbo_SomeCombo)

End Sub

HTH
Dale

 

[Dale Fye]

Actually, that should be:

If not isnull( )

instead of:

Is not null( )

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Lester Lane]

Lester,

The way I handle this is to create a function. You cannot use a global
variable in a query, but you can us a function, so here is how I do it.
Assuming that you have a value that is an integer, declare the function as an
integer and give it an optional parameter that is a variant set to default to
NULL. You'll see why when you look at the code.

Public Function fnSomeValue(Optional SomeValue as Variant = Null) as Integer

Static myValue as integer

'If a value is passed then set the static variable to the value
is not isnull(SomeValue) then myValue = SomeValue

'Since it is declared as an integer myValue will default to zero
fnSomeValue = myValue

End Function

Now you can set the value of this function in any one of your forms and can
call it as a column or in a where clause of a query.

An example of how I might use this is in the AfterUpdate of a combo box.
Lets assume that the combo box has an All option that has a value of zero, an
the other values are all integers.

Private sub cbo_SomeCombo_AfterUpdate

Call fnSomeValue(me.cbo_SomeCombo)

End Sub

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Wow this looks promising. Just so I've got the whole thing in my
head... can't try this til I get home
I understand the function: Nulls end up as 0 and I assume 0 ends up as
0 if the All option was selected in the combo box (would love to know
how you weave in this option assuming the combo is based on a query).
I understand the calling of the function too! Basically setting the
static value and in turn the integer value of the function. What I'm
not sure is what/how you would add this to the query. Would you just
put [fnSomeValue] in the criteria row?
So after the update of a combo I can set the value and requery the
subform - brilliant! and thanks a lot.

 

[Dale Fye]

Lester,

You could put it in a criteria row like:

WHERE [SomeField] = fnSomeValue()

Or, if you have the All option in a listbox, you might do something like:

WHERE fnSomeValue() = 0 or fnSomeValue = [SomeField]

The other thing that is nice is that you can use that value in the Open
event of a form, to set the value of a combo box in the new form, or if it
is an ID value, you could avoid having to pass the value in the OpenArgs and
just have the form automatically find that record. All sorts of uses for
this, but my favorite is queries that are data sources for forms or reports.

HTH
Dale

Lester,

The way I handle this is to create a function. You cannot use a global
variable in a query, but you can us a function, so here is how I do it.
Assuming that you have a value that is an integer, declare the function
as an
integer and give it an optional parameter that is a variant set to
default to
NULL. You'll see why when you look at the code.

Public Function fnSomeValue(Optional SomeValue as Variant = Null) as
Integer

Static myValue as integer

'If a value is passed then set the static variable to the value
is not isnull(SomeValue) then myValue = SomeValue

'Since it is declared as an integer myValue will default to zero
fnSomeValue = myValue

End Function

Now you can set the value of this function in any one of your forms and
can
call it as a column or in a where clause of a query.

An example of how I might use this is in the AfterUpdate of a combo box.
Lets assume that the combo box has an All option that has a value of
zero, an
the other values are all integers.

Private sub cbo_SomeCombo_AfterUpdate

Call fnSomeValue(me.cbo_SomeCombo)

End Sub

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Wow this looks promising. Just so I've got the whole thing in my
head... can't try this til I get home
I understand the function: Nulls end up as 0 and I assume 0 ends up as
0 if the All option was selected in the combo box (would love to know
how you weave in this option assuming the combo is based on a query).
I understand the calling of the function too! Basically setting the
static value and in turn the integer value of the function. What I'm
not sure is what/how you would add this to the query. Would you just
put [fnSomeValue] in the criteria row?
So after the update of a combo I can set the value and requery the
subform - brilliant! and thanks a lot.