Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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()
    End Sub

    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts