Parameter Query with Drop-down Box

[Guest]

Is is possible (and not too complicated) to set up a parameter query that
will display a drop-down instead of just having to type the name? If the
user misspells the name, none of the records will load.
For example: If I am looking for a person's last name, a drop-down box will
be used to choose the name of the employees.

Thank you, Karen

 

[Allen Browne]

No. The query parameters are not a real interface, and cannot contain combos
or check boxes.

Instead, create a small unbound form where you can put a combo. The refer to
it as the paremeter in your query.

For example, if the form is named Form1, and the combo is Combo7, the
Criteria row in your query will contain:
[Forms].[Form1].[Combo7]
Provided the form is open, the query will then read the value from the combo
on the form.

 

[Brian Bastl]

not possible the way you want to do it.

create a small pop-up form for your criteria, and reference these controls
in your query's criteria

Brian

 

[Guest]

I have the same problem. I have tried what you suggested - I created a combo
box which draws its values from the table with the names in a form entitled
Form1 and in the query I wrote "[Forms].[Form1].[Combo0]". The thing is that
instead of the query taking the value from the combo box, the usual parameter
pop up window appears where you have to write it down yourself, and
"[Forms].[Form1].[Combo0]" is where "Enter name:" or whatever usualy is.

I don't know what I'm doing wrong here. Please help me out.
Thanks

No. The query parameters are not a real interface, and cannot contain combos
or check boxes.

Instead, create a small unbound form where you can put a combo. The refer to
it as the paremeter in your query.

For example, if the form is named Form1, and the combo is Combo7, the
Criteria row in your query will contain:
[Forms].[Form1].[Combo7]
Provided the form is open, the query will then read the value from the combo
on the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is is possible (and not too complicated) to set up a parameter query that
will display a drop-down instead of just having to type the name? If the
user misspells the name, none of the records will load.
For example: If I am looking for a person's last name, a drop-down box
will
be used to choose the name of the employees.

Thank you, Karen

 

[Allen Browne]

The form must be open, with a value selected in the combo before you run the
query.

If that's the case and it is still asking for a parameter, you have
misspelled the name of the form or combo in the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have the same problem. I have tried what you suggested - I created a
combo
box which draws its values from the table with the names in a form
entitled
Form1 and in the query I wrote "[Forms].[Form1].[Combo0]". The thing is
that
instead of the query taking the value from the combo box, the usual
parameter
pop up window appears where you have to write it down yourself, and
"[Forms].[Form1].[Combo0]" is where "Enter name:" or whatever usualy is.

I don't know what I'm doing wrong here. Please help me out.
Thanks

No. The query parameters are not a real interface, and cannot contain
combos
or check boxes.

Instead, create a small unbound form where you can put a combo. The refer
to
it as the paremeter in your query.

For example, if the form is named Form1, and the combo is Combo7, the
Criteria row in your query will contain:
[Forms].[Form1].[Combo7]
Provided the form is open, the query will then read the value from the
combo
on the form.

Is is possible (and not too complicated) to set up a parameter query
that
will display a drop-down instead of just having to type the name? If
the
user misspells the name, none of the records will load.
For example: If I am looking for a person's last name, a drop-down box
will
be used to choose the name of the employees.

 

[Guest]

I found the solution. I don't know if that has anything to do with it, but
I'm using the swedish version of access. Anyway, I used exclamation marks
instead of dots, making "[Forms]![Form1]![Combo0]" the correct parameter.

Thanks for answering, though!

I have the same problem. I have tried what you suggested - I created a combo
box which draws its values from the table with the names in a form entitled
Form1 and in the query I wrote "[Forms].[Form1].[Combo0]". The thing is that
instead of the query taking the value from the combo box, the usual parameter
pop up window appears where you have to write it down yourself, and
"[Forms].[Form1].[Combo0]" is where "Enter name:" or whatever usualy is.

I don't know what I'm doing wrong here. Please help me out.
Thanks

No. The query parameters are not a real interface, and cannot contain combos
or check boxes.

Instead, create a small unbound form where you can put a combo. The refer to
it as the paremeter in your query.

For example, if the form is named Form1, and the combo is Combo7, the
Criteria row in your query will contain:
[Forms].[Form1].[Combo7]
Provided the form is open, the query will then read the value from the combo
on the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is is possible (and not too complicated) to set up a parameter query that
will display a drop-down instead of just having to type the name? If the
user misspells the name, none of the records will load.
For example: If I am looking for a person's last name, a drop-down box
will
be used to choose the name of the employees.

Thank you, Karen

 

[Travis]

I found the solution. I don't know if that has anything to do with it, but
I'm using the swedish version of access. Anyway, I used exclamation marks
instead of dots, making "[Forms]![Form1]![Combo0]" the correct parameter.

Perhaps Allen can clear up exactly when ! should be used instead of .
but from what I've seen they usually do pretty much the same thing.

Travis
www.travismorien.com

 

[Allen Browne]

It should work with either the bang (!) or the dot (.) in this context. I am
not familiar with the Sweedish version, but I can't see anything that should
prevent that from working.

Travis, if you are interested in a technical discussion of bang verses dot,
Andy Baron has a description of the difference here:
http://doc.advisor.com/doc/05352

The only common context where you must use the bang is when referring to
fields of a recordset, e.g.:
rs!Surname
works but rs.Surname does not.

I personally find the dot more convenient, because:
a) VBA autocompletes the name for you, which means faster and more accurate
coding, and
b) If you do mistype it (or refer to a field that is no longer there), the
compiler catches the error.
Anything that improves the accuracy of your coding *and* catches problems at
development time has to be worthwhile.

There is one context where the dot can confuse Access. If your form has a
field named (say) City, but there is not control named City on the form, and
you use:
Me.City
then, under rare circumstances, the compiler can spit the dummy and tell you
there is no such thing as Me.City. This certainly happens if you reassign
the RecordSource of the form after it opens, but the problem is more
widespread than that, and can suddenly arise in altering a database that
previously compiled okay. It seems to be one of the many inconsistencies in
the way Access handles the AccessField type. In short, if I am referring to
a field in the form's RecordSource that is not a control on the form, I will
use the bang to avoid this problem.

If you want a rule of thumb, my suggestion is:
Use the dot if you can, and use the bang when the dot doesn't work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I found the solution. I don't know if that has anything to do with it,
but
I'm using the swedish version of access. Anyway, I used exclamation marks
instead of dots, making "[Forms]![Form1]![Combo0]" the correct parameter.

Perhaps Allen can clear up exactly when ! should be used instead of .
but from what I've seen they usually do pretty much the same thing.

Travis
www.travismorien.com