Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drop down in Parameter Query? (Access2003)

    Hello,
    Is it possible to add a drop down menu to a parameter query?
    For example, I have a query asking for the 'Company Name' , and I would like to have a drop down from which to select the 'Company Name'. Also, after the Company Name is selected, the next parameter is 'Machine#' - how do I tie the Machine# to the Company Name selected previously (so only Machine#'s related to the 'Company Name' selected appear in the 'Machine#' parameter drop down? Any help would be greatly appreciated.
    Thanks,
    Bart Putnam

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Drop down in Parameter Query? (Access2003)

    The way to do this is to create a form with a combo box that displays the list of companies, and a combo box that lists the machines for the selected company. See for example How to synchronize two combo boxes on a form in Access 2002 or in Access 2003, or do a search for cascading combo box in this forum.

    You can replace the query parameters with references to the combo boxes, e.g. [Forms]![frmSelect]![cboCompany]

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down in Parameter Query? (Access2003)

    Hans,
    I've tried what you recommended but I'm getting errors. I'm also not sure how to tie the Report to the parameter query while using a Form to make selections.
    I've attached a copy of the database. The Report I'm trying to work on is called 'Analysis Report'. It is pulled from the 'Analysis Query'' which has the Company Name and Machine # parameters. I'm trying to be able to have drop down choices for the Company Name and Machine # parameters.
    Thanks,
    Bart

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Drop down in Parameter Query? (Access2003)

    You have used spaces and characters such as # in the names of fields and controls. I always avoid that, for it requires you to enclose those names in square brackets [ ], otherwise SQL and VBA won't recognize them, or raise errors.
    See attached modified version.

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down in Parameter Query? (Access2003)

    Thanks Hans!
    Is there a way to exclude the duplicate entries from the Machine# Combo box?

    I did realize I had really broken naming conventions, etc. I plan to go back through the database and name objects 'properly'.
    Thanks again.
    Bart

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Drop down in Parameter Query? (Access2003)

    The code in the database I attached already eliminates duplicate entries by setting the Row Source of the Machine # combo box to a SQL statement beginning with SELECT DISTINCT.

Posting Permissions

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