Using a Combo box selection as a criteria in a query

  • Thread starter Thread starter JImJewell
  • Start date Start date
J

JImJewell

Guest
I'm sure the answer is simple, but I am baffled.

If I use a drop down combo box in a form that presents the distinct entries from a related table, how do I make all records an option? (And have it actually return the entire table.)
 
Don't quite follow you there Jim

What are you trying to do exactly? run a query?

Col
 
What you're trying to do is easy to implement, but depending on your knowledge of Access, might be daunting at first. I'm sure this has been gone over in this forum before, so I won't go into extreme detail. Do a search on the Microsoft Knowledge Base as well. I think this problem might be covered in the Developer Solutions Database as well.

Here's the concept: whatever query you are using as the source for the combo box, use a union query to create an <<All>> choice for it like this:
SELECT "<<All>>" from qryInfo UNION
SELECT Sales from qryInfo;

That's the simplest case. If you have more than one field coming from your query, then use something of this form:
SELECT "<<All>>,0 from qryInfo UNION
SELECT Sales,SalesID from qryInfo;

Now, you're going to have to use some VBA code to check to see if the user choose "<<All>>" from the list or a value. Then build the rest of your query from there. In most cases, when the user chooses "<<All>>", you can just omit the "WHERE" part of your SQL statement. Hopefully, you're already using a SQL statement to pull the records. If not, you're going to have to. There's no other way (I know of) to do it using a select query with a reference to a form value.
 
Mile-O and DCX have it covered - I was reading it differently:rolleyes:

Col
 
Thanks to all, I'll try these variations of what I've been doing.
 

Users who are viewing this thread

Back
Top Bottom