combo box for parameter query

[Guest]

I am creating a query and I want to tell it which job I want to query. I
know how to create the query where it asks me and I have to key in the exact
name. How can I have it give me a combo box so I can select the job from a
list?

 

[Guest]

Hello Rich,

This sounds a lot like your question from the 18th...

Anyway, the trick is to not have the query prompt you for parameters, but to
select them via combo box in a form and then have the query pull the
information from the combo boxe/s. Then you can link reports or other forms
to that query and have them show records as specified by the parameters in
the first form. That way the requirement that the forms be open when the
query is run is also satisfied.

[Forms]![the_name_of_your_input_form]![Combo0] is the command for the query.

I had 2 parameters, so I had such statements in 2 fields; of course, they
pointed to different combo boxes.

 

[Guest]

Hi Rich

You could create a small popup form with just one field (a combobox). Use
this form to provide the perameter that the quesry is asking for.

Set this in the criteria of the query column

[Forms]![NameOfPopUp]![NameOfCombo]

Hope this helps

 

[Guest]

Hi Niniel

[Forms]![the_name_of_your_input_form]![Combo0] is the command for the query.

I had 2 parameters, so I had such statements in 2 fields; of course, they
pointed to different combo boxes.

If you are using 2 combos then there is always the possibility the one of
them may not be selected BUT the query will still be looing for the perameter
so you need to alter the formula just slightly to

Forms![NameOfForm]![NameOfCombo] Or Forms![NameOfForm]![NameOfComboo] Is Null

You will need to place this in "each" criteria section that you have the
need for a perameter

--
Wayne
Manchester, England.
Not an expert
Enjoy whatever it is you do

Hello Rich,

This sounds a lot like your question from the 18th...

Anyway, the trick is to not have the query prompt you for parameters, but to
select them via combo box in a form and then have the query pull the
information from the combo boxe/s. Then you can link reports or other forms
to that query and have them show records as specified by the parameters in
the first form. That way the requirement that the forms be open when the
query is run is also satisfied.

[Forms]![the_name_of_your_input_form]![Combo0] is the command for the query.

I had 2 parameters, so I had such statements in 2 fields; of course, they
pointed to different combo boxes.

I am creating a query and I want to tell it which job I want to query. I
know how to create the query where it asks me and I have to key in the exact
name. How can I have it give me a combo box so I can select the job from a
list?

 

[Guest]

Wayne,
I'm feel like such an illiterate. I created the form (titled "Job Names /
Numbers", but I'm not sure what the Name of Combo is. Anyhow, I entered the
criteria as best I could from what you an Niniel told me. When I run the
query, the box comes up with the criteria without [] in the parameter
box...no drop down list. Put on your kindergarten teacher hat and get back
to me please...and thanks.
--
Rich D
Armstrong Custom Homes
Redmond

Hi Rich

You could create a small popup form with just one field (a combobox). Use
this form to provide the perameter that the quesry is asking for.

Set this in the criteria of the query column

[Forms]![NameOfPopUp]![NameOfCombo]

Hope this helps



--
Wayne
Manchester, England.
Not an expert
Enjoy whatever it is you do

I am creating a query and I want to tell it which job I want to query. I
know how to create the query where it asks me and I have to key in the exact
name. How can I have it give me a combo box so I can select the job from a
list?

 

[Guest]

Hm, that's interesting, I have to remember that.

Don't think it's a problem in my case though, people just have to enter a
first and last name, they should be able to do that.

Hi Niniel

[Forms]![the_name_of_your_input_form]![Combo0] is the command for the query.

I had 2 parameters, so I had such statements in 2 fields; of course, they
pointed to different combo boxes.

If you are using 2 combos then there is always the possibility the one of
them may not be selected BUT the query will still be looing for the perameter
so you need to alter the formula just slightly to

Forms![NameOfForm]![NameOfCombo] Or Forms![NameOfForm]![NameOfComboo] Is Null

You will need to place this in "each" criteria section that you have the
need for a perameter

--
Wayne
Manchester, England.
Not an expert
Enjoy whatever it is you do

Hello Rich,

This sounds a lot like your question from the 18th...

Anyway, the trick is to not have the query prompt you for parameters, but to
select them via combo box in a form and then have the query pull the
information from the combo boxe/s. Then you can link reports or other forms
to that query and have them show records as specified by the parameters in
the first form. That way the requirement that the forms be open when the
query is run is also satisfied.

[Forms]![the_name_of_your_input_form]![Combo0] is the command for the query.

I had 2 parameters, so I had such statements in 2 fields; of course, they
pointed to different combo boxes.

I am creating a query and I want to tell it which job I want to query. I
know how to create the query where it asks me and I have to key in the exact
name. How can I have it give me a combo box so I can select the job from a
list?

 

[Guest]

Don't run the query yourself!
Only have whatever report/form that needs the parameters link to the query.

Maybe explaining what I did will be more helpful; and don't despair, it took
me a couple of days as well to change my way of thinking. This requires a
paradigm shift.

I started with a menu-form that had 2 combo boxes - one for first names, and
one for last names, and their labels. Nothing else.
Then I built my query. In the field for the the first name I used the
Build... function [in Design View] for that. I navigated to my menu form in
the first column, and then in the second column I simply picked one of the
two combo boxes listed there. That was it.
I did that for both the fields I wanted to filter the database for. Of
course you can just adapt the SQL command and paste it directly into the
Critera section of the field you want to use as a parameter.
Then I made a report based on the query, and placed a command button for it
on the same menu form as the combo boxes. I also created another form that
uses the query - it pulls the information of the person I specify - and also
placed a command button for that on the same form as the report button and
the two combo boxes.
So now when the database is started, an autoexec macro launches the menu
form, and that's all the user sees. They see two combo boxes that prompt them
to enter a first and a last name, so they do that, and then they can either
look at that person's information, or get a report with all the records
associated with that person.

Hope that makes sense.

Wayne,
I'm feel like such an illiterate. I created the form (titled "Job Names /
Numbers", but I'm not sure what the Name of Combo is. Anyhow, I entered the
criteria as best I could from what you an Niniel told me. When I run the
query, the box comes up with the criteria without [] in the parameter
box...no drop down list. Put on your kindergarten teacher hat and get back
to me please...and thanks.
--
Rich D
Armstrong Custom Homes
Redmond

Hi Rich

You could create a small popup form with just one field (a combobox). Use
this form to provide the perameter that the quesry is asking for.

Set this in the criteria of the query column

[Forms]![NameOfPopUp]![NameOfCombo]

Hope this helps



--
Wayne
Manchester, England.
Not an expert
Enjoy whatever it is you do

I am creating a query and I want to tell it which job I want to query. I
know how to create the query where it asks me and I have to key in the exact
name. How can I have it give me a combo box so I can select the job from a
list?

 

[Doug Munich]

I don't think Access will open a form for you this way. You need to have
the form open (and the combo box set) before you run the query.

Doug

Niniel,

I have been reading and trying to get this done for many moons. I just do
not understand where I am going wrong.

First I create a form ( I called it Supervisor Lookup) and put just one
combo box on it for the name. I then tied the Supervisor Query to the
combo
box. I can open the Lookup form and can see the list when I click on the
pull down. Then I went to the query that I normally have [Enter
Supervisor:]
in the criteria and used the expression builder like you said. I clicked
on
Supervisor Lookup for the form and the combo box name of lookup in the
second
column. It pastes fine. I save it and get out. Then I executed the query
and it had a normal pop-up requesting 'Forms!Supervisor Lookup. . . .to be
entered. It never showed me the form I built.

I have been using Access 2003. Any help you can give would be
appreciated.

Don't run the query yourself!
Only have whatever report/form that needs the parameters link to the
query.

Maybe explaining what I did will be more helpful; and don't despair, it
took
me a couple of days as well to change my way of thinking. This requires a
paradigm shift.

I started with a menu-form that had 2 combo boxes - one for first names,
and
one for last names, and their labels. Nothing else.
Then I built my query. In the field for the the first name I used the
Build... function [in Design View] for that. I navigated to my menu form
in
the first column, and then in the second column I simply picked one of
the
two combo boxes listed there. That was it.
I did that for both the fields I wanted to filter the database for. Of
course you can just adapt the SQL command and paste it directly into the
Critera section of the field you want to use as a parameter.
Then I made a report based on the query, and placed a command button for
it
on the same menu form as the combo boxes. I also created another form
that
uses the query - it pulls the information of the person I specify - and
also
placed a command button for that on the same form as the report button
and
the two combo boxes.
So now when the database is started, an autoexec macro launches the menu
form, and that's all the user sees. They see two combo boxes that prompt
them
to enter a first and a last name, so they do that, and then they can
either
look at that person's information, or get a report with all the records
associated with that person.

Hope that makes sense.

Wayne,
I'm feel like such an illiterate. I created the form (titled "Job
Names /
Numbers", but I'm not sure what the Name of Combo is. Anyhow, I
entered the
criteria as best I could from what you an Niniel told me. When I run
the
query, the box comes up with the criteria without [] in the parameter
box...no drop down list. Put on your kindergarten teacher hat and get
back
to me please...and thanks.
--
Rich D
Armstrong Custom Homes
Redmond


:

Hi Rich

You could create a small popup form with just one field (a combobox).
Use
this form to provide the perameter that the quesry is asking for.

Set this in the criteria of the query column

[Forms]![NameOfPopUp]![NameOfCombo]

Hope this helps



--
Wayne
Manchester, England.
Not an expert
Enjoy whatever it is you do


:

I am creating a query and I want to tell it which job I want to
query. I
know how to create the query where it asks me and I have to key in
the exact
name. How can I have it give me a combo box so I can select the
job from a
list?