passing multiple parameters from Access form to query

[jcaulder]

I am attempting to pass multiple parameters from an Access form
to a predesigned query that was also built in Access. The basic
idea is that I want to allow the user to enter various query criteria
via the form then use those values in the WHERE clause of the
query. I do not want to create the query in code using recordset
objects. I want to use the query design aspect of Access to build
the query. From much trial and error, I have determined that
Access seems to only allow the object with current focus on the
form to pass its value to the query. So if I have two text box
values I want to pass a query, only the value with the focus gets
sent, the other apparently sends NULL.

Example:
form1.text1.setfocus
form1.text1.text = 04/22/02
form1.text2.setfocus
form1.text2.text = 04/23/02
docmd.openquery("queryname&quot

***the query, as stated above is already created in Access
***the WHERE clause is basically like the following. . .
WHERE order_date >= form1.text1.text AND
order_date<form1.text2.text

In this example because text1 no longer has the focus, the query
pulls in NULL for this value whereas the text2 pulls in 04/23/02.

Is there anyway to pass multiple values from an Access form to an
Access query?

TIA!!

 

[jebry]

Hi!

Try it like this:

form1.text1.Value = 04/22/02
form1.text2.Value = 04/23/02
docmd.openquery(&quot;queryname&quot

Your query will look at the value only and not the text.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[jcaulder]

Worked perfectly!! Thanks for the help!!

It still seems a little unusual that it will only allow one text_box.text to be passed correctly while the other passes NULL. That's what really had me confused. If neither had worked, I would have moved on to another property. I guess there must be a reason for it. Similarly, I've discovered an object must have focus before its properties can be changed(or accessed at all I guess). I'm obviously new at Access but not at programming. I can't recall any other OOL that exhibits this behavior. Is it just me or is there a reason for this?

If you don't want to go into the theory of Microsoft, I'll understand.

Thanks again!!

Jonathan
jonathan_caulder@unc.edu