Combo Box Query

  • Thread starter Thread starter Oopesh
  • Start date Start date
O

Oopesh

Hi,

I am having a lil bit of a problem trying to do the Following ...

I want to have a Query which brings up a Combo Box / Drop Down Menu
when you choose the specific value, I need it to Display thos
results.

I could use =[Enter Cost Code] in the critera of the query but th
values in there are to complex to type out and there various codes..
eg. "9839 Excon938

So if i could just select what I need from a combo box and than clic
the ok button

I dont know how to create a query using a form, so I have it a bric
wall there :

I have searched this forums for solutions, in the hopes that someon
experiencedthe same problem, but could not find anything ...

PS: This is the best forum I have ever seem, it has allowed me t
create 70% of my project ... just reports / queries are killing me.

please help

thank
 
A query can't open a form. You can open a form that contains a combo box or
list box. Then open a query that has a criteria referencing the control on
the form.
 
Hi,

Thanks for you reply.

I am not trying to get a query to open a form, I am doing th
following in a Design Query, Critera field :

=[Enter Data Here] <--- so when I open that query it asks me t
specify the value I am searching for ...

now the twist is the above is easy, because i just need to add tha
code in the critera field, but I want a Combobox / dropdown menu tha
reads what is in that collum (or pre-defind range) and allows me t
select the specific range, than once selected and I click the o
button it will display all those with that range. so it just filter
the critera i selected in the combobox.

hope that makes it make more sense, i am not good with coding..

oopes
 
A parameter prompt has no ability to provide a drop down or anything else.
That's why I suggested using a control on a form rather than the parameter
prompt.
 
Hi Duan

Ahh ok that makes sense ... I have been researching the web, an
understand what you mean now but experencing some difficult

I created a form with a combo box called Vendor, I specified it t
read all Vendors in the query (used the wizard). I than created
submit button that run's the Query, but when i click on submit i
displays all data and does not filter it to the selection I choose.

What am I missing ?

Thanks for you valuble feedback.

Oopes
 
Did you set any criteria in your query? You should have an expression in the
criteria of your query like:

Forms!frmYourForm!Vendor
 
Hi Duan

That worked Brilliantly if I set Fix Values in the combo ABC;ACB et
...

Now I still have a issue, because I am trying to set it that it read
the Values from the query, but when i insert the parameters and g
back to the form it shows me a blank drop down box.

I have entertained the thought of creating a seperate table just fo
the combo box to read the vendors name, but I face another issue.
have multiple enties of Vendors e.g as per belo

ID Vendor Descriptio
1 ABC Exce
2 ABC Acces
3 ABC Powerpoin
4 ACB SQ
5 ACB Ph

Now if I create or set the combo to read the "Vendor" field, it show
me 3 x "ABC" 2 x ACB.

How do i specify the combo to read / display just one record ?

any suggestions

Thanks a Million for all your help Duane, you really know your stuff
and great at teaching a noob :
 
Sorry, I have no idea what you are asking. I thought you wanted a combo box
value to filter a query.
 
Hi Duan

That is correct, that is exactly what I want, but when I click on th
combo box it shows no values after I inserted
Forms!frmYourForm!Vendor

The drop down does not show anything I can select.

I am assuming that because the code asks for information first fro
the form, it is not showing the any values.

When the code is not in the report criteria , the combo box than show
the values in there ....

hope that makes sense ....

another thing is when the code is not there and the combo does sho
the data range, it shows me 4 ABC's (Because I have 4 records with
that name) which means you have to scroll alot for the one u lookin
for if you have many Vendors. (Hope that makes sense

How do i tell a combo / dropdown box to read / display Just 1 AB
appose to listing all 4 ????

Regards,
Oopes
 
The Row Source of your combo box shouldn't have any criteria on it unless
you have a good, unexplained reason.
 
Hi Duane,

But if there is nothing specified in your Row Source there will be n
Values for the combo box ???

It will have no listing etc ...

does it not have to read some sort of values from somewhere ? if not
u have nothing to choose from .
 
A combo box has a Row Source property. This is usually a SQL statement or
query name (provided the Row Source Type is "Table/Query"). You need to
create a SQL statement that contains all of the unique values that you would
like to appear in the combo box drop down.
 
I was thinking of making it read a specific query / table, as the dat
in time will change, so the combo box needs to list new data if a ne
entry is inserted in, otherwise it will have to be manually change
everytime..
 
Okay, this is really simple. Say your form is called FormA and has a
combo box called ComboA in which you want to list all the possible
values from FieldA of QueryA; then you want to run QueryA to show only
the records where FieldA is whatever you've selected in ComboA. For
the Row Source of ComboA, type:

Select distinct FieldA from QueryA

Make sure the Default Value of ComboA is Null. Then open QueryA in
Design view, and in the Criteria row for FieldA, type:

[Forms]![FormA]![ComboA] OR [Forms]![FormA]![ComboA] Is Null

(After you save the query, Access will do this stupid thing where it
splits the OR into two separate columns, but that's okay).

Report back if that doesn't work.

Anyone know how to do this with a multi-select list box?
 
Hi,

Thanks for your input, I done what u mentioned but i get the followin
error...

'Select distinct FieldF from Query on external Vendors' may not be
valid setting for the RowSourceType property, or there was a compil
error in the function

I inserted the following in Row Source
Select distinct Course Vendors from Query on Enternal Vendor

I inserted the Following in my Query (name = Query on Enterna
Vendors
[Forms]![Form1]![Vendor] OR [Forms]![Form1]![Vendor] Is Nul

I inserted it under the Vendor criteria field ...

what am i doing wrong
 
Oopesh,
If you insist on using spaces in object names, get used to addin [ ]s around
your object names:
Select distinct [Course Vendors] from [Query on Enternal Vendors]

If you use the query design interface, these [ ]s are added for you.
 
Hi Duane,

I was still having a problem with the values not showing up an
started to mess around a bit , kinda got it to work .... I used th
following

SELECT DISTINCT [Course Vendors] AS Expr1, [Query on Enterna
Vendors].[Course Vendor] FROM [Query on Enternal Vendors];

This gave me a distinct lising of the vendors, but a 2 small problem
...

1. When i click on the combo, the query box comes up for me to type i
a vendor, I just click ok, than it lists everything in the combo :)

FYI : in the Query I have criteria set to : [Forms]![Form1]![Vendor

2. After i have the listing / selection to choose what I am searchin
for, when I click my selection, the box does not keep it in there, i
wont select it bacically, so they query cant read what is in th
"Vendor" combo according to my selection.

I feel like I am so close .... am I missing something ??

PS : if I use SELECT DISTINCT [Course Vendors] FROM [Query on Enterna
Vendors]

I just get a pop up box saying "Course Vendor" and I need to type in
value, once I click ok, the combo box has nothing in it :

Thanks all for your input.
 
Oopesh said:
Hi Duane,

I was still having a problem with the values not showing up and
started to mess around a bit , kinda got it to work .... I used the
following :

SELECT DISTINCT [Course Vendors] AS Expr1, [Query on Enternal
Vendors].[Course Vendor] FROM [Query on Enternal Vendors];
Click to expand...

Why did you do that?
This gave me a distinct lising of the vendors, but a 2 small problems
...

1. When i click on the combo, the query box comes up for me to type in
a vendor, I just click ok, than it lists everything in the combo :)

FYI : in the Query I have criteria set to : [Forms]![Form1]![Vendor]
Click to expand...

If you open the query normally, without the criterion, do you get a
popup box?

You have to set the criterion in the query to
[Forms]![Form1]![Vendor] OR [Forms]![Form1]![Vendor] IS NULL

If Access splits it out into two differet columns for the same field,
that's fine, but you have to have the "[Forms]![Form1]![Vendor] IS
NULL" in there. Otherwise, when you click the combo box, the query
will run using whatever is in there as the criterion for the field, and
if there's nothing in there you won't get any records from the query to
populate the list. Get it?
2. After i have the listing / selection to choose what I am searching
for, when I click my selection, the box does not keep it in there, it
wont select it bacically, so they query cant read what is in the
"Vendor" combo according to my selection.

I feel like I am so close .... am I missing something ??

PS : if I use SELECT DISTINCT [Course Vendors] FROM [Query on Enternal
Vendors]

I just get a pop up box saying "Course Vendor" and I need to type in a
value, once I click ok, the combo box has nothing in it :(
Click to expand...

My guess is that you have mistyped something.

What is the actual name of the field in the query? Is it [Course
Vendor] or [Course Vendors]? Is there an underscore? Make sure you
get it right in the Row Source of your combo box. Also, make sure you
have the name of the query typed correctly. You have typed it more
than one way in your posts. Is it [Query on Enternal Vendors] or
[Query on External Vendors]?
 
OMG!!!!

I got it to work ... wooooooot !!!! :

I used the following ....

SELECT DISTINCT [Query on Enternal Vendors].[Course Vendor] FRO
[Query on Enternal Vendors];

and it worked 8)

infact you guys made this possible, wow I feel like i just climbed th
highest mountain .....

thanks you all so so so so much .... I really appreciate all you
assitance ... with out you, this would of not been possible ....

Thanks,
Oopes
 
Hi Duane / Sassy,

I seem to be having a lil problem ....

I have a Query of Vendors we use, it runs off a form where you choos
which vendor was used. The query has many fields Name, surname
vendor, cost etc ...

I Created a report, this report shows you the particular vendor yo
selected and all the entries on them.

I have a cost per entry ... I want to do a Total cost, so I inserted
Unbound Box in my report and added in the command =Sum([Cost])

That unfortunatly does not seem to work , it show #Error

Did I forget something ??

Thanks and Regards,
Oopes
 
Back
Top