Problems enabling a drop down on a query

[rachelm920]

I am attempting to add a drop down prompt for the "area" to a query in my db.
So far I have created a form and titled it "AreaSelect". I then created a
Combo Box that pulls the data from the area table and I titled it "Area" I
saved the form. Then I went into the query and added all the information from
the table that I needed and under the area criteria I put [Forms]![AreaSelect]
![Area]. I saved the query. Then I went back into the form in form view and
selected an area from the combo box, and hit the tab button. When I attempt
to run the query it still gives me a Enter Parameter Value prompt stating
"Forms!AreaSelect!Area. If I type in the same exact area it gives me that
data. If I type nothing I get a blank query and if I click cancel it does
nothing at all. I've researched this and this is the way I am supposed to be
doing the drop down, but for some reason it isn't working.

My DB is an Access 2000 DB but I am running Access 2007

 

[Michel Walsh]

You will get an automatic resolution for the parameter
FORMS!formName!ControlName is you use the 'string' as a source of records
(for a form, or for elements of a combo box, list box), DoCmd, in domains
functions (DLookup, DMax, ... ), a saved query from the user interface,
among other places

BUT NOT when you are using CurrentDb or any database object.



In other words:

DoCmd.RunSQL "SELECT * FROM somewhere WHERE field =
FORMS!formName!controlName"


would NOT ask for a parameter, but


CurrentDb.Execute "SELECT * FROM somewhere WHERE field =
FORMS!formName!controlName"


will prompt you for the parameter FORMS!forsmName!controlName



If you prefer, the CONTEXT fro which you execute the code dictates the
behavior, not the sql statement alone.



To use the automatic parameter evaluation from within CurrentDb, let say,
someone can use something like:


Dim p as parameter
Dim q as querydef
Set q=CurrentDb.QueryDefs("saved query name here")
for each p in q.Parameters
p.value = eval(p.name) ' <---- here
next

Dim rst as recordset
Set rst=q.OpenRecordset() ' <--- and here




where the main points are about to call eval to solve the parameter NAME :
FORMS!formName!controlName and to open the recordset ON THE QUERYDEF object,
after you assigned a VALUE to each of its parameters.


That assumes each parameter can be assigned through: eval(its_name)





Vanderghast, Access MVP