Results 1 to 2 of 2
2008-03-04, 00:08 #1
- Join Date
- Nov 2002
- Seattle, Washington, USA
- Thanked 0 Times in 0 Posts
Select query with user-defined criteria (2000/SP3)
I'm an Access n00b hoping that I'm asking an easy question and can be pointed to a primer of some kind. (My Dummies books are failing me.)
Suppose I have a simple database that keeps track of my company's individual stores. Stores are located in a few dozen US cities. Each city has one or more stores.
What I want is a form in which a user can select one or more cities from a list of cities, and have returned a list of stores in each of the selected cities.
This seems to me to be a select query, where the user defines the criteria. If that's so, how might I go about setting up a form that takes the user input and returns the proper output?
2008-03-04, 01:01 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Select query with user-defined criteria (2000/SP3)
You can place an unbound combo box or list box on your form and set its Row Source property to a table that contains the list of cities (or a query that returns that list).
Create a query based on the stores table that has Forms!NameOfForm!NameOfComboBox (with the appropriate names substituted) as criteria for the city field.
Use this query as Record Source for the form.
To make the form react to the choice of a city, create the following event procedure for the After Update event of the combo box:
Private Sub NameOfComboBox_AfterUpdate()
This procedure merely forces the form to read its record source (the query) again.
I have attached a demo database using the Customers table from the Northwind sample database. The query qryCustomers uses a variant of what I described above that allows you to leave the combo box blank if you want to display all records.
To let the user select a number of cities would require more advanced VBA coding.