Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drop-Down to search in multiple fileds (Access 2000)

    Salesmen in our firm might be the principle salesman or secondary salesman or part of a sales team of up to 10 people. When I built the database, I created a table of salesman names. On the sales card for each sale there are 10 dropdown boxes from which you can select the members of the sales team for each sales job. These dropdown boxes save the name of the salesperson in a table.

    I now want to create a mechanism to find out which jobs each salesman worked on. This is proving difficult because there are 10 separate fileds and a salesman could have worked on a number of jobs and have been listed in any of the fields (1 to 10). Right now the only way to get this information is through a query with a parameter value - but you have to type in the complete name of the salesperson and my boss isn't too keen on that. Is there some way to create a drop-down box that looks for the name in all 10 fields?

    Thanks,

    JoeK

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

    Re: Drop-Down to search in multiple fileds (Access 2000)

    The data design is not optimal. It would be better to create a separate table with a record for each salesman - job combination. So if job A has a sales team of 5 persons, there would be 5 records for job A, and if job B has a sales team of 10 people, there would be 10 records for job B. It would then be easy to select all jobs a specific salesman is involved in.

    With the present setup, you could create a form frmSelect with a combo box cboSalesman listing all salesmen. (I assume that you have a table containing their names).
    You can then replace the parameter in the query with

    [Forms]![frmSelect]![cboSalesman]

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop-Down to search in multiple fileds (Access

    Hans,

    Thanks for your answer. I tried your second suggestion, but this only brings up an "enter parameter value" box, the same as before, but this time with FormsfrmSelectcboSalesman (in black text right above the text entry field). If I type in the name of the salesman, this still works, but the idea was to use the dropdown. What have I done wrong?

    Is [Forms]![frmSelect]![cboSalesman] supposed to bring up the form with the dropdown menu?

    Thanks,

    JoeK

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

    Re: Drop-Down to search in multiple fileds (Access

    No, you're supposed to open the form with the combo box, and open the query, or a form or report based on the query, from that form, for example in the On Click code of a command button.

    If the form with the combo box isn't open, you'll get a parameter prompt.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop-Down to search in multiple fileds (Access

    Works!

Posting Permissions

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