Passing a variable to a query

[reach_dev]

Hi,

I am developing an access solution, not having used VBA for 7 years. Thrown in at the deep end some might say.

My problem is thus;

I am going to need to generate reports, based upon user input (OrderID). However, this order ID is not provided by a user, in the form of a text box but is selected from a multicolumn listbox. I know how to generate a query based upon the contents of a form field, textbox etc but is it possible to reference a variable stored within an open form from a query.

Code which sets the variable is here;
http://rafb.net/paste/results/fksKn365.html

OrderID is simply a global variable, used in other parts of the system.

 

[ted.martin]

The easiest solution is to somewhere within the form code make the variable Me.tag = OrderID.text ( or OrderID.Value)

In your query; in the criteria use : [Forms]![FormName].[tag]

NOTE: The form MUST remain open when the query or report is called in order to pass through the tag value. Once the query has been called, you can close the form if you want too.

There are other ways through Filtering code when you open Form or Report from which the query runs but this is the simplest way.

 

[reach_dev]

The easiest solution is to somewhere within the form code make the variable Me.tag = OrderID.text ( or OrderID.Value)

In your query; in the criteria use : [Forms]![FormName].[tag]

NOTE: The form MUST remain open when the query or report is called in order to pass through the tag value. Once the query has been called, you can close the form if you want too.

There are other ways through Filtering code when you open Form or Report from which the query runs but this is the simplest way.

Cheers mate, thats veery helpful. Just got taken off my access project to work on my predecessors ASP CMS. Its so longwinded! Arg!

 

[prenticehill]

I have a similar question. I need to pass a variable to a query and it needs to hold more than one value. How can I do this?

 

[gemma-the-husky]

an equal easy way is to dereference the variable

if you have a global variable then set it in code

myvar = whatever


however to USE it in a query, you need to read it with a function

function myfunc as whatevertype
myfunc = myvar
end function

then in your query use
=myfunc()

you can use parameters to the function call


I find this method far more flexible and powerful than using form controls.

[and now I see this was a really old thread ...]

 

[Galaxiom]

Dave has explained the most versatile technique.

Another is to write the values to fields in a single record table and include the table in the query.

Another way, but only in 2007, is by setting a TempVar.
These can be referred to in queries.

http://msdn.microsoft.com/en-us/library/bb257083.aspx